Google Answers Logo
View Question
Q: Excel "Maxif" and "Minif" Function ( Answered 2 out of 5 stars,   0 Comments )
Subject: Excel "Maxif" and "Minif" Function
Category: Computers > Algorithms
Asked by: addisonbr-ga
List Price: $10.00
Posted: 21 May 2003 15:38 PDT
Expires: 20 Jun 2003 15:38 PDT
Question ID: 207026
Excel has a "sumif" function; I need the equivalent of a "maxif"
function (or a flexible workaround) for Excel 2000.  I have a
selection of securities, with market data from several different
exchanges (bids, asks, volumes, etc.).  In each row, I want a function
that takes the security from that row returns the largest (or
smallest) values from among all of the markets.  Here is an example -
columns A - D represent my data, columns E-G represent the values I
would like returned.

Stock	Bid	Ask	Volume	MaxBid	MaxAsk	MaxVol
MSFT	23	25	1000	23	24	1500
MSFT	23	24	1500	23	24	1500
MSFT	22	24	1250	23	24	1500
INTC	18	20	2000	19	20	3000
INTC	19	21	3000	19	20	3000
DELL	30	32	1500	30	31	2000
DELL	29	31	2000	30	31	2000
DELL	29	32	1000	30	31	2000
DELL	30	31	500	30	31	2000

The number of markets for each security varies and is not constant
from day to day - I get a daily data download from a third party,
which makes the manual brute force fixes to my problem useless the
next day - so the solution needs to be as hassle-free as any other
formula I might plug into my spreadsheet (like sumif for example).  I
do not want an array formula solution (for a variety of reasons).  Can
anyone provide me with a "maxif" or "minif" solution that works much
the same way that "sumif" does?  Maybe a formula trick, or a
user-defined function I can throw into VB?

Request for Question Clarification by gwagner-ga on 21 May 2003 15:59 PDT
Would the "max" function in connection with "vlookup" do the trick?
You'd create another table using the "vlookup" method and then choose
the largest one from the new table. Or would that be the array formula
solution you don't want?

Subject: Re: Excel "Maxif" and "Minif" Function
Answered By: maniac-ga on 21 May 2003 17:22 PDT
Rated:2 out of 5 stars
Hello Addisonbr,

You want to use something called an "array formula" to do what you
ask. You can get an explanation of array formulas by searching the
Excel help with the phrase
  array formula

A brief explanation on my system is under an entry titled "About Array
Formulas and How to Enter Them". You help file may be slightly
different - check for similar names if this one does not appear. Note
in particular the way you "enter" the formula. There are a few
examples under this entry as well - one very similar to the one I show

The formulas I entered into cell E2 using your sample data was...
though after you enter this as an array formula, it will be shown as
in the formula bar. Cell E2 shows the value 23 after entry.

If you get an error at this point, make sure the formula is typed
correctly and that it is entered properly (entered without {}, but
displays with {}). Copying and pasting the "formula I entered" into
the formula bar when cell E2 is selected and then doing the "array
formula entry" should work - let me know if it does not. If you are
doing your own examples, one possible problem occurs if the ranges of
values don't have the same number of entries (in this case 2:10 or
nine) in each range.

Don't do this next step until the formula is correct. Select the range
E2 through G10 and do a "Fill Down" (Edit -> Fill -> Down) and "Fill
Right" (Edit -> Fill -> Right). You will see all the values shown as
their proper values. Sounds pretty easy, doesn't it. The reason it
works is because of the funny references described below.

Let me explain how this works. First the references...
 - $A$2:$A$10 is an "absolute reference" to the first column of names.
This value will not change when you do the fill down nor fill right.
If you click on any of the cells with the formula, you will see this
in the formula bar.
 - $A2 is an absolute reference to the first column and a relative
reference to the current row. Each row will show something like $A3,
$A4, and so on up to $A10. This makes the IF function apply to the
name in the first column on the current row.
 - B$2:B$10 is a relative reference to column B and absolute reference
to rows 2 through 10. If you click on a cell in column F, this will
appear as C$2:C$10; in column G, this will appear as D$2:D$10. This is
how each column has a different value passed to the MAX function.
 - 0 is used as the "smallest value" in this case. Depending on the
type of data you are analyzing, make it a large positive number if
using MIN and a large negative number if using MAX.
Note that if you just select a region with Excel, it is usually has
relative references. This does not help you when using the two fill
operations and you have to fix the references in the formula bar to
"make them right".

The IF function is also described in Excel help, but briefly if the
first expression ($A$2:$A$10=$A2) is true, the first value (B$2:B$10)
is returned, otherwise the second value (0) is returned. In cell E2,
that value is true only for the first three rows of data and false for
the remaining items. Since this is an array formula, in cell E2, we
get an array result
In other cells, you get similar values based on the relative /
absolute references described above.

The MAX function works as you expect for the array result. In cell E2,
we get a single valued result of 23. Any function like MIN, AVERAGE,
and others that operate with array values and return a single value
will work in a similar manner.

If you know how to define names, you can replace the absolute /
relative references with a name - let me know if you want to use an
enhancement like that.

