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.

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))

is effectively:

=average({A1,A3,A5,...,A19})

But we cannot enter an array of that form.

The problem with array formulas is they are difficult to edit. After
editing, always remember to "commit" with ctrl+shift+Enter. If you
get #VALUE, select the cell, press F2, then press ctrl+shift+Enter.
Sometimes, you will not get #VALUE, but you will not get the result
you expect either; it depends on the function(s) used in the array
formula. Confusing! 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. ={...}).