Could you please help - as I have a weird situation that when I enter a number 22222.09482 in cell then I see a different number 22222.0948199999 in the formula bar. Below is the snapshot of the problem.

Sample error

I see the same behavior when I enter the following numbers:

22222.09482
33333.09482
44444.09482
55555.09482

but when I enter 11111.09482 and 66666.09482, 77777.09482.. until 99999.09482 then they shows correctly. I am not sure is this related to rounding ? I didn't setup any rounding profiles. Could you please help me in resolving the issue.

  • 1
    Neat find, does same for me - can you just use ROUND()? =ROUND(A1,5), then copy/paste the data as Values and remove the original numbers? – BruceWayne Oct 15 at 18:31
  • 1
    @BruceWayne, interestingly the ROUND() shows the correct digits but the copy/paste as value takes it back to the original issue! – Rey Juna Oct 15 at 21:11
  • 1
    @ReyJuna - Then just do ROUND(A1,5) again on the pasted values. Then copy/paste those as values, and Round() again, then copy/paste. ... :P ...that's interesting, and it looks like @EugenRieck has the reasoning. Nice question! – BruceWayne Oct 15 at 21:13
  • 1
    Do you actually care about the difference between 22222.09482 and 22222.0948199999? It's a difference of 5 parts in 100 trillion. – Russell Borogove Oct 16 at 1:35
  • 4
    Is floating point math broken?. However that's likely a bug, since double has enough precision that when rounded shows the correct values as most people expected – phuclv Oct 16 at 6:38

It is a bug.

Excel uses the usual IEEE double-precision representation, according to other answers. Its precision is 53 significant binary digits, which corresponds to roughly 16 decimal digits.

It is always "safe" to display the first 15 significant decimal digits. In the sense that any decimally "presented" number given with 15 digits can be safely distinguished from the numbers obtained by changing the 15th decimal figure by one. For example, the 15-digit numbers:

22222.09481 99999
22222.09482 00000
22222.09482 00001

map to three distinct double-precision numbers. None of these three will be "neighbors" in the double-precision representation, in this particular case.

So, confusing the first two in the user display, is a bug of Excel.

In fact, in this domain (between 16384 and 32768), the absolute precision is 2-38, and the following numbers are representable:

...
22222.09481 99998 96571 9714760780334472656250000
22222.09481 99999 00209 9502831697463989257812500 <-- the one closest to what Excel showed to the user
22222.09481 99999 03847 9290902614593505859375000
22222.09481 99999 07485 9078973531723022460937500
22222.09481 99999 11123 8867044448852539062500000
22222.09481 99999 14761 8655115365982055664062500
22222.09481 99999 18399 8443186283111572265625000
22222.09481 99999 22037 8231257200241088867187500
22222.09481 99999 25675 8019328117370605468750000
22222.09481 99999 29313 7807399034500122070312500
22222.09481 99999 32951 7595469951629638671875000
22222.09481 99999 36589 7383540868759155273437500
22222.09481 99999 40227 7171611785888671875000000
22222.09481 99999 43865 6959682703018188476562500
22222.09481 99999 47503 6747753620147705078125000
22222.09481 99999 51141 6535824537277221679687500
22222.09481 99999 54779 6323895454406738281250000
22222.09481 99999 58417 6111966371536254882812500
22222.09481 99999 62055 5900037288665771484375000
22222.09481 99999 65693 5688108205795288085937500
22222.09481 99999 69331 5476179122924804687500000
22222.09481 99999 72969 5264250040054321289062500
22222.09481 99999 76607 5052320957183837890625000
22222.09481 99999 80245 4840391874313354492187500
22222.09481 99999 83883 4628462791442871093750000
22222.09481 99999 87521 4416533708572387695312500
22222.09481 99999 91159 4204604625701904296875000
22222.09481 99999 94797 3992675542831420898437500
22222.09481 99999 98435 3780746459960937500000000 <-- the one closest to what the user types
22222.09482 00000 02073 3568817377090454101562500
22222.09482 00000 05711 3356888294219970703125000
22222.09482 00000 09349 3144959211349487304687500
22222.09482 00000 12987 2933030128479003906250000
22222.09482 00000 16625 2721101045608520507812500
22222.09482 00000 20263 2509171962738037109375000
22222.09482 00000 23901 2297242879867553710937500
22222.09482 00000 27539 2085313796997070312500000
22222.09482 00000 31177 1873384714126586914062500
22222.09482 00000 34815 1661455631256103515625000
22222.09482 00000 38453 1449526548385620117187500
22222.09482 00000 42091 1237597465515136718750000
22222.09482 00000 45729 1025668382644653320312500
22222.09482 00000 49367 0813739299774169921875000
22222.09482 00000 53005 0601810216903686523437500
22222.09482 00000 56643 0389881134033203125000000
22222.09482 00000 60281 0177952051162719726562500
22222.09482 00000 63918 9966022968292236328125000
22222.09482 00000 67556 9754093885421752929687500
22222.09482 00000 71194 9542164802551269531250000
22222.09482 00000 74832 9330235719680786132812500
22222.09482 00000 78470 9118306636810302734375000
22222.09482 00000 82108 8906377553939819335937500
22222.09482 00000 85746 8694448471069335937500000
22222.09482 00000 89384 8482519388198852539062500
22222.09482 00000 93022 8270590305328369140625000
22222.09482 00000 96660 8058661222457885742187500
22222.09482 00001 00298 7846732139587402343750000
...

