Excel formula bar displaying different value

[atpthomas]

Hi,

I'm entering a value 27625.1213 in an excel cell. The formula bar is displaying this as 27625.1212999999. 

How do I fix this? I need the formula bar to display the exact same value I'm entering.

Tried enabling the setting 'set precision as displayed', changing the cell formats, using the excel round function but none of it helped.

[#2]

Hello @atpthomas,

Try these:
A1: 27625.1212
A2: =$A$1+0.0001
then copy cell A2, and paste it as VALUE in cell A4

image below:

[#3]

@Rr_ 

27625.1212+0.0001 only appears to work because Excel formats only up to 15 significant digits, rounded.

But with that expression in A2, note that

ISNUMBER(MATCH(VALUE("27625.1213"), A2, 0)) returns FALSE.

That is because the binary value of 27625.1212+0.0001 is not the same as the binary approximation of 27625.1213.

The exact decimal values of the internal binary approximations are shown below.

27625.1213

27625.12129999999888241291046142578125

27625.1212+0.0001

27625.12130000000252039171755313873291015625

27625.1212999999

27625.12129999990065698511898517608642578125

[#4 · Accepted solution]

@atpthomas 

This is a formatting defect in Excel.

It is not a limitation of the internal binary representation (64-bit binary floating-point).  Note that VBA has no problem displaying 27625.1213.

It happens to relatively few numbers whose internal binary approximation fits a particular pattern.  The details are probably TMI.  LMK if you are interested.  But honestly, it does little good to know.  Pragmatically, we cannot predict which numbers will be affected by the formatting defect.

IMHO, the best work-around is to enter VALUE("27625.1213").

-----

If left alone, the formatting defect can have surprising results.  For example, if we enter the following formula:

=IF(27625.1213=VALUE("27625.1213"),"same","different")

initially the result is "same" even though the number of the left appears to be 27625.1212999999.

But if we edit the formula as follows:

=IF(27625.1212999999=VALUE("27625.1213"),"the same","different")

it now returns "different".

The reason is:  in the initial formula, Excel uses the binary approximation of 27625.1213, even though the constant appears to be 27625.1212999999.

But when we edit the formula, Excel enters it exactly as it appears.  So, the binary approximation of 27625.1212999999 is used.

Thus, we might get away with ignoring the appearance of 27625.1213 initially.  But effectually, it will cause surprising results "inexplicably".

[#5]

@Joe User

Thank you - that helps!

Do you know if there an official MS document mentioning this as a binary defect with Excel?

[#6]

@atpthomas  wrote: ``[...] binary defect with Excel?``

Again, it is not a "binary" defect, insofar as it is not directly related to the 64-bit binary floating-point representation and arithmetic.

Instead, it is a formatting defect that is specific to Excel.  I don't believe it is even duplicated in any work-alike apps.  For example, the defect does not appear in Google Sheets.

-----

The formatting defect was originally reported (sort of) in KB161234, which was last updated in 2005.

But I started toying with this formatting defect using Excel 2003.

And the article says it applies to as early as Excel 5.0 (1993), if we can believe MSFT.  (We cannot.)

Unfortunately, MSFT has undergone extreme "aesthetic cleansing" in recent years, deleting any useful information from KB archives and product documentation.  (sigh)

So, I don't know of any microsoft.com website that documents the defect.

But we can still find this particular KB at https://www.betaarchive.com/wiki/index.php/Microsoft_KB_Archive/161234 .

The KB was never very good, in the first place.  It documents the formatting defect only for 0.848 when the integer part is from 32678 to 65535.  (Considering the unsigned value of the number).

But over the years, I have documented many other decimal fractions that are not related to 0.848 (*) -- a fact that you stumbled onto yourself.  And I demonstrated that the integer part can be any integer from zero to 65535, depending on the decimal fraction.

-----

(*) 0.848 is just one of a family of 3-digit decimal fractions of the form 0.098+0.125*x, for x = 0 to 7.  So, 0.098, 0.223, 0.348, 0.473, 0.598, 0.732, 0.848 and 0.973 with integers from 32768 to 65535.

BTW, 0.1213 is part of a family of 4-digit decimal fractions of the form 0.0588+0.0625*x, for x = 0 to 15.  So, 0.0588, 0.1213, 0.1838, 0.2463, 0.3088, 0.3713, 0.4338, 0.4963, 0.5588, 0.6213, 0.6838, 0.7463, 0.8088, 0.8713, 0.9338 and 0.9963 with integers from 16384 to 65535.