11

My wife and I have a spreadsheet to work out our finances at the beginning of each month and this month has a strange figure in cell F9.

The formula in cell F9 is ="+ £"&-'Sue''s Outgoings'!C13&" Shortfall" which should output + £57.35 Shortfall but it outputs + £57.3500000000001 Shortfall.

Formula in cell F9
image of formula in cell F9

To try and troubleshoot this, I put =-'Sue''s Outgoings'!C13 in cell F13 which outputs exactly what is in 'Sue''s Outgoings'!C13 which is 57.35. (see second image below)

Formula in cell F13
image of formula in cell F13

All cells in Sue's Outgoings sheet are formatted to Currency format and I temporarily reformatted all cells to General to see if there was a hidden strange figure in there and there was nothing. All figures in Sue's Outgoings sheet are straight figures and not results of formulae.

What other reason could there be for the spurious addition of 0.0000000000001 to the result?

16
  • 69
    because Excel uses floating-point math. See Is floating point math broken?
    – phuclv
    May 4, 2021 at 8:51
  • 4
  • 3
    It's precisely the same reason that 2/3 is sometimes reported as 0.666666667 -- a tiny bit more than the actual value when converted into decimal. May 5, 2021 at 12:26
  • 8
    Your comment to @DavidSchwartz makes it clear that you do not understand that Excel calculates in floating point, associates that value to the cell, then applies formatting when displaying that value. Your cell, "+ £"&-'Sue''s Outgoings'!C13&" Shortfall" fetches the value, not the displayed form, immediately stringifies it for concatenation, then applies its cell's formatting. See the DOLLAR function as an example of converting a value to a currency formatted string. May 5, 2021 at 13:44
  • 2
    @ChrisRogers : I'm looking at all the comments. You keep presenting surprise that floating point is being used for currecny calculations and that you believe currency formatting on 'Sue''s Outgoings'!C13 has any impact on the stringification. Cell formatting only affects presentation; it has nothing to do with the computation. May 5, 2021 at 14:07

4 Answers 4

35

