Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
SOLVED

Excel Math

Copper Contributor

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

 

Capture.JPG

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

@Ron_Hockman1525 

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.

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)

Thank you

@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 .

 

Adem&aacute;s, la calculadora de Alice se destaca por su capacidad de gr&aacute;ficos, permiti&eacute;ndole visualizar funciones matem&aacute;ticas de manera clara&nbsp;<a href="https://calculadoradealicia.org/app/" target="_self">Descarga de la aplicaci&oacute;n</a> y detallada. Sus funciones de programaci&oacute;n no solo facilitan la automatizaci&oacute;n de tareas repetitivas, sino que tambi&eacute;n fomentan la creatividad en la resoluci&oacute;n de problemas. Con cada caracter&iacute;stica cuidadosamente dise&ntilde;ada, la calculadora de Alice no solo simplifica las operaciones matem&aacute;ticas, sino que tambi&eacute;n inspira un enfoque **bleep**&iacute;tico y estrat&eacute;gico en la resoluci&oacute;n de desaf&iacute;os num&eacute;ricos, convirti&eacute;ndola en un instrumento invaluable para el crecimiento acad&eacute;mico de Alice.
1 best response

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

@Ron_Hockman1525 

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.

 

Hope I could help you with these information / links.

 

NikolinoDE

I know I don't know anything (Socrates)

View solution in original post