Help understanding CSE results

[JMay]

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

 

[David Biddulph]

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

 

[Bernard Liengme]

Let's begin with the IF part: the syntax for IF is =IF(condition,
true_value, false-value)
The 'condition' will general be something like A1>10; and will return TRUE
or FALSE
But Excel is just as happy with numbers: where any non-zero value is taken
to be TRUE and Zero is taken as False. So I could use IF(A1,"a number", "not
a number or zero") in place of IF(A1<>0, ....)

So you IF says, "If the number in the range are non-zero return the
numbers". You do not say what to do otherwise (you have no 'false-value'
argument) so Excel returns FALSE for the zero values (and the blank). This
give the array: false, false, 321, 123, 456, false, 33, false, 444

Then AVERAGE gets you the average of this array - it ignores the non-numeric
"False" values
BTW the way: instead of using F9, if you have XL2003 or 2007 use the
Evaluate Formula tool to watch the steps.

Any help?
best wishes

 

[David Biddulph]

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.

 

[JMay]

Thanks VERY MUCH for the indepth explanation. I better understand

 

[JMay]

Thanks VERY MUCH for the indepth explanation. I better understand