How to determine the values? Referring following online file, I would like to de

Question

How to determine the values?

Referring following online file, I would like to determine the values based on matching following conditions
1) Under column A, "C" is matched.
2) Under column B, "1-Sep-14" is matched.
3) Under column D, Looking for the minimum value and greater than 0
4) Under column C, looking for maximum and minimum within matched selections from 1 to 3 conditions

It should return
Maximum 24000
Minimum 23600

If only one matched value, then maximum and minimum values would be the same.
Does anyone have any suggestions?
Thanks in advance for any suggestions???

Explanation / Answer

For step 3, enter the following array formula, confirmed with Ctrl+Shift+Enter, in a cell, for example in I10:

=MIN(IF(($A$2:$A$253=$F$3)*($B$2:$B$253=$G$3)*($D$2:$D$253>0),$D$2:$D$253))

For step 4, enter the following array formula, confirmed with Ctrl+Shift+Enter, in a cell, for example in I11:

=MAX(IF(($A$2:$A$253=$F$3)*($B$2:$B$253=$G$3)*($D$2:$D$253=$I$10),$C$2:$C$253))

and in I12:

=MIN(IF(($A$2:$A$253=$F$3)*($B$2:$B$253=$G$3)*($D$2:$D$253=$I$10),$C$2:$C$253))

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.