If any of this is unclear or you want to pursue some related
enhancements (such as using names), please use a clarification


Clarification of Answer by maniac-ga on 22 May 2003 05:31 PDT
Hello Addisonbr,

I wish I had seen that restriction earlier; I will attempt to read
future questions more fully. To aid the researchers reading your
questions, please place a restriction like that on a line by itself so
it appears more clearly. I also ask you to request a question
clarification in cases like this one; it allows the researcher to
retract an answer.

I apologize for the incorrect answer and to correct it I offer the

Using Visual Basic, enter the following function

Function IfMax(Selector, Match, Value)
' IfMax Macro
' Macro created 5/22/2003 by Maniac

    Max = Value.Cells(1).Value
    For N = 1 To Match.Count
        If (Match.Cells(N).Value = Selector.Value) Then
            If (Max < Value.Cells(N).Value) Then
                Max = Value.Cells(N).Value
            End If
      End If
    Next N
    IfMax = Max
End Function

In cells E2:G10 change them to read
This will refer to the function defined above - providing the three
parameters required by the function.

Let me explain how this function works. The IfMax function is passed
three parameters:
 - the cell to match
 - the range of cells to match
 - the range of cells with the value to get the maximum
Max is a variable used to keep the current "maximum value". Initially
assume the "first" value is the maximum value.
The For loop is used to walk through each value in the range of cells
to match
The first If statement is used to select only those rows where the
match is made.
The second If statement is used to update the Max value if this cell
is larger than the current Max.

IfMin is generated in a similar manner to read

Function IfMin(Selector, Match, Value)
' IfMin Macro
' Macro created 5/22/2003 by Maniac

    Min = Value.Cells(1).Value
    For N = 1 To Match.Count
        If (Match.Cells(N).Value = Selector.Value) Then
            If (Min < Value.Cells(N).Value) Then
                Min = Value.Cells(N).Value
            End If
      End If
    Next N
    IfMin = Max
End Function

This basic structure can be changed to generate averages and other
similar functions. For example, if you have varying amounts of data to
process each day, but the columns remain the same each day it is
possible to process the "whole worksheet" with a slight change. I
would use something like
  For N = 1 to ActiveCell.CurrentRegion.Rows.Count
to determine the number of rows of data and adjust the value
references to process each row in the "current region" (a range
bounded by blank rows / columns). If something like this is what you
want - please use a clarification request.

Please use a request clarification request if you have any difficulty
with this answer.


Clarification of Answer by maniac-ga on 22 May 2003 08:52 PDT
Hello Addisonbr,

I noticed a slight error in what I provided earlier. Use the following
versions instead for IfMax and IfMin. Use "recalculate now" (Ctrl-=)
in Excel if needed to refresh the values in the spreadsheet if you cut
/ paste these into Visual Basic to replace the other copies.

       Function IfMax(Selector, Match, Value)
       ' IfMax Macro
       ' Macro created 5/22/2003 by Maniac
           Max = -100000000
           For N = 1 To Match.Count
               If (Match.Cells(N).Value = Selector.Value) Then
                   If (Max < Value.Cells(N).Value) Then
                       Max = Value.Cells(N).Value
                   End If
             End If
           Next N
           IfMax = Max
       End Function
       Function IfMin(Selector, Match, Value)
       ' IfMin Macro
       ' Macro created 5/22/2003 by Maniac
           Min = 100000000
           For N = 1 To Match.Count
               If (Match.Cells(N).Value = Selector.Value) Then
                   If (Min > Value.Cells(N).Value) Then
                       Min = Value.Cells(N).Value
                   End If
             End If
           Next N
           IfMin = Min
       End Function

Request for Answer Clarification by addisonbr-ga on 28 May 2003 14:46 PDT
Maniac - just got back in town to see your further responses.  The
function code and explanations are exactly what I was looking for;
thank you for the update.  I'd like to edit the rating for your answer
if possible, but I wasn't able to figure out how to do that - if you
have a suggestion please let me know.

Clarification of Answer by maniac-ga on 29 May 2003 04:38 PDT
Hello Addisonbr,

I am glad the revised answer provided what you need.

As far as I know, there is no direct way for you to change the rating.
The FAQ at
in particular states " cannot revise your rating. Therefore, you
should only rate an answer after you have completed the "Answer
Clarification" process...."

It may be possible for one of the support staff to revise the rating
for you. Try sending a message to or
and see what they say.


Request for Answer Clarification by addisonbr-ga on 29 May 2003 20:07 PDT
To Google Answers team - I'd like to change the rating on this answer
to four stars if possible, if the rating itself can't be changed
please take this comment into subjective account.  Maniac, thanks

Clarification of Answer by maniac-ga on 30 May 2003 04:35 PDT
Hello Addisonbr,

Thank you for the kind words and good luck with your work.

addisonbr-ga rated this answer:2 out of 5 stars
Unfortunately, I specifically said "I do not want an array formula
solution (for a variety of reasons)"... But 2 stars instead of one
because I guess if I actually did want an array formula answer, it's a
good explanation.

There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  

Google Home - Answers FAQ - Terms of Service - Privacy Policy