Errata....

On Nov 7, 11:31 pm, I wrote:
On Nov 7, 8:24 pm, John wrote:
By the way, how do you enter an arrayed function...or what does that
mean exactly? Is it just a formula that, after you enter into the Fx bar,
you hit ctrl+shft+enter instead of just enter?

Yes, that is what we mean by "commit" with ctrl+shift+Enter.

That much is correct. But lots of mistakes in details in the rest of
my explanation.

An array formula is one that operates on an array of values. In the
example, if A1,A3,A5,etc (all odd rows) are positive, then:
=average(if(A1:A20=0, A1:A20))

To gain some appreciation of the difference between an array and non-
array formula, try entering the above example as both -- that is,
commit with Enter one time (non-array formula) and with ctrl+shift
+Enter another time (array formula). Each time, use Tools = Formula
Auditing = Evaluate Formula to step through the evaluation of the
formula.

But we cannot enter an array of that form.
[....]
To verify that it is an array formula, select the cell and
be sure that the entire formula after "=" is enclosed in
curly braces (i.e. ={...}).

What I should have said is: An array formula is entirely embraced in
curly brackets (i.e. {=...}). And what I meant to say is: We cannot
enter an array formula by typing the outer-most curly braces
explicitly.

To gain some insight (or not) into the vagaries of Excel, try the
above example with the following formulas committed as both non-array
and array formulas.

=sum(if(A1:A20=0,A1:A20))

=sumproduct(if(A1:A20=0,A1:A20))

As array formulas, both return the same result, as they should. But
as non-array formulas, when A2 is negative, note that the SUMPRODUCT
formula fails with #VALUE, whereas the SUM formula works (albeit not
with the intended result). Use Evaluate Formula to see why (klunk!).
Go figure! Change A2 to non-negative, and you will see that both non-
array formulas return the same non-erroneous result (but again, not
what was intended).