If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Formula help
I am trying to program excel to return rows and values based on criteria and
need some help on which formula to use and how. I need a separate excel document to add part numbers and qty whose sales are over a certain amount for a given period of time. See below for example Part# Description 1st Qtr Avg. 2nd Qtr Avg. 3rd Qtr Avg. 4th Qtr Avg 1 Float .67 .75 .38 1.45 I need the excel document to pull the part# description and qty if two consecutive qtrs avg .67 and above and add it to a separate excel file. |
#2
|
|||
|
|||
Formula help
Could use an array* formula like this:
=INDEX(A:A,SMALL(IF(MAX(AVERAGE($C$2:$D$10),AVERAG E($D$2:$E$10),AVERAGE($E2:$F$10))0.67,ROW($F$2:$F $10)),ROW(A1))) Copy formula down as far as needed (will display the #NUM error if no more results are found). This formula will display part number. To get the description, change first part of formula to reference B:B. *Array formulas must be confimed using Ctrl+Shift+Enter, not just Enter -- Best Regards, Luke M "Cooldistribution" wrote in message ... I am trying to program excel to return rows and values based on criteria and need some help on which formula to use and how. I need a separate excel document to add part numbers and qty whose sales are over a certain amount for a given period of time. See below for example Part# Description 1st Qtr Avg. 2nd Qtr Avg. 3rd Qtr Avg. 4th Qtr Avg 1 Float .67 .75 .38 1.45 I need the excel document to pull the part# description and qty if two consecutive qtrs avg .67 and above and add it to a separate excel file. |
Thread Tools | |
Display Modes | |
|
|