MAXIF, MINIF functions
Excel doesn’t provide MAXIF and MINIF functions, but you can roll your own with array functions.
=MAX((B1:B9=”Tom”)*(A1:A9)) array entered will return 7, the max for Tom
=MIN(IF(B1:B9=”Tom”,A1:A9,”")) array entered will return 1, the minimum for Tom.
You’ll note that the MIN function is a little less straightforward. The problem with MIN and array formulas is that it almost always returns zero. For instance, if the MIN was written like the MAX, then any rows with ‘Harry’ will return zero and that will be the minimum value in the array. To get around that, an IF function is used to return the values for the name we want and an empty string for any other names. The empty string (like any string) is ignored by the MIN function.
Update: Another application - John comments
I have a column of numbers (16520, 16345, 19456, 16123, 16201, 19325, 19113, 16350, 16787, 16675) and want to use a function to find the highest number in the 16000 group. I think I need to use the MAX and IF Worksheet Functions, but I can’t get it. Any help?
Try this formula
I would think that you’d want to use an If function either way (at least potentially). I say this because, as it is written, the Max formula would not return a proper value if all of Tom’s values were negative. In that case, it would return zero.
That might not be an issue, but in cases when you’re not quite sure, it’s best to be safe.
emmmm. does it not give the highest value , i.e from -1 to - 5
max = -1
min = -5
as expected yes?
Good point Andy. The non-Tom’s would return zero and that would be the max of the whole array if Tom’s were all negative. I never do it, but it would be a good practice to start.
No, Ross. If all of Tom’s values were negative, then the Max function, as written, would return zero.
ok, i think see what your saying now, but is that true, i get a #value! on both formulas? - ahrr - i’m confused
can someone explian how these forumlas work please. see i’m thinking,
MAX((B1:B9=”Tom”) — finds the tom in the 7th row
*(A1:A9)) - - x 1(true) by the 7 in the a col same row.
[ ps. i dont know why it would do this, i'm guessing!]
hence 1x-7 = -7
Am i thick?
why dont the formulas work for me?
Ross, in order for the formulas to work, they must be entered as array formulas. In other words, press to edit the formula, and then press . The formula should now have brackets {} around it that were put there by Excel rather than by you.
In answer to your other question (why it works), we need to break the formula down differently than you did.
The formula: =MAX((B1:B9=”Dick”)*1) (again, when entered as an array formula) will return a 1, which, as you surmised, is how Excel treats the True value.
Therefore, =MAX((B1:B9=”Tom”)*(A1:A9)) will take the largest value of an instance where “Tom” is in Column B multiplied by the value that is in column A. The Max function applies after the multiplication occurs. Therefore, it is saying Max(1*1,2*0,3*0,4*1,5*0,6*0,7*1,2*0,4*0) or Max(1,0,0,4,0,0,7,0,0), which is, of course, 7.
Now you can also see why the formula, as written, does not work properly if all of Tom’s values are negative. Max (-1,0,0,-4,0,0,-7,0,0) is zero.
Brilliant Andy! Thanks very much indeed.
DOH! My message was modified because of HTML.
You need to press F2 to edit the formulas, and then press CTRL+Shift+Enter to create them as array formulas.
I had enclosed those terms in brackets <> and the html took them as tags. Oh well.
- of topic but, i can use HTML tags in maessages - if needed!! - such as
Bold etc.
useful
thanks fellas
It’s intresting that it effected the footer having not colsed the tag correctly.
Any way - sorry
I have used HTML in here successfully. What I meant was that, normally when I refer to array formulas, I put the [Ctrl][Shift][Enter] keys in the angled brackets that you use for HTML. When I did that in this post, it read them as being HTML tags. Therefore, instead of seeing “Press < .F2.>” (without the periods) it just showed up as “Press “.
This page is insanely useful!
BUT I can’t get it to work. Here’s what I have
=MAX((A:A=DATE(2004,7,12))*(M:M))
I have no idea why I would press F2, it does nothing. I did press ctrl-shift-enter to make it an array formula, but as soon as I did I got a #NUM error. Before I made it an array formula I would get the contents of each cell in M:M when the date was specified. Thanks!
Usarian M. Skiff
Alright now I’m REALLY frustrated.
I copied my query data onto a blank page and specified which rows to start and stop in.. ie.
=max((a1:A1000=F$3$)*(M:M))
it worked.
I did the same thing both ON the query page and USING the query page, and I got that #NUM error again!
UMS: Two things about array formulas that you should know. 1. You can’t use whole column references like A:A and M:M. I have no idea why they designed it like that, but they did. 2. All of the “arrays” in your formula must have the same number of rows. So
=max((a1:a1000=$f$3)*(m1:m1000))
Note that they don’t have to be the same rows (1:1000), but they have to have the same number of rows.
Great stuff!!!!!
Just a couple of points…
1. Column references did work for me (Excel 97) but …
2. Only if the array formula was on the same tab as the data. I cannot get this to work across tabs - even when I tried the syntax a1:a1000, etc.
Is there a way around this?
Brett - I don’t think that you can do it across sheets. You could try using a named range, but I’m not sure if that would work either.
UMS - [F2] lets you modify a previously entered formula. If you are typing the formula for the first time, then it would not be needed.
Its really great information
Thanks a lot
Find Maximum two values in a array
i wana to find maximum two numbers in a array through max if formula
MAXIF
COL D COL J
AGENT PROFIT
JOHN 500
JIM 400
JOHN 200
JACK 1000
JOHN 750
I’M TRYING TO DETERMINE WHAT JOHN’S HIGHEST PROFIT WAS. BUT WHEN I USED YOUR EXAMPLE
=MAX((D1:100=”JOHN”)*(J1:J100))
THE RESULT COMES BACK: 100
HOWEVER, WHEN I CLICK ON THE EQUAL SIGN TO EDIT THE FORMULA IT SAYS THE RESULT IS 750. THAT IS CORRECT BUT MY SPREADSHEET CELL DISPLAYS 100.
WHY?
THANKS
JOE NALLIE
800 835-3247
JOE.NALLIE@GMAIL.COM
When I copied your example into my Excel 2003 spreadsheet, I got errors. A few things I ensured to get a good result:
1) define numeric cells as some kind of numeric and A/N cells as general or text
2) make sure numeric entries, especially your “750″, don’t have any leading or trailing blanks, which will make them non-numeric and not evaluated in the MAX function
3) I found myself retyping “JOHN” in the formula and it was after that that the formula worked.
4) Don’t forget the ctrl+shift+enter.
I am trying to create a formula where zero is not taken into account. How would I do that? For example: if I have the numbers 4.5, 3.3, .30, 1.2, 0, 2.2
Now, even though zero is one of the answers, in my case, a zero means I do not have data for that cell. Therefore, I want all the zeros to not be accoutned for so that in this case the answer would be .30.
Any ideas?
Thanks.
=MIN(IF(rng=0,”",rng))
enter with control+shift+enter
how would I go about doing a min(if(……over about 20 or so sheets of info ?….the formula i’m trying to get to work looks like this……
=Min(if(sheet1:sheet20!b4=”text”,sheet1:sheet20!ac8)
this only returns a #REF so idk i’m stumped
Hi There,
Just thought I’d post up something that is probably obvious but might be worth mentioning:
The MAXIF equation given above i.e. ‘{=MIN(IF(B1:B9=”Tom”,A1:A9,””))}’, works fine when the ranges are specified, in this case the condition ranges from ‘B1′ to ‘B9′ and the values range from ‘A1′ to ‘A9′.
However, if you wish to do the MAXIF on an entire column you cannot use ‘B:B’ and ‘A:A’ as the function will not work. To overcome this you can simply put in the range as 1 to the max number of rows a spreadsheet can contain (65536) minus 1. You need to subtract one to prevent excel automatically replacing something like ‘A1:A65535′ with ‘A:A’.
Also, you can overcome the zero problem in the MAXIF and MINIF functions by putting in the ‘else’ argument into the IF statement. For the MAXIF function this should be the lowest number possible in the spreadsheet e.g. -999999999999999999999, and for MINIF it should be the highest number possible.
Here are some examples:
MAXIF: ‘{=MAX(IF(A2:A$65535=”A”,B2:B$65535,-999999999999999999999))}’
MINIF: ‘{=MIN(IF(A2:A$65535=”A”,B2:B$65535,999999999999999999999))}’
This page has saved my voluntary work project for a charity. MASSIVE thanks to all the contributors!
Scanning through all this, I do not quite see the function I need - i.e.,
I need to find the lowest not-zero numeric value in a non-contiguous array of cells all containing numbers or zero values.
Have I missed it. I can’t seem to see my way through to the solution although this MIN IF format seems likely to lead to it.
E.G., each of the cells to be included contains a formula resulting in a number.
E2 result is 1, G2 result is 1, I2 result is zero, K2 result is zero, M2 result is zero, O2 result is .67
In cell P2, I want the function to return the lowest non-zero value in the range - i.e., .67 from cell O2.
Cheers, woggie
Paul: =MIN(IF(E2:O2<>0,E2:O2,”")) array entered (control+shift+enter)
Dick, can this be used with multiple conditions? I tried using the “AND” Function after the “IF” which did not work.
my formula:
=MIN(IF(AND(B2:B313=824,E2:E313=”MOT_HSDPA”),AO2:AO313,”"))
Any ideas?
Die_Another_Day
Well I kluged a nested if statement to make it work…
=MIN(IF(B2:B313=824,IF(E2:E313=”MOT_HSDPA”,AO2:AO313,”"),”"))
Any better ideas?
Die_Another_Day
I need to take this MAXIF a step farther…
Given the example at the top of the page where the max is calculated for “Tom”,
I need to add formula in column C (for each row) that will state that that particular row is the max for that particular person (it will say “Personal Max”). the other rows for column C will be blank.
So the results would have the string “Personal Max” in column C on rows 5,6,7 since they are the highest entries for those people.
I can do it with two formula columns now (one of the maxif array function, the next checks if the number equals the max for that entry). How do I do it in one column? I dont know how to combine my if statement with my array formula.
Chris
Chris:
Array entered in C1 and copied down:
=IF(A1=MAX(IF(B$1:B$9=B1,A$1:A$9)),”Personal Max”,”")
A non-array alternative to the “MAXIF” function:
=SUMPRODUCT(MAX((B1:B9=”Tom”)*A1:A9))
Unfortunately, this method will not work for a “MINIF” if all the values are positive.
Biff
thanks andy. this helped a lot.
{=IF(B1=MAX($B$1:$B$18*(($A$1:$A$18)=A1)),B1,”")}
i have an array formula entered in a particular column. where there is no adjacent data for the array to calculate, the result is an error #N/A. i normally use an IF statement to “hide” any such errors, such that, as an example only, “If(C4=”",”",c4*2)”.
I have tried to combine this style with an array formula and it doesn’t work: (entered with CSE)
“=IF((VLOOKUP(S79,TRIM(CodeFlags),2,0))=”",”",(VLOOKUP(S79,TRIM(CodeFlags),2,0)))”
Has anybody got a fix so that my users do not get errors on their reports?
ajm: VLOOKUP won’t return “” if there’s an error. Try ISNA(VLOOKUP(…))
I once used for non-array MAXIF the following (I call it “Inceremnetal MAXIF”)based on the exampe above:
1. On Cell C1 type =A1
2. On Cell C2 type =MAX(0,A2-SUMIF(B$1:B2,B2,C$1:C2)) and drag on column C
3. On cell D1 type =SUMIF(B:B,B1,C:C) and drag on column D
This will not work on MINIF or MAXIF with negative MAX
There seems to be a bug with certain nested array formulas. To see this, type the following array formula {max(cell(”row”,a1:a4))}. Excel will return 1, rather than 4. Maybe not a bug so much as a distinction in exactly when excel iterates on the array.
Robert,
According to Help the Cell function will return the info for only the top-left cell in a range.
Robert: The Cell function won’t return an array, so it’s only returning the first value.
This is much simpler in a Pivot table with one column Value set for Max and another set for Min. Where row labels Column A.