Forcing DIV/0 to display "0"

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pieter van der Walt - 12 Jul 2006 10:52 GMT

I have a scorecard in Excel 2003 and instead of having DIV/0 errors
displayed is there a way of actually forcing it to display a zero/null
value? I had a looked at conditional formatting and the example only seems
to be hiding it in a different colour.

Thanks
Pieter

Reply to this Message

Bondi - 12 Jul 2006 11:01 GMT

> I have a scorecard in Excel 2003 and instead of having DIV/0 errors
> displayed is there a way of actually forcing it to display a zero/null
[quoted text clipped - 3 lines]
> Thanks
> Pieter

Hi Pieter,

One way would be to use ISERROR. SOmething along the lines of

=IF(ISERROR(A1/B1),0,A1/B1)

Where the numbers in the operation is in A1 and A2..

Another would be to tjeck if the denominator is zero something like:

=IF(B1=0,0,A1/B1)

Regards,
Bondi

Reply to this Message

Pieter van der Walt - 13 Jul 2006 07:59 GMT

Thanks Bondi - got it working!!

>> I have a scorecard in Excel 2003 and instead of having DIV/0 errors
>> displayed is there a way of actually forcing it to display a zero/null
[quoted text clipped - 19 lines]
> Regards,
> Bondi

Reply to this Message