How to make #DIV/0! present as either 0 or an empty cell?

[MSEscapee]

Hi, I have a spreadsheet calculating percentage differences which we use as a template. It is for balance sheet asset valuation comparison between an item’s purchase cost and it’s current value.

In the master file, none of the ‘Current Value’ cells have any values in but the formulas are all set up. So when someone opens the master file and then saves it with a different filename they can populate relevant cells in the ‘Current Value’ column with the current values of the assets. In the next column along the cells show the percentage difference between the cost value and the current valuation.

Assets are being regularly bought and sold so the master file is regularly being updated. We have different groups of rows for different asset categories.

For some time we have been putting up with seeing #DIV/0! in percent difference cells in rows where the source cells are blank, but it is unsightly and a bit distracting. Of course everybody knows that some would argue that you can’t divide any value by 0 so “error” is the correct result - and others would argue that you can and the correct result is always 0.

We would be happy to set Calc so that any figure divided by 0 equals 0 and we can’t see a problem with doing that.

Our desktop calculators show 0 as the result if anything is divided by zero and it has never caused us a problem.

So the question is, can we either set up Calc to show anything divided by 0 giving a value of 0, or is there a formula to get a percentage difference cell to be either blank or have a value of 0 if either or both it’s source cells have either a zero in or are blank?

Thank you

[PKG]

=iferror(your formula;0)

[MSEscapee]

Thank you very much! Ill try that on Monday.

[MSEscapee]

I couldn’t wait so just tried it, but it didn’t work.

However, I tried some alternatives and the following did work (where =R12/D12 was our original total formula):

=IFERROR(R12/D12,0)

So I guess that would be generically represented as:

=IFERROR(your formula,0)

If I am incorrect and my accidentally discovered alternative could cause problems please say.

Thanks

[karolus]

advice: To continue, press any key

hum, where is this »any«-key ?

[erAck]

IFERROR() catches all errors though and you usually don’t want that. The better way is to ask whether the denominator is 0, so assuming A1 is the numerator and B1 is the denominator, use

=IF(B1<>0;A1/B1;0)

[MSEscapee]

Thanks, that sounds even better. I’ll try that as well.