Eliminating #DIV/O!
 

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?

Eliminating #DIV/O!
 

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 wrote:

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

Eliminating #DIV/O!
 

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

--
Hope this is helpful

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

Thank You

cheers, francis










"MCheru" wrote:

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?

Eliminating #DIV/O!
 

How clever. Thank you!

"Dave Peterson" wrote:

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 wrote:

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

Eliminating #DIV/O!
 

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

"francis" wrote:

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

--
Hope this is helpful

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

Thank You

cheers, francis










"MCheru" wrote:

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?

Eliminating #DIV/O!
 

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

"Gord Dibben" wrote:

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

On Tue, 17 Mar 2009 12:04:01 -0700, francis
wrote:

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

Eliminating #DIV/O!
 

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

"David Biddulph" wrote:

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


"MCheru" wrote in message
...
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?