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.

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Derek P" wrote:

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.

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" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Derek P" wrote:

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.

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

"Derek P" wrote in message
...
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" wrote:

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.

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire


"Derek P" wrote:

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.

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.

Shane Devenshire wrote:
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

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.

"smartin" wrote:

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.

Shane Devenshire wrote:
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