Eliminating #DIV/O!

[MCheru]

I have this formula =SUM(AVERAGE(N4:N200)). Right now #DIV/O! appears if
N4:N200 does not have any data to report. Is it possible to modify this
formula so that the cell the formula is in reports a zero “0†if it does not
have any data to report?

 

[Dave Peterson]

You don't need the =sum() function.

=average(n4:n200)
will do the same.

=if(count(n4:n200)=0,"No numbers",average(n4:n200))

would check to make sure that there's numbers to be counted/summed/averaged.

 

[francis]

try
=IF(ISERROR(SUM(AVERAGE(N4:N200))),0,SUM(AVERAGE(N4:N200)))

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

 

[David Biddulph]

May I ask you a question in return? What do you think the SUM function does
for you in that formula?
In what way do you expect =SUM(AVERAGE(N4:N200)) to differ from
=AVERAGE(N4:N200) ?
Have you looked in Excel help for the SUM function to see what SUM does?

You might try =IF(COUNT(N4:N200),AVERAGE(N4:N200),0)
or =IF(ISERROR(AVERAGE(N4:N200)),0,AVERAGE(N4:N200))

 

[Gord Dibben]

As Dave pointed out the use of SUM is a waste of space.

Also, ISERROR will mask all errors which may not be desirable.

IMO the use of ISERROR is used too much with little thought given to
correcting the reason for the error.

Trap for 0 in some other way..........see Dave's post for that.


Gord Dibben MS Excel MVP

 

[MCheru]

How clever. Thank you!

You don't need the =sum() function.

=average(n4:n200)
will do the same.

=if(count(n4:n200)=0,"No numbers",average(n4:n200))

would check to make sure that there's numbers to be counted/summed/averaged.

 

[MCheru]

It is helpful. Thanks for the tip! I appreciate you're help.

 

[MCheru]

Point taken. Thank you. Interesting point regarding the ISERROR.

 

[MCheru]

Works like a charm! Thank you for you're help.

May I ask you a question in return? What do you think the SUM function does
for you in that formula?
In what way do you expect =SUM(AVERAGE(N4:N200)) to differ from
=AVERAGE(N4:N200) ?
Have you looked in Excel help for the SUM function to see what SUM does?

You might try =IF(COUNT(N4:N200),AVERAGE(N4:N200),0)
or =IF(ISERROR(AVERAGE(N4:N200)),0,AVERAGE(N4:N200))