Eliminating #DIV/O!

  • Thread starter Thread starter MCheru
  • Start date Start date
M

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?
 
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.
 
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
 
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))
 
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
 
How clever. Thank you!

Dave Peterson said:
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.
Click to expand...
 
Works like a charm! Thank you for you're help.

David Biddulph said:
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))
Click to expand...
 
Back
Top