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