To elaborate further, try typing 22222.09482 in one cell, and typing 22222.0948199999 (five trailing nines) in another cell. Excel should pick the two IEEE representatives indicated by the arrow above. And I think it does, because you can calculate the difference of these two cells to get 9.82254E-11. But both are shown in the same way.

If Excel had shown the first 17 digits, that would be helpful to pick out exactly what IEEE number is "underneath" the decimal number. In that case:

22222.0948199999 --> 22222.09481 99999 00
22222.09482      --> 22222.09481 99999 98

But showing 15 digits rounded in an incorrect way, is misleading and unhelpful.


Before anyone claims it is intentional, then why does 8.7 not show the same behavior? The nearest double-precision number to 8.7 is:

8.69999999999999 93

so it should show as 8.69999999999999 if this was intentional. But it does not.

  • 9
    You are right, it's a bug. The algorithms for converting a binary floating point number displaying the fewest number of significant digits are hard, but known. It looks like someone missed some important detail. – Mark Ransom Oct 16 at 20:53
  • 2
    @Ruslan I have seen a lot of threads on floating-point arithmetic, on Stack Overflow and other SE sites, and often people give an answer with all kinds of general truths and notions about (binary) floating point, without relating to the actual numbers and verifying if the behavior is IEEE compliant. I think I could post the question "Why does 2.1 + 2.2 return 4.80000001?" and get many answers saying that is what I must expect when I use floating point. – Jeppe Stig Nielsen Oct 17 at 7:10
  • 1
    @benshepherd: the LibreOffice screenshot does not show what the Excel screenshot shows – Thomas Weller Oct 17 at 11:26
  • 1
    @JeppeStigNielsen: 2.1+2.2 = 4.8? That's just 0.5 off. It's normal. – Thomas Weller Oct 17 at 11:27
  • @MarkRansom: so, Excel really implements that itself? Excel is not implemented in C++ or something which does that floating point calculation for it? Is that what you're saying? Or is the bug in C++ already, so it affects millions of applications? – Thomas Weller Oct 17 at 11:32

Excel stores numbers in IEEE 754 binary 64-bit floating point format. The key is "stores" - the change from decimal to binary takes place whenever a number is stored, not just when it is used in an actual calculation.

A nice article on this is at Understanding Floating Point Precision, aka “Why does Excel Give Me Seemingly Wrong Answers?”

