Thanks VERY MUCH for the indepth explanation. I better understand

"David Biddulph" wrote:

To clarify my reply a little, perhaps I should make it clear that I meant
that zero as an input to a boolean test (such as in your IF function)
returns FALSE, but when using the AVERAGE function it does not treat a FALSE
boolean (as in the default result of the IF function) as being zero. The
relevant part of Excel help for the AVERAGE function is:
"If an array or reference argument contains text, logical values, or empty
cells, those values are ignored; however, cells with the value zero are
included. "

Having said that, there isn't really any need for my
=AVERAGE(IF(A1:A10<"",A1:A10)), as of course the blanks are ignored anyway
so =AVERAGE(A1:A10) would do the job equally well.

The equivalence between zero and FALSE is thus rather directional. The
directionality of the equivalence is what leads to the use of the double
unary minus construct in SUMPRODUCT if one wants to convert a boolean to a
number to be multiplied.
--
David Biddulph

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
It seems to be doing exactly what you've asked it to do, so I'm not sure
what you are asking. Zero in Excel is treated as being the equivalent of
the Boolean FALSE, so your formula is asking for the average of the
non-zero values, and that's what it's given you. What were you trying to
achieve, if not that?

If you wanted an average of the non-blank, rather than non-zero, values,
try
=AVERAGE(IF(A1:A10<"",A1:A10))
--
David Biddulph


"JMay" wrote in message
...
In range A1:A10 i have values

0
0
321
123
456
0
33

444


In an adjacent cell I have (as CSE entered):
{=AVERAGE(IF(A1:A10,A1:A10))}

producing 275.40 (1377/5)

When in the formula bar of the above formla I
highlight the portion If(A1:A10,A1:A10) and press F9
I see the False, False, 321, 123, 456, False, 33, etc
showing a total of the 5 positive nunbers, but I am
not understanding how this is taking place. Can
someone help explain?

TIA,

JMay