Excel uses floating point math, that means sometimes there are such digits hidden in your values. (https://docs.microsoft.com/en-US/office/troubleshoot/excel/floating-point-arithmetic-inaccurate-result)

Those are generally displayed correctly, however when you convert them to text, those indeed may cause issues, one workaround is to use ROUND in your formula:

="+ £"& ROUND(-'Sue''s Outgoings'!C13,2) &" Shortfall"
13
  • 22
    That's a slightly different thing, mind..
    – Caius Jard
    May 4, 2021 at 9:20
  • 17
    @ChrisRogers actually decimal based calculations are nearly non existing in computers. They almost always use binary and just read or display them in decimal.
    – jjj
    May 4, 2021 at 9:32
  • 14
    @ChrisRogers - it is working as expected, as long as you expect computers to use floating point maths and therefore have limitations in talking down to us dumb humans in decimal. Lot of decimal numbers which seem "simple" to us cannot be represented properly in binary, just like 1/3 cannot be represented properly as a decimal number. If you were treating this as a number, Excel would deal with the issue and you would not get to see the problem, as proved by your third screenshot where Excel hides the discrepancy from you. Because you are showing numbers as text, that code did not kick in here.
    – AdamV
    May 4, 2021 at 11:18
  • 21
    There's a reason why money in particular is often handled by using integers scaled by a factor of 100 or by storing the fractional part separately ( database example ). That has its own set of problems, but floating-point isn't one.
    – A C
    May 4, 2021 at 19:51
  • 5
    @AC Or, historically, by using BCD (and indirectly making interoperability remarkably more complicated by inventing EBCDIC as a result...). May 5, 2021 at 0:16
14

To add a fix rather than a workaround to the problem, following the Microsoft Docs page linked by @MátéJuhász:

If you are working on spreadsheets which are purely money based like am here, or anything else to one fixed number of decimal places, you could set the worksheet to "precision as displayed".

Method 2: Precision as displayed

In some cases, you may be able to prevent rounding errors from affecting your work by using the Precision as displayed option. This option forces the value of each number in the worksheet to be the displayed value. To turn on this option, follow these steps.

  1. On the File menu, click Options, and then click the Advanced category.
  2. In the When calculating this workbook section, select the workbook that you want, and then select the Set precision as displayed check box.

For example, if you choose a number format that shows two decimal places, and then you turn on the Precision as displayed option, all accuracy beyond two decimal places is lost when you save your workbook. This option affects the active workbook including all worksheets. You cannot undo this option and recover the lost data. We recommend that you save your workbook before you enable this option.

Then there is no need to worry about floating point math errors needing to be worked around with ROUND().

6
  • 4
    The explanation in this answer is exactly backwards. The setting being recommended injects errors into calculated values. May 5, 2021 at 14:24
  • 5
    Rounding is the process of replacing a correct value with an incorrect value. You are recommending inserting error into computations to resolve a formatting issue. May 5, 2021 at 14:29
  • 2
    @EricTowers now you are not understanding the correction of floating point errors. Take the result and round it to 2 decimal places. You get the result expected. Again, I understand that is done front-end in the usual sense and back-end in the file and sheet coding will be the "accurate" number according to binary calculations otherwise finer accuracy cannot be maintained with higher decimal place figures. We will have to beg to differ with each other I believe as you think I don't understand when I know I do May 5, 2021 at 14:34
  • 5
    "You get the result expected." No. You see the result expected. The value in your spreadsheet is now wrong. May 5, 2021 at 14:38
  • 1
    It depends on what type of calculation you’re doing. If all you’re doing is adding, subtracting and multiplying by integers, then rounding to 2 decimal places for currency is fine. As soon as you start doing calculations that include fractions of a penny, you will lose that information and introduce error. I think it’s better that the decision to round is explicit in the formula. Of course, you also need to consider the potential biases introduced by always rounding 0.5 upwards (as Excel does). May 5, 2021 at 19:20
9

You are asking Excel to display a number in the middle of a text string without telling it what format to use or in any other way prompting it to realise you might want it to figure out how best to display this.

Rounding is an option here, but generally I think it is good practice here to just unambiguously tell Excel how you would like this number to appear in the middle of this text (I am using a simple two decimal places format here:

="+ £"& TEXT(-'Sue''s Outgoings'!C13,"#.00") &" Shortfall"

Alternatively, you could do the whole thing just by pulling the number in and using a custom number format like this (note it displays both positive and negative numbers as positive, with different labels):

+ £#,###.00 "excess";+ £#,###.00 "shortfall";0.00 "balanced";@
7
  • 1
    Hmmm, I can see where you are coming from, but when all the figures in a table are entered as figures, and not formulas, plus the figures entered are all to 2 decimal figures, the result of a SUM() total should be in a maximum of 2 decimal figures. I can understand that I might get 1 or zero decimal places, (e.g. £0.25 + £0.75 = £1) but 13 decimal places? Therefore having a figure at 13 decimal figures is not what you would expect if the calculations are done correctly. Hence, the answer given by @MátéJuhász May 4, 2021 at 12:06
  • 2
    Because even entering only one decimal place can result in a binary number which is really long and still inaccurate. Eg 0.1 (1/10) is 0.00011001100110011001100110011 in binary to 29 bits of precision and still not very close: 0.099999999627471. "Precision as displayed" is great if everything is always entered as data, or not calculated with any more precision (eg always adding/subtracting, never dividing or multiplying by non-integers.
    – AdamV
    May 4, 2021 at 14:57
  • 2
    @ChrisRogers What would you expect 1/3 to be displayed as? What if 35/100 has no exact representation in the internal format the computer uses just as 1/3 has no exact representation in fixed-precision base 10 decimal? May 5, 2021 at 12:28
  • @DavidSchwartz why are you talking of 3rds? Where is it mentioned that 3rds or even 35/100 were involved? May 5, 2021 at 13:02
  • 3
    @ChrisRogers 57.35 is 57 and 35/100. It's easier for humans to understand thirds because they likely have some experience with representing 1/3 and 0.333333 and 2/3 and 0.666667 such that 2 * 1/3 doesn't produce precisely 2/3 and so on. May 5, 2021 at 16:53
1

Long ago, computer programmers decided to not store decimal numbers perfectly.

Theoretically, the square root of two can be stored in a computer.
This is for the same reason that when you use a pen and paper to write the following symbol, it does NOT require an infinite amount of paper and ink.

SQUARE_ROOT_OF_TWO

In practice, computer programmers do not use precise decimal numbers. As such, every time that you add, subtract, multiply, or divide, you introduce rounding error.

If you want to learn more, you can Google for "IEEE single precision floating point number."

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .