Eliminating #DIV/O!

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?
 
D

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.
 
F

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
 
D

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))
 
G

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
 
M

MCheru

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.
 
M

MCheru

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))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Copy addition formula of non-consecutive horizontal cells 2
Supress DIV/0 2
Curly Brackets 2
Transfer data between two tables 3
#DIV error 2
#DIV/O! 5
#DIV/0! ERROR 2
Averaging a column that contains text 4

Top