Tarheel8181
New Member
See attached. Need formula that will find the max time among those who got As on both test.
Need multi condition MAX formula
- Thread starter Tarheel8181
- Start date
you were pretty close. Unforuntately, the Boolean functions only spit out a single value. To get your formula to work, we'll force the True/False arrays to multiply against each other, forming a single array of 1/0's.
Array formula:
=MAX(IF(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1),$C$3:$C$7))
Array formula:
=MAX(IF(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1),$C$3:$C$7))
NARAYANK991
Excel Ninja
Hi ,
To add to what Luke has already posted , I would like to point out that when you use the MAX function , the IF function may not always be necessary. In your case , even the following formula will give the correct result :
=MAX(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1)*($C$3:$C$7))
This is because the first and second terms return an array of 0s and 1s ; 0 where the condition is not satisfied , and 1 where the condition is true. Thus , the resulting array will contain the values from the range $C$3:$C$7 where both conditions were met , and 0 elsewhere. The MAX function will therefore return the correct result.
It is a different matter when you wish to retrieve the minimum value.
In this case , where the conditions are not satisfied , the multiplication of the 3 arrays will return 0 , and this may not be the minimum value. To ensure that the correct minimum value is returned , the IF becomes necessary , so that where the conditions are not satisfied , the IF function puts in a FALSE value , which is ignored by the MIN function , as in :
=MIN(IF(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1),($C$3:$C$7)))
In both cases , the formulae are array formulae , to be entered using CTRL SHIFT ENTER.
Narayan
To add to what Luke has already posted , I would like to point out that when you use the MAX function , the IF function may not always be necessary. In your case , even the following formula will give the correct result :
=MAX(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1)*($C$3:$C$7))
This is because the first and second terms return an array of 0s and 1s ; 0 where the condition is not satisfied , and 1 where the condition is true. Thus , the resulting array will contain the values from the range $C$3:$C$7 where both conditions were met , and 0 elsewhere. The MAX function will therefore return the correct result.
It is a different matter when you wish to retrieve the minimum value.
In this case , where the conditions are not satisfied , the multiplication of the 3 arrays will return 0 , and this may not be the minimum value. To ensure that the correct minimum value is returned , the IF becomes necessary , so that where the conditions are not satisfied , the IF function puts in a FALSE value , which is ignored by the MIN function , as in :
=MIN(IF(($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1),($C$3:$C$7)))
In both cases , the formulae are array formulae , to be entered using CTRL SHIFT ENTER.
Narayan
Somendra Misra
Excel Ninja
Hi,
Further to @Luke M & @NARAYANK991 Sir posted, if you have Excel 2010+ you can also use below formula.
=AGGREGATE(14,6,($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1)*($C$3:$C$7),1)
Advantage is you need not use Ctrl+Shift+Enter. Just Enter will work.
Regards,
Further to @Luke M & @NARAYANK991 Sir posted, if you have Excel 2010+ you can also use below formula.
=AGGREGATE(14,6,($A$3:$A$7=$G$1)*($B$3:$B$7=$G$1)*($C$3:$C$7),1)
Advantage is you need not use Ctrl+Shift+Enter. Just Enter will work.
Regards,