Multiple Criteria for Max

  • 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

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

  • 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

  • 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













    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

  • 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

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

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!