I have just found a problem in a very simple spreadsheet (explained below) for which I do not understand the reason.



A1: 7280
A2: 6640
B2: difference formula =A1-A2 [cell displays (640.00)]
C2: division formula =614.73/A2 [cell displays .09]
D2: multiplication formula =B2*C2 [cell displays 59.25]

I inserted these identical cells within an Open Office spreadsheet. The result was the same as above.

However, for the cell displaying 59.25, my wife showed a value of 57.60 on her calculator, which matched a manual effort.

I changed the D2 formula and instead of using cell C2 (a formula) I used a different cell wherein I manually entered the result of the C2 formula, .09. The result then matched the calculator result of 57.60

Can someone explain what is going on?

Data is very often displayed differently than it is stored... formatting can only change the appearance of data; formatting is not capable of changing values.

To see the true contents of a cell, select the cell and look at the Formula bar (wide white thing above the column letters). I suspect cell A2 is lying to you.

It appears you have the cell number format limited to two decimal places.

The actual cell values ofr your calculations is

C2: 0.0925798192771084
D2: 59.2510843373494

Just change your cell formats to see the rest of the numbers.

Cheers,

P.S. - I think Kimberly meant C2 vice A2.

I had earlier pasted a section of the Excel sheet into an Open Office spreadsheet to confirm the problem. When confirmed, I posted my question on their forum in addition, needing a resolution before this Friday.

Remembering my initial post problem was that the spreadsheet value returned from the multiplication was ~1.65 different than that returned by a calculator (59.25 vs. 57.60).

The answer you guys gave does not change that fact--the answer is still 59.25xxxxx. But the answer that the Oo person returned did provide a way to align the calculator vs. spreadsheet as quoted below:

"Use a calculator and multiply .09*640 and you get 57.6. The actual value in the cell is .0926 rounded to four decimals. .0926*640=59.264 according to my calculator not a spreadsheet. A spreadsheet always uses the exact value no matter how you format the cell for display purposes.
Go to Tools-->Options-->Openoffice.org Calc-->Calculate and check the box "Precision as Shown" and you will get the result I show above in your spreadsheet."

Since this spreadsheet will be given to a group of folks who use calculators far more than spreadsheets, the numbers need to match that of a calculator. I do not see an option within Excel that corresponds to the 'Precision as Shown' option of Oo? Is there one or a way of getting a similar result?

Excel Options > Advanced > Set precision as displayed

Thanks (again) Kimberly.

One other question. By default, how many decimal places does Excel use in calculations?

15 digits of precision... without regard to the position of the decimal point.

Excel, like most computer programs, uses a number storage standard named IEEE Double Precision Floating Points. Numbers stored in this format are limited to 15 digits of precision, beyond which the numbers are rounded.

Institute of Electrical and Electronic Engineers (IEEE) is an international organization that determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers can be stored in a binary computer.

There is, for example, no way to represent 0.1 in a finite number of digits in binary.

I would look at the many rounding functions, such as ROUND(), if I were you, instead of setting the 'precision as displayed'.

15 digits of precision... without regard to the position of the decimal point.

Excel, like most computer programs, uses a number storage standard named IEEE Double Precision Floating Points. Numbers stored in this format are limited to 15 digits of precision, beyond which the numbers are rounded.

Institute of Electrical and Electronic Engineers (IEEE) is an international organization that determines standards for computer software and hardware. The 754 specification is a very widely adopted specification that describes how floating-point numbers can be stored in a binary computer.

There is, for example, no way to represent 0.1 in a finite number of digits in binary.

I would look at the many rounding functions, such as ROUND(), if I were you, instead of setting the 'precision as displayed'.

I sort of misspoke slightly, I should have said that you can't represent 0.1 in binary floating point. Much like you can't represent 1/3 in our decimal number system.

A couple of clarifications Kimberly...

If the limit is 15 digits regardless of the decimal, then the spreadsheet couldn't calculate 100 trillion?

There's no difference between round(2) and Round as shown (assuming the as shown cells have been set to 2 decimals) correct? I will not be using either of the conventions--my wife will for a specific group of folks. This group does not generally use spreadsheets--they use a calculator. This effort is to avoid conflicts wherein her spreadsheet values do not align with those of an ordinary calculator.

I'm very interested in this discussion since I never realized this stuff before and I'm sure this new knowledge, once I understand it better, will help me in the future.

Have a look on the Web at double precision floating point numbers and also at precision as displayed and round. They are sure deep subjects.