Formula Forensics No. 008 – Elkhan’s MaxIf

Posted on January 24th, 2012 in Formula Forensics , Huis , Posts by Hui - 31 comments

Last week Chandoo received an email from Elkhan, “I have a data table with several parameters. My aim is to calculate the maximum value of the parameter5 if the parameter3 is “A” and the parameter4 is C1. Of course I can do it by sorting the data by par3 and then by par4 and then subtotaling by max, however I wonder if it can be done by a formula and I am sure it can because I believe Excel has the absolute power to do anything with any set of data. Could you please help me.

Chandoo responded with a nice Array Formula:

=MAX(IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0)) <Ctrl, Shift, Enter>

So today in Formula Forensics we will have a look at how this MaxIf formula works.

As always, please follow along using a sample file you can Download Here

 

The Max If Formula

=MAX(IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0))


Named Formula

Firstly, we should note that the formula uses 3 Named Ranges. These are Parameter_3, Parameter_4 and Parameter_5.

This is good practice as it simplifies the formula and makes the formula more readable and extendable as we will see later.

I think it is clear that:

=MAX(IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0))

is much clearer to Read and Understand than

=MAX(IF((Data1!$D$2:$D$12=D13)*(Data1!$E$2:$E$12=E13),Data1!$F$2:$F$12,0))

 

If you open the Sample File and goto the Name Manager you will see the named formula listed

Each named formula eg: Parameter_3 simply refers to a range on the Data worksheet

Parameter_3: =Data1!$D$2:$D$12

 

The Max If Formula

=MAX(IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0))

Reading this formula it is saying, I want the Maximum value of Parameter_5 If Parameter_3= the value in D13 and Parameter_4= the value in E13,

But how does it work.

We can see looking at the above formula that the formula is returning the Maximum value of an If Function.

The formula IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0)

We have seen in previous Formula Forensics, the Excel If() function has this format

=If(Condition, Value if True, Value if False)

Condition (Parameter_3=D13)*(Parameter_4=E13)

Value if True: Parameter_5

Value if False: 0

So this is saying

If (Parameter_3=D13) and (Parameter_4=E13) then use the value in Parameter_5

and

If (Parameter_3<>D13) or (Parameter_4<>E13) then use the value 0

 

We can check this

In a spare cell, say F15, Type:  =(Parameter_3=D13)*(Parameter_4=E13), Press F9 not Enter

Excel returns: ={1;0;1;0;1;0;0;1;0;0;1}

This is saying that the 1st, 3rd, 5th, 8th and 11th cells all contain values that match our Criteria

So the 1st, 3rd, 5th, 8th and 11th cells match our criteria, How do we use that to get the values from Column F ?

The Criteria is part of an If() Function, which says

If the Criteria is True, Return the value in Parameter_5 else 0

In another spare cell, Say F17, Enter:  =IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5,0)

Excel will now return ={0.08;0;0.198;0;0.019;0;0;0.545;0;0;0.246}

This is a list of the values from Parameter_5 (Column F) that match the If() statement

Note that the 1st, 3rd, 5th, 8th and 11th values contain values and the remaining 2nd, 4th, 6th, 7th, 9th and 10th values contain 0’s as they failed the criteria test in the If() statement.

This array made by the If() function is then passed to the Max() function

=MAX({0.08;0;0.198;0;0.019;0;0;0.545;0;0;0.246} )

Which you can check in Cell F19

Excel returns 0.545 as it should as it is the maximum value in the array.

 

Extension

Elkhan hinted in a follow-up email at wanting to extend this to all criteria, not just the 2 criteria questioned.

The beauty of using Named Ranges in these formula is highlighted here where we simply add two more parameters to our Criteria part of the If() function, see in Red below.

=MAX(IF((Parameter_1=B22)*(Parameter_2=C22)*(Parameter_3=D22)*(Parameter_4=E22),Parameter_5,0))


