MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Barrie, Question on your formula


Posted by AL on January 27, 2002 7:07 PM

How do I get rid of the #DIV/0 in the formula =ROUND(SUM(B107:M107)/COUNT(B107:M107)*12,2) when there is a zero in that particular cell?


Posted by Barrie Davidson on January 27, 2002 7:11 PM

Al, you are getting that error because you have no numbers in the range B107:M107. Just change the formula to read:

=IF(COUNT(B107:M107)=0,0,ROUND(SUM(B107:M107)/COUNT(B107:M107)*12,2))

Regards,
BarrieBarrie Davidson

Posted by Brian on January 27, 2002 7:34 PM

Aloha Barrie!

Posted by Barrie Davidson on January 27, 2002 7:39 PM

Re: Aloha Barrie!

Aloha Brian! What are you doing cruising MrExcel on a Sunday night?

Barrie

Posted by AL on January 27, 2002 7:40 PM

Posted by AL on January 27, 2002 7:44 PM

Barrie

The formula above only gives me the amount in B9and does not times anything by 12, What could be wrong??

AL

Posted by AL on January 27, 2002 7:48 PM

Posted by Barrie Davidson on January 27, 2002 7:48 PM

Al, I don't understand how this formula returns the amount in B9?? It only references row 107?? What am I missing? When I tested the formula it worked fine for me. What do the following parts of the formula return (paste them somewhere in the spreadsheet)?

=COUNT(B107:M107)
=SUM(B107:M107)

BarrieBarrie Davidson

Posted by Barrie Davidson on January 27, 2002 7:50 PM

See my question above (nt)

Posted by AL on January 27, 2002 8:02 PM

Barrie

It is only the total columns that were giving me problems I think because they have formulas in them and I guess that formula does not total totals that are formulas.

AL

Posted by Barrie Davidson on January 27, 2002 8:06 PM

The COUNT function should count any formulae that total. This function will count the number of cells that contain numbers and this will include a formula that returns a number. Do you want to send me a small example of what you are working on?

BarrieBarrie Davidson