I"m trying to usage the average function to average all numbers excluding 0.

Here's how i set up the function:
A B C D
7
8 JAN FEB MAR
9 Apples Bought 0 839 9,206

using this formula AVERAGE(IF(B9:D9<0,B9:D9,"")) the result should be
5022.5. This is what is shown when i click on the function bar to look at the
formula but on my spreadsheet it gives me and error #VALUE. Do you know why
this is?

FMMM

hi
the average function automaticly sums AND counts all value in a range
including zeros so you can't exclude zeros with the average function.
so.....create your own......

=IF(SUM(B9:D9)<0,SUM(B9:D9)/COUNTIF(B9:D9,"0"),"")

this would exclude zeros.

Regards
FSt1


"FMMM" wrote:

I"m trying to usage the average function to average all numbers excluding 0.

Here's how i set up the function:
A B C D
7
8 JAN FEB MAR
9 Apples Bought 0 839 9,206

using this formula AVERAGE(IF(B9:D9<0,B9:D9,"")) the result should be
5022.5. This is what is shown when i click on the function bar to look at the
formula but on my spreadsheet it gives me and error #VALUE. Do you know why
this is?

FMMM

FMMM wrote:
I"m trying to usage the average function to average all numbers excluding 0.

Here's how i set up the function:
A B C D
7
8 JAN FEB MAR
9 Apples Bought 0 839 9,206

using this formula AVERAGE(IF(B9:D9<0,B9:D9,"")) the result should be
5022.5. This is what is shown when i click on the function bar to look at the
formula but on my spreadsheet it gives me and error #VALUE. Do you know why
this is?

Your formula should work just fine as an array formula. Commit it by
pressing Ctrl + Shift + Enter, not just Enter.

Here is a slightly simpler version:

=AVERAGE(IF(B9:D9<0,B9:D9))

Thanks so much. It worked by pressing ctrl, shift, enter.

"smartin" wrote:

FMMM wrote:
I"m trying to usage the average function to average all numbers excluding 0.

Here's how i set up the function:
A B C D
7
8 JAN FEB MAR
9 Apples Bought 0 839 9,206

using this formula AVERAGE(IF(B9:D9<0,B9:D9,"")) the result should be
5022.5. This is what is shown when i click on the function bar to look at the
formula but on my spreadsheet it gives me and error #VALUE. Do you know why
this is?

Your formula should work just fine as an array formula. Commit it by
pressing Ctrl + Shift + Enter, not just Enter.

Here is a slightly simpler version:

=AVERAGE(IF(B9:D9<0,B9:D9))