Formula Forensics No. 008 – Elkhan’s MaxIf

Formula Forensics No. 008 – Elkhan’s MaxIf:
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 1^st, 3^rd, 5^th, 8^th and 11^th cells all contain values that match our Criteria

So the 1^st, 3^rd, 5^th, 8^th and 11^th 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 1^st, 3^rd, 5^th, 8^th and 11^th values contain values and the remaining 2^nd, 4^th, 6^th, 7^th, 9^th and 10^th 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.