Average, max and min based on 3 criteria

**VBADUD** · 02-06-2013, 05:40 AM

Hi All,

Have a situation that I cannot seem to overcome.

On the attached sheet cells B1:D1 have drop down lists where selections can be made. Based on the type of contract selected and the value range selected and based on the criteria being applied to Columns I and J - I want to get the average % variations from col L along with the maximum and minimum variation %ge in the selected range.

Sounds pretty straight forward but nothing really seems to work for me! The averageifs formula I tried in column E can be seen in the attachment (E1) - for the max and min i tried the {=Max(if(and(criteria1, criteria 2, criteria 3), L2:L100))} option - but that did not work either. Please help.

Cheers,

Vbadud

**Ace_XL** · 02-06-2013, 05:51 AM

the maximum value in column J is $10000 whereas in your selection the lower range is $50000, hence the anomaly

Your avergeifs formula seems right

For max and min use these array formula

=MAX(IF(($I$2:$I$100=B2)*($J$2:$J$100>=C2)*($J$2:$J$100<=D2),$L$2:$L$100))

and

=MIN(IF(($I$2:$I$100=B2)*($J$2:$J$100>=C2)*($J$2:$J$100<=D2),$L$2:$L$100))

Confirm with Ctrl+Shift+Enter

**VBADUD** · 02-06-2013, 11:07 PM

Thanks a ton Ace - that worked well.
Hopefully I can build on that and insert more variations and selection options into those formulae. Thanks again.

vbadud