Formula help

C

Cooldistribution

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.
 
L

Luke M

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top