Menu
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 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:
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.
Hello Awesome...
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
I hope you enjoyed this article. Visit Excel for Beginner or Advanced Excel pages to learn more or join my online video class to master Excel.
Thank you and see you around.
Related articles:
|
Leave a Reply
| « Excel’s Auditing Functions [Spreadsheet Risk Management – Part 3 of 4] | Cleaning Up Imported Data – A Recent Case Study » |
At Chandoo.org, I have one goal, "to make you awesome in Excel & Power BI". I started this website in 2007 and today it has 1,000+ articles and tutorials on data analysis, visualization, reporting and automation using Excel and Power BI. 
31 Responses to “Formula Forensics No. 008 – Elkhan’s MaxIf”
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.
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.
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.
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?
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.
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.
Jim
I have seen your post several times
I am not an accountant by any means and so I would suggest you contact and accountant or review an accounting text book as it seems that should be possible.
Hui...
Hey Hui.
I do not think an accountant would have the kind of understanding in writing formulas in Excel as the people here in any way, shape or form. I could be wrong. The problem would be to call all accountants ask if they know excel formulas and then get charged.
if I find one.
I am kind of confused, I read all the time people needing help and they get it. I step out and I get see an accountant.? What is it with my need that no one wants to takle it? Is it to hard? Can it not be done?
Hi Jim,
Follow the link to my skydrive folder and open the workbook called "Annuity with changing interest rates.xlsx". It needs more work to make it more dynamic/flexible but it should get you started. I also included the formula for when the interest rate remains constant.
Let me know if the link does not work.
https://skydrive.live.com/redir.aspx?cid=9a0721b634391421&resid=9A0721B634391421!150&parid=9A0721B634391421!143
Kyle
Kyle,
Thank you, but unfortunitly It states "We can't show you that page". I would have loved to take a look at the workbook.
Is there another way that you can think of?
You can always connect with me on LinkedIn. See below.
http://www.linkedin.com/pub/james-hemmerly/34/862/b7
I'm not on Linkedin or any other networking/social media site for that matter.
The previous link directly opened the excel file in the browser; i meant to link to the folder that contains the file, so try this link first, before we try something else:
https://skydrive.live.com/redir.aspx?cid=9a0721b634391421&resid=9A0721B634391421!143&parid=9A0721B634391421!136
Even if it opens in a Browser just select
File, Download and save it your computer
@ Jim & Kyle
I have uploaded Kyles file to my Rapidshare account
You can download it here:
https://rapidshare.com/files/3058949059/Annuity_with_changing_interest_rates.xlsx
I re-posted a cleaned up version, if anyone happened to download it.
Thanks.
Kyle & Hui
Thank you for your time. Do you have this in Excel 2003. It only copies vaues into 03.
Hui,
Thanks for posting the file.
Jim,
I uploaded the 2003 version to my skydrive folder. Use the second link I posted. If you cannot access the skydrive, then you will have to ask Hui to repost the file to his rapidshare account. I cannot access such sites at this time.
Thank you Kyle.
Hui, could you grab the 2003 version and send me a Rapidshare link again?
Thanks again guys for all your help.
I wish you much success in 2012
Jim
Here is the 2003 Version on Rapidshare
https://rapidshare.com/files/1093175066/2003_Annuity_with_changing_interest_rates.xls
Thank You!Thank You!Thank You!Thank You!
Wish you all the best!
Jim H.
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/
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.
Very good tip Asa...
[...] in Formula Forensics 008 we looked at Elkhans MaxIf [...]
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
the formula is so hard to understand
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!
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).
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
[...] Finding maximum value subject to conditions – MAXIF [...]
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! 😀