It is possible to make a spreadsheet program that would handle really big numbers with a lot of significant digits. But it isn't terribly practical. Excel could have been designed to use the IEEE 754 decimal128 format, which allows for 34 decimal digits - more than enough to store 22222.09482. But instead it uses the far more common binary64 Double Precision format, which has 53 bits of precision, which is just under 16 digits. While you might think that would be enough for a number with only 10 digits in it, conversion from decimal to binary complicates things a bit - i.e., 2222209482 can be stored 100% correctly as a binary64 number, but 22222.09482 can not.

Keep in mind that typically spreadsheets are used for financial data, which typically does not require so many digits of precision, or for "what if" modeling in a variety of scenarios, where a super-high level of precision is not needed. There are certainly other tools (and probably other spreadsheet programs, but I haven't searched lately) that either by default or by special configuration settings can use a larger numeric format, but Excel isn't one of them.

For those who point out that LibreOffice handles this better, looks can be deceiving. See this post for more details. It seems that LibreOffice handles large numbers slightly differently but has the same basic 64-bit floating point representation with similar problems.

  • 3
    This answer would be better if it also explained the observation that some numbers with decimals are shown exactly as entered - such as 11111.09482 in the OP's example. – andrew Oct 16 at 1:37
  • 11
    Just for fun: 22222.09482 is stored as 1.0101101100111000011000010001100001111110011111000000₂ * 2₁₀^(10000001101₂ - 1023₁₀) in IEEE 754, or in other words as exactly 1.35632902954101553 * 2^14, which is 22222.0948199999984353787904. – YoYoYonnY Oct 16 at 10:46
  • 1
    Binary64 (a.k.a. double precision) is more than enough for a number with ten decimal digits. Of course it cannot be represented exactly, but most numbers, including 0.2 and 0.1 cannot be represented exactly (the fraction 1/5 (one fifth) has an infinite recurring binary expansion). However, Excel shows things in a confusing way. – Jeppe Stig Nielsen Oct 16 at 19:36
  • 2
    "Keep in mind that typically spreadsheets are used for financial data, which typically does not require so many digits of precision" - this is generally very wrong - financial data require precise behaviour in the sense "what you type is what you expect will remain there", You are right with the decimal/binary conversion etc, but this particular argument is invalid! For financial data we usually use some kind of Money or BigInteger format, which often uses decimal storage format. – Honza Zidek Oct 16 at 21:58
  • 4
    This answer is not sufficient to explain what happens. We're observing a bug in Excel, where the number is formatted wrongly. 22222.09482 and 22222.0948199999 are distinct binary64 numbers. – Ruslan Oct 17 at 6:12

When doing it's calculations, Excel needs to find a good internal binary representation for the numbers it uses. In your case, it uses a floating point number, and as a matter of fact this data format has a (very good) approximation for your number, but no exact match. So if you don't explicitly tell Excel which output format to use, it will do a "best effort", resulting in an output that is closer to the internally calculated value, but is not exactly the text you enter.

Just to make this clear: Understanding, that the text you entered represents a number and converting the sequence of digits into a number already fulfills the definition of "calculation" from above.

EDIT

I have not made it clear enough, that I consider the choice of using a 64 bit floating-point representation actually a good one: Excel is not ment as a tool for Scientists, where a rounding error in the 11th digit after the decimal point makes a big impact, but accountants don't want their processing speed reduced by a factor of millions to acommodate a source of inexact calculation that might manifest itself in numbers that they never use.

If you use a spreadsheet program for what it was designed for and use explicit output formatting to make sure those effects never make it into the visible realm, you will be fine.

  • 1
    Comments are not for extended discussion; this conversation has been moved to chat. – DavidPostill Oct 16 at 17:36
  • 1
    This answer is not sufficient to explain what happens. We're observing a bug in Excel, where the number is formatted wrongly. 22222.09482 and 22222.0948199999 are distinct binary64 numbers. – Ruslan Oct 17 at 6:12

When I enter 11111.09482 and 66666.09482, 77777.09482.. until 99999.09482 then they shows correctly. I am not sure is this related to rounding ? I didn't setup any rounding profiles. Could you please help me in resolving the issue.

Some numbers can be represented correctly and some can not.

Set the displayed precision appropriately for your calculations and use the round() function.

  • Explanation:

  • Solution:

    You can frequently prevent floating point rounding errors from affecting your work by setting the Precision as displayed option before you apply a number format to your data. This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

    1. Click File > Options.
      In Excel 2007: Click the Microsoft Office Button Office button image, and then click Excel Options.
      Button Image

    2. Click Advanced, and then under When calculating this workbook, select the Set precision as displayed check box, and then click OK.

    3. Click OK.

    4. In the worksheet, select the cells that you want to format.

    5. On the Home tab, click the Dialog Box Launcher Button image next to Number.
      Launcher Button Image
      Excel Ribbon Image

    6. In the Category box, click Number.

    7. In the Decimal places box, enter the number of decimal places that you want to display.

    Tip: To minimize any effects of floating point arithmetic storage inaccuracy, you can also use the ROUND function to round numbers to the number of decimal places that is required by your calculation.

  • Journal of Accountancy - "Bugged by Excel's calculation errors":

    Certain odd numbers create repeating binary decimals, and when those repeating digits are cut off after 15 places, the binary number does not convert back accurately to the intended numeric value. As an example, in all editions of Excel, the formula 22.26 − 21.29 should yield 0.97, but instead yields 0.970000000000002. Try it, and remember to increase your column width and decimal places so you can see the calculation problem.

    Such errors are typically considered insignificant or immaterial because they rarely manifest into meaningful calculation errors; nonetheless, here are two measures you can take to eliminate potential floating decimal point errors:

    1. The ROUND function. Use Excel’s ROUND function to round your calculated values to the desired decimal place, thereby eliminating any possibility of 15th-digit anomalies. For example, the formula =ROUND(-21.29 + 22.26,2) accurately yields 0.97.

    2. Precision. You can turn on Excel’s Precision as Displayed option to force all formulas to truncate and round calculated values based on the visible digits.

    To turn this option on in Excel 2013, 2010, and 2007, select File (or the Office Orb), Options (or Excel Options), Advanced, and in the When calculating this workbook section, check the Set precision as displayed box, and then click OK.

    In Excel 2003, 2002, and 2000, from the Tools menu, select Options, and on the Calculation tab, under Workbook options, check the Precision as displayed box, and then click OK.

  • 1
    The explanation is irrelevant: the difference between expected and obtained numbers is 28 ULP – way too much for natural roundoff error. The "solution" just hides a real bug. – Ruslan Oct 17 at 9:28
  • It would be great if you could post that, along with a few links supporting your answer, as your own answer instead of a comment. That way it would come to the attention of user954171 and people could vote on it. Can you provide a link to the Bug Report? – Rob Oct 17 at 19:51
  • There already is an answer with all the relevant mathematical details. Dunno of any bug reports about this though. – Ruslan Oct 17 at 19:56

As I'm sure you know, computers internally work only using zeros and ones (a.k.a. bits) and have a fixed number of bits to represent a value (usually 64 bits nowadays). That means that the number of different values that can be represented is 2 to the 64th power. That's a huge number, sure, but the number of possible values is finite, so not all numbers can be represented. When it encounters a number it can't represent exactly, it automatically gets replaced by the closest one that it can represent. That is what you're seeing.

  • Are you stating it's because of number base conversion, or because limited amount of memory per value? In both cases, your reasoning is wrong. Technically, it is perfectly possible to have arbitrary precision and arbitrary length numbers - assuming infinite memory. To give an example, there is GNU bignum. Furthermore, there is no inherent need to encode numbers in a lossy format. So neither is computer science at fault, nor binary numbers. It's only the Excel developers who chose a number encoding that is lossy w.r.t. user input. – phresnel Oct 16 at 6:55

Computers do their math in binary, and almost always use floating point for non-integer values. The only fractional values which can be represented precisely in floating point must be a sum of some combination of fractional powers of 2 (1/2, 1/4, 1/8, 1/16, 1/32,...) terminating at the designed-in precision limit (usually 53 bits). These values don't always have a tidy or exact representation in decimal, and conversely, not all fractional values you can represent exactly in decimal will have an exact representation in binary. For example: 0.1. It can't be represented as a sum of fractional powers of 2 that does not go on forever.

When you enter a decimal value into your spreadsheet, it will be converted and stored in binary, and cases such as you described, will become the closest approximation that can be represented in binary. When displayed, it is converted back to decimal, again requiring an approximation, which might not convert back to exactly the same representation you entered.

Why 53 bits (give or take)? Because the typical standard for storing "double precision" floating point uses 64 bits, in which there is a mantissa (also called significand), a sign indicator, and an exponent. The exponent is usually allocated 10 bits, the sign takes one, leaving 53 for the mantissa. This is for storage. Calculations are usually done using 80 bits and rounded back.

There are situations where computers will work in base 10, especially when working with monetary values where rounding artifacts are not acceptable.

  • 2
    I disagree with a blanket All computers statement. There were many computers, admittedly most no longer in active use except as museum pieces, that used decimal numeric formats for storage & calculations. There are also BCD - Binary Coded Decimal - used in a number of more recent CPUs, including limited usage in Intel CPUs. None of that is relevant to the question at hand - the method used by Microsoft Excel to store numbers, which is all binary. But "All computers" isn't correct. – manassehkatz Oct 16 at 1:15
  • I see the change from "All computers" to "Computers" - I'd go with "Almost all computers" or "Most modern computers" or something like that. I know - I'm picky. But so are computers :-) – manassehkatz Oct 16 at 2:19
  • @manassehkatz All digital computers are fundamentally binary. BCD is just a base-10 structure on top of binary to facilitate decimal arithmetic. – Zenilogix Oct 16 at 2:33
  • While all digital computers are fundamentally binary, there actually were quite a few in the early days that were decimal based. But I agree that BCD is essentially a base-10 structure on top of binary to facilitate decimal arithmetic. – manassehkatz Oct 16 at 2:49
  • This answer sounds as if it's the computer's fault. But it isn't. You can represent any number precisely you could write on paper (represent 1/3 exactly? easy when storing as fraction, e.g.). It's really about what representation the Excel authors chose to represent numbers. – phresnel Oct 16 at 8:35

