divivde by zero
 

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

divivde by zero
 

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

"kevcar40" wrote in message
oups.com...
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

divivde by zero
 

Alternatively:

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

Hope this helps.

Pete

divivde by zero
 

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

divivde by zero
 

Thanks for feeding back.

Pete

divivde by zero
 

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