Crossfooting problem

[Barry Katcher]

O.K., imagine an asininely simple spreadsheet of three rows and three columns, A1:C3.

In A4:C4 I have simple sum formulas for each column. In D1:D3 I have simple sum formulas for each row.

Now, in order to crossfoot (double-check) the sum of the rows equals the sum of the columns, in cell D4 I have the following basic formula:

=IF(SUM(A4:C4)=SUM(D1:D3),SUM(A4:C4),"ERROR")

O.K., this is standard procedure, and it works - most of the time. Occassionally, though, I'll get the "ERROR" return even if everything is O.K. No division to cause rounding errors, yet, when I add an =ROUND statement to the formula:

=IF(ROUND(SUM(A4:C4),2)=ROUND(SUM(D1:D3),2),SUM(A4:C4),"ERROR")

it gives the correct return, not the ERROR message. Can anyone tell me why this error occurs when not using ROUND, and why only sometimes? As I said above, there is no division or percentages involved.

_________________
I used to have delusions that I was a werewolf, but I'm much better noOOOWWWWWWWW!
This message was edited by Barry Katcher on 2002-10-28 15:01

 

[PaddyD]

Barry,

the problem stems from the precision errors that arrise when you implement floating-point arithmetic in a binary environment. See:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q78113

and

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q48606

paddy