Averaging

[rustygun3]

HELP! I need to average a colum of numbers omiting zero values, I found the
formula in the help menu and copied it using my cell ranges when I try to
enter formula it returns an error message with the false value highlighted
this is my formula:
=AVERAGE(IF(d5:d32<>0,d5:d32,**)) it will not accept the ** as a false value.
If I can get past this I also need to find a min. value with zero values
omited.

 

[Ron Coderre]

Try this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter], instead of
just [enter]):

=AVERAGE(IF(D532<>0,D532))

Does that help?

 

[Del Cotter]

As Ron says, you don't need to enter special characters in the "if
false" section: it's optional. Just leave it out of the expression
completely (leave the comma out as well), and the expression evaluates
as FALSE if the condition is false. If you block out a range with this
array function:

=IF(D532<>0,D532)

you'll see the FALSE values appear. And since AVERAGE ignores FALSE in
an array, you're all set.

Try this ARRAY FORMULA (committed with [ctrl]+[shift]+[enter], instead of
just [enter]):

=AVERAGE(IF(D532<>0,D532))

(if only charts ignored FALSE values as well! sadly, charts plot FALSE
as if it were 0.00)

Also, the answer to the second half of your question is