Forum Discussion
Excel giving incorrect answer to simple multiplication
This is undoubtedly due to some quirk of Excel, but I was using Excel to illustrate how fast doubling increases things. I was using the old story about the person who would work for $ .01 for the first day, .02 for the second, .04 for the third, etc. My formula is essentially A2=A1*2 carried through 64 cells. The cells are all formatted as currency with two decimal points. The calculations are all correct until day 51. Day 49 = 5,629,499,534,213.12. Day 50 should end with $xxxxxxxx.24. Instead it ends with .20. Day 51 (should end with .48) ends with .50 and so on through the rest of the spreadsheet. This probably has something to do with floating-point calculations, but there should be a way for it to just work with the actual number.
3 Replies
HelloBrettDes,
This behavior is caused by Excel’s use of binary floating-point arithmetic, not a calculation bug. Excel stores numbers using the IEEE 754 standard, and many decimal fractions like 0.01 cannot be represented exactly in binary. Each time you multiply by 2, the tiny approximation error doubles. By day 49–51, this accumulated error affects the last cent, which is why you see .20 instead of .24 and .50 instead of .48. Formatting as Currency only changes the display, not the stored value. Microsoft documents this here: https://learn.microsoft.com/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result. To avoid this, either work in whole integers (e.g., cents) and convert to dollars for display or round at each step using =ROUND(A1*2,2). Either approach prevents cumulative rounding errors.
Excel calculates using 16 digits (actually, it uses a 17th digit to round the 16th). Day 49 has 16 digits, day 50 has 17 digits. This means the last digit is rounded.
Excel calculates using 16 digits (actually, it uses a 17th digit to round the 16th). Day 49 has 16 digits, day 50 has 17 digits. This means the last digit is rounded.