We see that Excel returns the correct value of 0.198, as there are now only two records that match our criteria

 

DOWNLOAD

You can download a copy of the above file and follow along, Download Here.

 

OTHER POSTS IN THIS SERIES

You can learn more about how to pull Excel Formulas apart and how they work internally in the following post:

Formula Forensic Series:

 

WE NEED YOUR ONGOING HELP

I have received a few more ideas since last week and these will feature in coming weeks.

However I do need more ideas though and so I need your help.

If you have a neat formula that you would like to share and explain, try putting pen to paper and draft up a Post like above, or

If you have a formula that you would like explained but don’t want to write a post also send it in to Chandoo or Myself.

 

Written by Hui...
Tags: , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

31 Responses to “Formula Forensics No. 008 – Elkhan’s MaxIf”

  1. derek says:

    Instead of multiplying several individual logical tests together, you can also use MMULT to do it in one.

    =MAX(IF(MMULT(--(Parameters1_4=B22:E22),{1;1;1;1})=4,Parameter_5,0))

    This formula uses a new range that is the combination of all four ranges, and tests that all four are matched. you can change "=4" to ">=3" to test that at least three out of four are matched, and you can replace {1;1;1;1} with {1;2;4;8} to test any combination of matches: "=5" then tests that parameters 1 and 3 are matched, but 2 and 4 are not.

    Can anyone simplify this formula (such as eliminating the awkward {1;1;1;1} range)?

    Also, can anyone extend this to non-contiguous parameter ranges? I would like to test arbitrary columns in a large data table, but it using INDEX(TABLE,{A,B,C,D},)doesn't seem to work for me to make a range MMULT can use.

    • Kyle McGhee says:

      Hi Derek,

      I've wanted to use MMULT for some time, but could not think of a practical use for it in my line of work...but now you have. Thanks!

      As for simplifying the formula, I don't have anything that hasn't been said before, but I would put those array constants in a named formula, which would allow you to easily type it in a formula. If you want to be able to change the number of criteria (for the 2nd array in MMULT) on the fly, rather than having to change the {1;1;1;1} manually, you could set up a little dynamic range that refers with say 4 cells [A1:D1] with 1 in each cell and in the named formula do =Transpose(A1:D1), which will give you {1;1;1;1}. And if you add/delete/change criteria, the range will grow/shrink and update the array for MMULT.

      As for non-contiguous ranges, try something like this:
      In a named formula
      =CHOOSE({1,2},Data!$B$2:$B$12,Data!$D$2:$D$12)

      This will choose both B2:B12 and D2:D12 for the comparison. That is extensible to as many non-contiguous ranges as needed, though they need to be the same size otherwise N/A errors will appear. I'm sure something can be set up to make those ranges dynamic as well.

      Also, I'd modify the formula you posted to incorporate the tip that Asa posted below: taking out the ,0 for the false parameter in the IF statement, allowing the formula to pickup negative numbers.

      Thanks again for showing a great way for using MMULT.

      • derek says:

        Thanks, CHOOSE works but has the same problem of having to specify each column. So If I have a data table of a hundred columns, four of which are to be chosen using the values in B22:E22, then the expression must be

        =CHOOSE(B22:E22,Data!$B$2:$B$12,Data!$C$2:$C$12,Data!$D$2:$D$12,Data!$E$2:$E$12,[...and so on until],Data!$CW$2:$CW$12)

        An absurdly long expression.

        I tried defining the four columns out of a hundred as a named range, but still no result. But if I array-entered the named range in an actual spreadsheet range somewhere, and pointed the MMULT expression at that, it worked! This is so frustrating, when Excel demands a range and rejects an array expression, but accepts the array expression as long as it's displayed in a range.

    • strickep says:

      Seems to me that the "if" statement isn't required. You'll get the same answer with: =MAX((Parameter_3=D13)*(Parameter_4=E13)*Parameter_5)... array entered. Where the parameter conditions aren't true, you still end up with zero values and, where the conditions are true, you end up with the values from parameter_5. Does the if statement add some value that I am missing? Also, this formula only works if the max is above zero. How can you handle negative values, or taking the minimum?

      • Asa says:

        If comes in handy to allow negatives like this:
        =MAX(IF((Parameter_3=D13)*(Parameter_4=E13),Parameter_5))

        You can eliminate the 0 for false part of the if(). IF will return a boolean FALSE in those cases, and MAX will ignore them.. so only values that meet your criteria parameters are considered.

  2. Jimhemm says:

    Good Afternoon Hui. I have been trying to get someone to bit on this. I am not sure if I am giving enough info or not. But here it goes.

    I am looking for a financial formula with a little twist. Let say I want to Deposit a sum of money ($500K) into an account that earns a guaranteed interest rate that varies(GIR increases every 5 years by .5% Starting with a 1.5%) This account will pay out a level amount for a stated number of years till the account = $0 (Lets say 21 years). Question... What is the level amount?

    My hope is that I can input in individual cells the lump sum deposit and the number of years the account will pay out. Then a separate cell will show the level payout amount.

    I have created a calculator and at this time I need to use the Goal Seek function. This is a step I would like to do away with if possible.

    If you are interested to take this on,or any one for that matter, please let me know and I can give whatever additional info needed.

  3. Paul says:

    Nice job... just one small comment since it tool me a minute to understand why the results were showing #VALUE!.

    Press F2 then F9 for 1 time solution or CTRL + SHIFT + ENTER to sustainable solution.

    I feel array solutions can be very powerful; however, it is an area of growth for me since I tend to struggle with the purpose of double negatives and array's seperated by "," or "*", etc.

    The graphical explanations are very helpful, e.g. http://chandoo.org/wp/2009/11/10/excel-sumproduct-formula/

  4. Asa says:

    Hi Hui and Chandoo! I'd add that if you eliminate the ,0 at the end of the IF function, the formula will work correctly for positive and negative values. As is, if the MAX would be negative, the result if the formula would incorrectly state 0. If you leave the ,0 (value if false part) out, Excel will use FALSE instead of 0 at those positions int he array, and booleans are disregarded entirely by MAX and most other aggregate functions.

  5. Kyle McGhee says:

    I was picking around this file/topic with my morning coffee and realized with the current setup of the file, the following formula will do the trick.

    =DMAX(B1:F12,F1,B21:E22)

    not sure if can be simpler than that!...1 non-array formula

    there is one change that is needed for this to work and that is to copy and paste the headers of the table directly above the criteria.
    i.e. for the sample workbook, copy B1:E1 and paste to B21:E21

  6. nick says:

    the formula is so hard to understand

  7. Nice!  Thank you for sharing this formula.  I will be sharing this with my accountant since it seems accurate and easy to work with. Thanks!

  8. Kdu Bonalume says:

    I sent a similar question to Chandoo a few weeks ago. But, in my case, I was looking for the MIN value. With the above answer, it would fail, because the 0's from the false args are smaller than the values we really want to check. So, I added a IF in the middle, asking if the criterias were false and then, replacing those 0s (and also blank cells that excel convert to zeros) with a huge value (9e99). Then it worked.

    Here is the formula:

    =MIN(- -(IF(MONTH(rngDate)=5,1,9E+99))*(IF(rngDate="",9E+99,rngDate))) Ctrl+Shift+Enter

    PS: I was looking for the MIN date of a given month in a range with multiple months (in this case, the formula will find the min date for May). 

  9. Peter says:

    Hi Chandoo,
    How could be this formula done in VBA? Not using the application.formula statement. I mean how would look like the code?
    Thanks

  10. Eric Lind says:

    I love this tip so much!

    I'm just now trying a modified version to make SmallIF, and it appears to work exactly as expected.

    Thanks Elkhan, Hui, and Chandoo! 😀

Leave a Reply