Prev: Worksheet relative reference
Next: add two cells from seperate work sheets into a cell on seperat
From: Cooldistribution on 27 Apr 2010 11:56
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.
From: Luke M on 27 Apr 2010 14:41
Could use an array* formula like this:
=INDEX(A:A,SMALL(IF(MAX(AVERAGE($C$2:$D$10),AVERAGE($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" <Cooldistribution(a)discussions.microsoft.com> wrote in
message news:2AA271F7-EB1D-4819-9272-C12EF28DCBB4(a)microsoft.com...
>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.


 | 
Pages: 1
Prev: Worksheet relative reference
Next: add two cells from seperate work sheets into a cell on seperat