#DIV/0! Error

[paul29berks]

I have this formula:

=IF(AND($D$174=0,$Z$174=0),CG$174*($Z$169+$AP$169)/($Z$174+$AP$174),IF(AS$174=0,CG$174*($BN$169+$CD$169)/($BN$174+$CD$174),CG$174*($BN$169/$BN$174)))

However there are instances where I am dividing by 0 and receive the following error - does anyone know how I can prevent this by building into this formula?

Thanks

 

[Jon von der Heyden]

=IF(ISERROR(IF(AND($D$174=0,$Z$174=0),CG$174*($Z$169+$AP$169)/($Z$174+$AP$174),IF(AS$174=0,CG$174*($BN$169+$CD$169)/($BN$174+$CD$174),CG$174*($BN$169/$BN$174)))),0,IF(AND($D$174=0,$Z$174=0),CG$174*($Z$169+$AP$169)/($Z$174+$AP$174),IF(AS$174=0,CG$174*($BN$169+$CD$169)/($BN$174+$CD$174),CG$174*($BN$169/$BN$174))))

 

[WernerLouw]

Try using the isserror function.


=IF(ISERROR((your formula here)),"",(your formula here))

it is easy if you use the ASAP utiities add-in you can get it on the recommended add-ins section.

 

[paul29berks]

Sorry to be a pain have included as:

IF(ISERROR((IF(AND($D$14=0,$Z$14=0),CF$14*($Z9+$AP9)/($Z$14+$AP$14)),"",(IF(AR$14=0,CF$14*($BN9+$CD9)/($BN$14+$CD$14),CF$14*($BN9/$BN$14))

but unaccepted. Can you help?

 

[Fergus]

Hi Paul,

=IF(ISERROR(IF(AND($D$14=0,$Z$14=0),CF$14*($Z9+$AP9)/($Z$14+$AP$14),IF(AR$14=0,CF$14*($BN9+$CD9)/($BN$14+$CD$14),CF$14*($BN9/$BN$14)))),"",IF(AND($D$14=0,$Z$14=0),CF$14*($Z9+$AP9)/($Z$14+$AP$14),IF(AR$14=0,CF$14*($BN9+$CD9)/($BN$14+$CD$14),CF$14*($BN9/$BN$14))))

But see also : http://www.mrexcel.com/board2/viewtopic.php?t=126320