Like many above have said, this is an internal representation error. Excel has made the choice for double precision, 64 bit floating point numbers. This gives you 264 possible values. The real numbers domain contains an infinity of values, so when you try to use one that cannot be represented by Excel, it will use the closest that can be represented.

I've seen comments saying that given infinite memory, any real number can be represented. True, but there is no such thing as "infinite memory" so this is a moot point. Others have stated that Excel could have used larger internal representation, for example 128 bit. True, but, it turns out that computers are better at doing mathematical operations on numbers represented with the number of bits that matches the processor's bus size. So a 32 bit computer will be fastest at mathematical operations on 32 bit numbers and a 64 bit computer will be fastest at mathematical operations on 64 bit numbers. If and when there will be a 128 bit computer, then we can expect Excel to move to 128 bit number representation. That will still provide a very large but limited set of numbers that can be represented. The same effect will still be present, just with different numbers.

If your concern is about how the numbers look in the spreadsheet, then using a set precision (number of decimals) will give you consistent results. If you concern is about the difference between the number you type and the actual number stored by Excel, you are right to be concerned. The difference is real and the error will be carried through any calculations you make. I am afraid that you are stuck with this error. This is a limitation of Excel, not a bug as some have stated. It is not likely to change any time soon, so if it is not acceptable for you, I suggest you look for another spreadsheet application that can represent numbers with higher precision. But keep in mind that should you find any such application, the limitation is still there. It's just the size of the error that is different.

Your Answer

 

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.