Summing cells that contain '#DIV/0!'

Posted by Garry on June 13, 2000 7:03 AM

I have a spreadsheet that contains a summary sheet, this sheet collects data on a preformatted page from other sheets in the workbook. The other sheets are based on monthly sales figures, as such, they do not contain data at the moment, but they do contain formulas. This being the case, on each sheet, I get #DIV/0 errors. This is okay, and I understand why, but on the summary sheet, the referencing cell picks up the #DIV/0 entry, which when totaled at the bottom of the page, affects the total figure and again gives #DIV/0.

Can anyone throw any light on a way around this.

HELLLP

Posted by Zif on June 13, 0100 7:50 AM

You can get round this by changing the formulas that give the #DIV0 message as follows:
=IF(OR(A2=0,A2=""),0,A1/A2)

That way, you won't get the error when summarising the data.