Multiple Criteria for Max

boynejs · Aug 27th 2013

I am trying to find the maximum value in 4th column when 3 other criteria is met in 3 other columns. Here is my equation below...

=MAX(ifs($C$1:$C$289774=M1)*($D$1:$D$289774=N1)*($E$1:$E$289774=O1),$I$1:$I$289774)

The maximum value I am searching for is in "column I", but I need the criteria in column c, column d, and column e already met. The maximum value for all of these criteria would show up in column P
[TABLE="width: 1024"]

[tr]

[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl63, width: 64"][/TD]
[TD="class: xl64, width: 64"][/TD]
[TD="width: 64, align: center"][/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]A[/TD]
[TD="class: xl65, width: 64"]B[/TD]
[TD="class: xl65, width: 64"]C[/TD]
[TD="class: xl65, width: 64"]D[/TD]
[TD="class: xl65, width: 64"]E[/TD]
[TD="class: xl65, width: 64"]F[/TD]
[TD="class: xl65, width: 64"]G[/TD]
[TD="class: xl65, width: 64"]H[/TD]
[TD="class: xl65, width: 64"]I[/TD]
[TD="class: xl65, width: 64"]J[/TD]
[TD="class: xl65, width: 64"]K[/TD]
[TD="class: xl65, width: 64"]L[/TD]
[TD="class: xl63, width: 64"]M[/TD]
[TD="class: xl63, width: 64"]N[/TD]
[TD="class: xl64, width: 64"]O[/TD]
[TD="width: 64, align: center"]P[/TD]

[/tr]

[tr]

[TD="class: xl65, width: 64"]726430[/TD]
[TD="class: xl65, width: 64"]14920[/TD]
[TD="class: xl65, width: 64"]1933[/TD]
[TD="class: xl65, width: 64"]5[/TD]
[TD="class: xl65, width: 64"]22[/TD]
[TD="class: xl65, width: 64"]17[/TD]
[TD="class: xl65, width: 64"]0[/TD]
[TD="class: xl65, width: 64"]85[/TD]
[TD="class: xl65, width: 64"]66[/TD]
[TD="class: xl65, width: 64"]1700[/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl65, width: 64"][/TD]
[TD="class: xl63, width: 64"]1933[/TD]
[TD="class: xl63, width: 64"]5[/TD]
[TD="class: xl64, width: 64"]22[/TD]
[TD="width: 64, align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]52[/TD]
[TD="class: xl65"]51[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1934[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]78[/TD]
[TD="class: xl65"]55[/TD]
[TD="class: xl65"]1700[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1935[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]60[/TD]
[TD="class: xl65"]55[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1936[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]86[/TD]
[TD="class: xl65"]69[/TD]
[TD="class: xl65"]2100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1937[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]65[/TD]
[TD="class: xl65"]52[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1938[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]64[/TD]
[TD="class: xl65"]58[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1939[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]59[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1940[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]1[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]79[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1941[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]17[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]83[/TD]
[TD="class: xl65"]65[/TD]
[TD="class: xl65"]1700[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1942[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]2[/TD]
[TD="class: xl65"]21[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]83[/TD]
[TD="class: xl65"]69[/TD]
[TD="class: xl65"]2100[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1943[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]5[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]500[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1944[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]9[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]64[/TD]
[TD="class: xl65"]63[/TD]
[TD="class: xl65"]900[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1945[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[tr]

[TD="class: xl65"]726430[/TD]
[TD="class: xl65"]14920[/TD]
[TD="class: xl65"]1933[/TD]
[TD="class: xl65"]6[/TD]
[TD="class: xl65"]3[/TD]
[TD="class: xl65"]13[/TD]
[TD="class: xl65"]0[/TD]
[TD="class: xl65"]70[/TD]
[TD="class: xl65"]66[/TD]
[TD="class: xl65"]1300[/TD]
[TD="class: xl65"][/TD]
[TD="class: xl65"][/TD]
[TD="class: xl63"]1946[/TD]
[TD="class: xl63"]5[/TD]
[TD="class: xl64"]1[/TD]
[TD="align: center"]#VALUE![/TD]

[/tr]

[/TABLE]

Thanks in advance for the help!

Jeff

**pike** · Aug 27th 2013

Re: Multiple Criteria for Max

Hi
you can use the index to coerce an array to find the max
=MAX(INDEX(($C$1:$C$289774=M1)*($D$1:$D$289774=N1)*($E$1:$E$289774=O1)*($I$1:$I$289774),0))

boynejs · Aug 27th 2013

Re: Multiple Criteria for Max

Thanks so much for the help!

Robert Mika · Aug 27th 2013

Re: Multiple Criteria for Max

This should be slighlty quicker:
=MAX(IF($C$1:$C$289774=M1,IF($D$1:$D$289774=N1,IF($E$1:$E$289774,$I$1:$I$289774)))

Confirm Control+Shift+Enter

**pike** · Aug 27th 2013

Re: Multiple Criteria for Max

Have you timed them as Array formulas are generally slower as they have a lot more to calculate
Single index is faster than multiple indexes or functions.

Robert Mika · Aug 27th 2013

Re: Multiple Criteria for Max

Actually did not time myself but there was a topic on MrExcel where some of the gurus recemmended to us IF() function instead of multiplication.

**pike** · Aug 27th 2013

Re: Multiple Criteria for Max

http://www.ozgrid.com/Excel/fr…lLevel3/Level3Lesson2.htm explains my thinking, but I have to be careful when questioning MVP's as I can easily be corrected

**pike** · Aug 27th 2013

Re: Multiple Criteria for Max

Do you have a link? As those guys a brilliant big fan of donkeyote Dominic and co

Robert Mika · Aug 27th 2013

Re: Multiple Criteria for Max

I do not have link but quoted Aladin Akyurek( I belive you know that name)
A function call means some overhead. So many thinks: the lesser the number of such calls the faster the calculation. But I opt for because I expect filtering to be faster than pairwise multiplications (+ INDEX).

Since you just have 3 IFs, I'd go for the first formula for reasons of efficiency and transparency.

**pike** · Aug 28th 2013

Re: Multiple Criteria for Max

hope its not this one
http://www.mrexcel.com/forum/e…ormulas-unique-count.html

**pike** · Aug 28th 2013

Re: Multiple Criteria for Max

it would be good if you could find the thread searched couldnt find it..
the logic has one less step

Robert Mika · Aug 28th 2013

Re: Multiple Criteria for Max

No, not this one.
But I hope this clarifies some uncertainty

**pike** · Aug 28th 2013

Re: Multiple Criteria for Max

just doing a little more testing and it has half the evaluations in its processing

Robert Mika · Aug 28th 2013

Re: Multiple Criteria for Max

Quote from pike;681733

just doing a little more testing and it has half the evaluations in its processing

Glad to hear that.
So - is it time to change?

**pike** · Aug 28th 2013

Re: Multiple Criteria for Max

Discussion on going with other another sources "You noted in another thread how fast a ridiculously long megaformula actually calculated... number of calculations isn't always an indicator of speed... it's which functions are more efficient versus others...

The logic with the IF functions perhaps being better is that each IF check checks the logic, and only does the what_if_TRUE calculation if the result of the logical check is TRUE, and when the what_if_FALSE is a fixed result or another nested IF function, then it would be quicker to process each by skipping the ... whereas the multiplication of arrays, always calculates each of the conditions out all the time. "
So further testing shows the IF does calculates every value but is the IF arrayformula Boolean/binary sum quicker thanthe-non array multiplication .. Still testing

**pike** · Aug 28th 2013

Re: Multiple Criteria for Max

Possibly wont be much in it but it would be good to know how it all real works/process on the micro level.

Robert Mika · Aug 28th 2013

Re: Multiple Criteria for Max

What kind of data do you test?

**pike** · Aug 29th 2013

Re: Multiple Criteria for Max

the speed test i use...http://msdn.microsoft.com/en-us/library/aa730921.aspx

index = 0.00163

and if array
=MAX(IF(($C$1:$C$14=M1)[COLOR="#FF0000"]*[/COLOR]($D$1:$D$14=N1)[COLOR="#FF0000"]*[/COLOR]($E$1:$E$14=O1),$I$1:$I$14))
or
=MAX(IF($C$1:$C$14=M1,IF($D$1:$D$14=N1,IF($E$1:$E$14,$I$1:$I$14))))

0
.

0

1

5
3
bit of top gear suspence
If boolean logic faster at 0.00153

Thanks for the Tip Rob.. its the first array I've come across thats faster in calx time than standard formulas

**pike** · Aug 29th 2013

Re: Multiple Criteria for Max

but
=MAX(INDEX(($C$1:$C$1400=M1)*($D$1:$D$1400=N1)*($E$1:$E$1400=O1)*($I$1:$I$1400),0))
a extra 1386 rows
0.00273
=MAX(IF($C$1:$C$1400=M1,IF($D$1:$D$1400=N1,IF($E$1:$E$1400,$I$1:$I$1400))))
0.00292

so using a too many or large array formulas will slow calx's time down but small IF logic arrays could be quicker

Robert Mika · Aug 29th 2013

Re: Multiple Criteria for Max

I have not done any tests recently but sometimes when I run in the past I got the the split of seconds in test different everytime.
I keen to seen what woudl happen and 500000 rows.
I assume there is correlation as well to your PC(memory and processor) and the type of data.
Please share any other tests you going to do.

Multiple Criteria for Max

Participate now!

Share