[Solved] Calc calculation error, rounding?

[Nobama]

Hi all:

I'm probably doing something wrong and something easy, but I could't figure out where I made the mistake.

From the attached file row 21 (unit price) is correct. But once it is multiplied with quantity (row 5) the total price (row 22 highlighted) are wrong. e.g. Cell E22 should be $22900.00 and not $22900.50. I don't seem to be able to correct these Row 22 cells.

Please help. Thank you.

[MrProgrammer]

I could't figure out where I made the mistake.

One mistake was posting in the Calc calculation error topic. Your question is unrelated to last December's topic about how the subtraction operator is handled in Calc. You should have created your own topic, not posted on that one. One issue, one thread Fortunately a moderator has created a new topic for you.

From the attached file row 21 (unit price) is correct.

Not really; E21 does not have the value 45.80! Cell D19 contains the value 39.65*.14 = 5.551, however you've specified two decimal places for that cell and it displays as 5.55. So E21 has the value 39.65+5.551+0.60 = 45.801, which is displayed as 45.80. Multiplying, we have 45.801*500 = 22900.50. Perhaps you want to use the ROUND function, changing D19 to =ROUND(D18*B19;2). Read about that in the Help, in this forum, or in the Wiki.

Read section 2. Controlling how data is displayed in Ten concepts that every Calc user should know.

[Nobama]

Excellent advise and it worked. Thank you.

I do apologize posting to the wrong thread. Since I believed, and still do, the problem was a "calculation error" and didn't realize it was a rounding error. I do believe the same entries and cell parameters could be entered into MS Excel, the calculated total price (cell E22) would display correctly (22900.00) not needing to enter the ROUND function on cell D19.

This kind of error can be dangerous especially when someone assumed once the parameters for CELL FORMAT: NUMBERS are set, the rest of the calculation would be correct.

Regardless, thank you anyway.

[Nobama]

Nope, I was wrong. I don't remember ever had a problem with MS Office Excel before. But I just tested Excel Starter (we don't have a full version of a MS Office in this office) on another computer, the calculated total price is also incorrect. I just have to make an effort to remember to perform ROUND function on a "PERCENT" cell format. I wonder why this is necessary. I am sure there is a good reason for that.

[UnklDonald418]

I wonder why this is necessary. I am sure there is a good reason for that.

Basic mathematics. When you multiply a number with two decimal places by another number with two decimal places the answer will have 4 decimal places. It just so happened that in your example the 4th digit turns out to be 0. Change the price of LED PB to $2.99 instead of $3.00 and the result of the multiplication will have 4 decimal places.
Those extra digits can have significance in financial calculations when large numbers are involved. Again using your example what if the quantity was 1,000,000 now that $0.001 translates into $1000.00 !

[Nobama]

Understood. I was assuming once a cell was defined to display 2 decimal places, we are asking the computer to perform the round function automatically. And any other calculations that require to use the number displayed on this cell will calculate using the number that was displayed. Lesson learned.

[acknak]

... once a cell was defined to display 2 decimal places, we are asking the computer to perform the round function automatically. And any other calculations that require to use the number displayed on this cell will calculate using the number that was displayed. ...

This is what the "Precision as shown" option does: the value stored in the cell is automatically rounded to the number of decimal places displayed by the number format.

The "Precision as shown" option is global—it applies to all cells in the document. I generally prefer to manage it myself and use ROUND() as needed in the formulas.