SOLVED

# Excel Math

Copper Contributor

# Excel Math

I am not sure why this math is off.  The formulas are column * 83.50 to get column 2 (time units)  Then Column 6 multiplies column 2 * 83.50 + Column 3 (20).  .87*83.5+20 = 92.645 not 92.37.  What am I missing?  TY

4 Replies
best response confirmed by Ron_Hockman1525 (Copper Contributor)
Solution

# Re: Excel Math

Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers.

Excel therefore stores 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.

This could be why you are seeing a difference between your calculator and Excel.

Recommend to use the ROUND function to get the same result.

NikolinoDE

I know I don't know anything (Socrates)

Thank you

# Re: Excel Math

@NikolinoDE  wrote:  ``Excel therefore stores 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.``

That describes only how Excel interprets numbers entered as text (manually type; copy-and-paste from text sources; import from a text file) and how Excel displays numbers.

But as I explain in a response in the cited thread, that is not true of calculated values.  And that is not specificied in the IEEE 754 (or any) standard.

Excel does not store only 15 decimal significant digits, despite the many online articles that say otherwise, including MSFT articles.  They are wrong!

In fact, Excel does not store decimal values, at all.  Instead, it stores binary values in memory.

It has been 5 years since I wrote that explanation, and I think I do a better job of it now.  LMK if you want more details.

But to demonstrate the point, consider the following example.

Enter 1234.567 into A1.  Enter =A1+0.000000000000455 or =A1+4.55E-13 into A2.  Format both as Number with 15 decimal places.  Note that the latter requires 19 decimal digits of precision.

Both display 1234.567000000000000.  But:

1. MATCH(A1,A2,0) returns #N/A, indicating that they are not the same binary value.

2. A1-A2=0 returns FALSE for the same reason. [1]

3. SUM(A1,-A2) returns about 4.55E-13, not zero. [2]

That demonstrates that Excel stores a binary value in A2 that must be represented by more than 15 significant digits.

In fact, the exact decimal representation of the binary value in A2 is 1234.56700000000046202330850064754486083984375 . [3]

-----

Possible TMI....

[1] But A1=A2 returns TRUE because of a trick that Excel plays to try to hide such infinitesimal differences.

[2] But =A1-A2 (only in formula form) returns exactly zero for the same reason.

[3] The exact decimal representation of A2 is 1234.567000000000462... instead of 1234.567000000000455... because A1 is not 1234.567000000000000..., in the first place.  Instead, the exact decimal representation of A1 is 1234.5670000000000072759576141834259033203125 .

# Re: Excel Math

1 best response

Accepted Solutions
best response confirmed by Ron_Hockman1525 (Copper Contributor)
Solution

# Re: Excel Math

Excel follows the IEEE 754 specification on how to store and calculate floating-point numbers.

Excel therefore stores 15 significant digits in a number, and changes digits after the fifteenth place to zeroes.

This could be why you are seeing a difference between your calculator and Excel.

Recommend to use the ROUND function to get the same result.