I was doing some calculating of my budget on my Excel spreadsheet today, and found what looks to be a problem with the way Excel stores numbers. But I can't quite figure it out. I've attached a cut-down version of the spreadsheet which still shows the strange behaviour.
On the spreadsheet, in column A, I have a sequence of numbers - some negative, some positive. They're grouped together in a named range named 'Amounts'.
The problem arose when I tried to sum them and do some validation.
In column C, I've worked on the Amounts named range. In C2, I have the sum of the numbers in the Amounts named range that are positive. In C3, I have the sum of the numbers in the Amounts named range that are negative. In C4, I have the sum of all the numbers in the Amounts range. So the value in C2+C3 should = C4, right? As you can see in C6, where I test that C2+C3=C4, that's not the case. A quick look shows that in C4, a value of '1' has snuck in in the 15th (I think) place. Where did it come from, since all the numbers in the named range don't have it?
In column D, I have the same thing, but instead of using the Amounts named range, I just used the cell range. It has the same problem as column C.
In column E, I have the same thing, but this time I manually entered the numbers. No phantom '1's appear, but E2+E3 still doesn't = E4 - why not?
In column F, I did the same thing as column E, but this time instead of manually entering the value in F4, I calculated it (sum(F2:F3)). And this time a phantom '2' snuck in. Why? And - even stranger - despite the phantom '2', the test says that F2+F3=F4 is true!
Can anybody explain this weirdness?
Bookmarks