Max() Dmax() Looking for MaxIf() Equiv.

[Derek P]

I have a table of data in which I need the max based on which field if next
to the cell. There is Sumif(), Countif() etc...which allows me to complete
the math depending on what the first entry in the row is. I can not find the
Max() equivilent though, and It would be useful in many occasions.

Table Ex.

Yellow....2
Red........3
Red........4
Yellow....7
Blue.......1

On the next page i want the cells to look like (seperated by | )

Yellow | Maxif(max_range, variable_range, Variable)
Blue | Maxif(.....variable is A2 this time...)
Red | Maxif(.......................................)

The closest I could find is DMax but in the constraints section you must
have the column header as part of it, which does not let you search the 2nd
and 3rd entry if you copy down with the references. (my list has over a 100
unique entries...)

If anyone could help that would be appreciated.

 

[Shane Devenshire]

Hi

=MAX(IF(A1:A9="x",B1:B9,""))

This is an array formula, so you need to press Shift+Ctrl+Enter to enter it,
not Enter.

 

[Derek P]

Thanks!

I have always considered myself a proficient Excel person, but I have never
known of an array formula!

Can you just give me some insight on how the formula is processed?

It appears that it looks at the variable (lets choose red since it has a
duplicate in my ex.) and then inserts the corresponding cell into the array,
then looks for the next red and inserts that into the array...

So Array = { }
Find Red, lookup 3 --> Array = { 3 }
Find next Red, lookup 4 --> Array = { 3 , 4 }
Find next Red, None --> Evaluate Max{ 3 , 4 }
4.

I'm guessing the Brackets outside the equation changes the Range of A1:A9
into an Array with those corresponding values? (same for column B?)

Sorry, just never seen this and want to clarify how it works.
Thanks!!

 

[Shane Devenshire]

Hi,

arrays are an extremely useful tool in Excel, but Microsoft's help system
does little to address this topic. I have written a 1200 page book on the
subject but never pursued publishing it.

Considering the formula
=MAX(IF(A1:A9="x",B1:B9,""))

A1:A9="x" returns an array {TRUE,FALSE,...} depending on whether the test
passed or not.
The IF portion then return an array {12,"",16,"","Title","",...)
In other words if the first array returns TRUE at a given postion, then the
IF returns the appropriate item from B1:B9 otherwise it returns "".
Finally, the MAX function evaluates the results. The MAX function might be
looking at =MAX({"";38727;"";"";""}) for example. Normally, the IF would
only return one result so if you do not enter the formula as an array Excel
will use just one of the results of the IF, which may or may not return
anything of value, it may even return an error. But by pressing
Shift+Ctrl+Enter Excel knows to evaluate the IF for all the entries.

Many array formulas can be duplicated using the SUMPRODUCT or on occasion
MMULT functions, but not all. Also there are a number of functions in Excel
which require array entry to work properly, and these are documented in the
help system, for example LINEST, and FREQUENCY.

If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[smartin]

Great explanation, Shane.

Have you considered making your book available online? The world could
surely use more information on the subject!

One tiny addition for the OP: There is a tool under Tools | Formula
Auditing | Evaluate Formula that can be very helpful to show how Excel
works through array formulae (or any formula for that matter). To keep
this tool handy, enable the Formula Auditing toolbar.

 

[Derek P]

Thank you all for your help!

That was a great explination.

And yes, there is very little on using formualas as Arrays. In fact in all
my searching through the help/contents literature I have not once come
accross it.

Thanks again.