16 thoughts on “Does the location of a formula affect its accuracy?”

1. It appears to be related to the order of the summands in the calculation of cell A9.

   Changing A9 from =SUM(A1:A7)-A1 to =SUM(A2:A7,A1)-A1 changes FALSE to TRUE but changing it to =SUM(A1,A2:A7)-A1 yields FALSE again.

2. It’s got something with the floating point nature inside SUM(). It cant be because of where the formula is located, surely!
   In the first configuration I can change it from FALSE to TRUE by swapping 124.9 with 198.08

3. I tried the same thing as Rob van Gelder, and it yielded similar results. In fact, I swapped the first number with the last, and it becomes true. I’m wondering if it doesn’t have something to do with the number sequence…

4. I cannot believe that the formula location has anything to do with it. Rather, I think Rob has nailed it: the order in which the individual terms are summed is important, because of the floating point error, and the two examples are not adding the terms in the exact same order.

   Arithmetically, these should be the same, but due to the limits of IEEE floating point math, at any given operation you may get tiny errors. Thus, while it does not matter arithmetically which order you do the sums, the IEEE thing makes the order influential because one ordering of the terms may introduce an error, while another ordering will not.

5. Instead of using the SUM function, manually wrote the additon formula ie. =A2+A3+A4+A5+A6+A7 in Cell A1 and =(A8+A2+A3+A4+A5+A6+A7)-A8 in Cell A9. It didn’t matter if I used the first formula in Cell A1 or A8, I still come up with a FALSE result.

6. I think order matters in how floating-point error accumulates.

   If you add 0.1, 0.1, and 100.1, you get 100.3

   If you add 100.1, 0.1, and 0.1, you get 100.3, and Excel will tell you the results are the same — but they are not.

   The first result is a double with a hex value of 4059133333333333, and the second is 4059133333333332.

7. I should add that =match(first result, second result, 0) returns #N/A, even though = first result = second result returns True.

8. Very, very old news.

   Read the ANSI/IEEE Standard 754-1985 and discover that floating point addition and multiplication are NOT associative. Which does mean the odd results cited are due to evaluation order.

   If you need the range of values provided by floating point, you have to use nearly equal tests, e.g., =ABS(x-y)<5E-5 where x and y could have 2 places to the right of the decimal point. OTOH, if you need exact arithmetic, you have to use fixed point. There are arbitrary precision programs, but they're much slower than floating or fixed point programs like Excel.

   Interestingly, OpenOffice/LibreOffice Calc, Zoho and Edit Grid all return TRUE for both scenarios. Google Docs produces the same results as Excel. FWIW, I tried out the same calculations in various nonspreadsheet programs, and they all produced TRUE for both scenarios. So it looks like Excel is somewhat limited in this regard, and Google Docs faithfully mimics Excel's limitations. Then again, do experience Excel users really expect accuracy from Excel?

9. It is old news, but it also has some interesting twists and turns that (as far as I know) are not adequately documented. For instance you will often get different results for the equality of two floating point numbers in VBA to what you get on the spreadsheet, and even enclosing a spreadsheet formula in brackets can give different results.

   I have recently discussed these things at:

   http://newtonexcelbach.wordpress.com/2011/12/20/when-does-35-not-equal-35/
   and
   http://newtonexcelbach.wordpress.com/2012/01/07/comparing-floating-point-numbers/

   The latter link has some VBA functions to compare numbers to any chosen precision, and both have interesting discussion and further links in the comments.

10. By the way, Lotus 123 Rel 9.5 also gives TRUE (or rather 1) for both scenarios in the OP.

11. Many of us will have had similar problems when doing double type maths using variants created by who knows what functions and formulas. It happens a lot in complex Access or SQL Server queries although most situations are not really effected by precision rounding, (or checked accurately enough). Variables that have not had hard types assigned to them, eg. hard coded numbers in queries for example can produce surprising results, that appear as bad calculations for example FX rates or interest rates that get severely rounded due to a USD – USD=1 calculation that sets the data type precedent for the rest of the calculation. This means that the order of execution (often not as obvious as it sounds) forces the precision, creating exactly this sort of contradiction.

12. @Doug – interesting. 123R5 (kept for sentimental reasons) gives 0 and 1, respectively, for the two scenarios. Gnumeric also gives the same results as Excel. Are you absolutely sure about 123R9.5? In 123 if you enter the formula @SUM(A7..A2), the formula won’t change to @SUM(A2..A7), so I have to wonder whether 123 iterates through the range in the former bottom-up.

13. fzz – yes, I have double checked. It’s not surprising that they behave differently because at some stage Lotus went from 80 bit precision back to 64 bit. I thought all the Windows versions were 64 bit, but maybe they made the change when they went from Rel 5 to Rel 97 (which was a year or two before 9.5).

    Another oddity (in Excel) is that if you make the final number 131.81000000001 then:
    =SUM(A2:A7) (in A1) displays as 8392.51000000000
    and =SUM(A1:A7)-A1 (in A9) displays as 8392.51000000001
    but =A9=A1 displays as true!

    I have been discussing some similar peculiarities on my blog recently, which generated some good feedback, so anyone interested might like a look. I posted a comment here with a link yesterday, but it’s still in moderation, so just click on my name at the top of the comment.

14. I sure miss Lotus 123. Wish I still had the 5.25 inch diskettes.

    /rnlr

    Cheers,

    Bob

15. Pingback: Does the location of a formula affect its accuracy? « Excel Notes from Anthonli

16. Is very old news. We used to do assignment questions demonstrating this effect in the numerical methods course I did in the early 80s. Always risky when doing differences that result in small values.

    At heart it is mainly due to a number that is exact in decimal (for example 0.1) may not be exact when expressed as a binary decimal number (0.1 decimal is 0.0001100110011001100110011001100110011001100110011001101 in binary). As there is a limited number of bits the number is rounded and hence an approximation of the “true” value. These small difference can become important when doing the type of calculations shown above.

    I odd a lot of these type of calculations when I add check into my models. I often have to add a round function to ensure that the checks don’t have false positives.

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.