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
