Averaging
 

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.

Averaging
 

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

=AVERAGE(IF(D5:D32<0,D5:D32))

Does that help?
----------------------
Regards,

Ron
Microsoft MVP (Excel)

"rustygun3" wrote in message
...
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.

Averaging
 

On Wed, 6 Jun 2007, in microsoft.public.excel.worksheet.functions,
Ron Coderre said:

"rustygun3" wrote

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.

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(D5:D32<0,D5:D32)

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(D5:D32<0,D5:D32))

(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

=MIN(IF(D5:D32<0,D5:D32))

If I can get past this I also need to find a min. value with zero
values
omited.

--
Del Cotter
NB Personal replies to this post will send email to ,
which goes to a spam folder-- please send your email to del3 instead.