[Solved] Average not showing 0 answer

[totalsum]

Hi all,

what is wrong with this:
=IF(((AVERAGE(C7:C18))=0;"";(AVERAGE(C7:C18)))

when C7:C18 have no values yet i get #div/0!
but i ask for "" ,like show nothing.

anyone know a better way to get a blank cell when the outcome of average is 0?

[RusselB]

An average is the summation of numbers entered, then divided by the quantity of numbers entered.
Since you have no entries, there is nothing for the summation or the division, thus the average function is trying to do division by zero (which is what that error is)
If you want the formula to return a blank when there are no entries, you could use

Code: Select all

=if(or(iserr(average(c7:c18));average(c7:c18)=0);"";average(c7:c18))

[eremmel]

Or take advantage of the fact that when avg == 0, then sum==0 , and empty cells count as 0 for sum() as well:

Code: Select all

=IF(SUM(C7:C18)=0;"";AVERAGE(C7:C18))

[Lupp]

Code: Select all

=IF(COUNT(C7:C18)=0;"No values!";AVERAGE(C7:C18))

This is the reasonabl solution, imo. Since an average of 0 (zero) is a valid result, it should actually be reported.
If there are specific reasons to suppress the result if it's 0, the following formula would do:

Code: Select all

=IF(COUNT(C7:C18)=0;"No values!";IF(SUM(C7:C18)=0;"";AVERAGE(C7:C18)))

[totalsum]

Thanks for the quick replies you all!
And the solutions all work fine for me.

like this i'll be an expert in no time!?!!
Thanks