divivde by zero

[kevcar40]

hi
i have the following formula that reurns the efficiency of a team
this works great until a zero score is entered and 60 minutes is
entered as a downtime


(C27/($C$147*(C142-C25)/C142)

the cells are as follows
c27 = 0 score
C147 = 78 target
C142 = 60 minutes available
c25 = 60 downtime

How can i put a zero in to the result cell if the result is #div/0!

thanks for your help

kevin

 

[Paul B]

Kevin, here is one way,
=IF(ISERROR(C27/($C$147*(C142-C25)/C142)),0,C27/($C$147*(C142-C25)/C142))

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Pete_UK]

Alternatively:

IF(($C$147*(C142-C25)/C142)=0,0,C27/($C$147*(C142-C25)/C142))

Hope this helps.

Pete

 

[kevcar40]

Thanks both
both work the problem was not the formula but the fact i was trying
hide the cell value if the result was zero
as follows
=IF(C27/($C$147*((C142-C25)/C142))=0%,"",if(C27=0,0,(C27/($C$147*(C142-C25)/C142)))
when i remove the first aprt of th statement it works fine
as follows

=IF(C27=0,0,(C27/($C$147*(C142-C25)/C142)))
If i can hide the value so be it

thanks kevin

 

[Pete_UK]

Thanks for feeding back.

Pete

 

[Pete_UK]

Actually, if you want the zero to be hidden you can do this - use
Format | Conditional Formatting to select a foreground colour of white
if the cell content is equal to 0.

Pete