# Thread: problems with decimals-help urgently

1. ## problems with decimals-help urgently

Hello!

In attached workbook, in sheet named Analiza-dio1, there are several tables. Several numbers are rounded to four decimal places. They have to be rounded to two decimal places, but there is problem. If you round these numbers to two decimal places and then calculate it on desk calculator, the total sum in column Indirektni porezi-total is 100,01%. This mustn't be case.

Question is: how to round them to two decimal places with total sum(calculated on desk calculator) 100%? I mustn't delete contents of cells and type values myself.

Any help would be greatly appreciated.

2. Slaven,

There is a thing you MUST remember about Excel, that being formatting a number to 2 decimal only affects how the number is displayed NOT the value of the number used in calculations!

You MUST use the Round() function to affect how the number is used in calculations. I took your example and added a column which rounds the number to the right to 2 decimal places. Then Summed the numbers using the Sum() function. I then added the numbers in the new column using the Calculator and you'll notice that the numbers match.

Slaven_Rounding.JPG

Note: there is no way to accomplish your task if you can't make changes. You must be able to add the column to do the rounding. HTH

3. Rounding in Excel has long been a problem. If you have two numbers, 1.2455 and 1.2545, adding them gives 2.5. If you round them to 2 decimal places you get 1.25 and 1.26, obviously not the same. You need to keep the full precision of the numbers, no rounding, but display them as 2 decimal places using formatting.

cheers, Paul

4. Paul,

What version of Excel are you using?
Things seem to work fine in 2010
Round2.JPG

5. RetiredGeek, Paul T, I am talking about sheet Analiza-dio1(this is second sheet).

In this sheet, there are several cells with numbers rounded to 4 decimals.

Try to round it to two decimals and sum these numbers. The total sum is 100,01 percent.

Are you willing to edit file that I attached, keeping original formulas, and do the procedure you typed and post edited file again?

Thank you so much.

Reason why I ask you to do this for me is because I am in a big hurry.

6. 63,01 (formula:ROUND(63,0059;2)) Reference: A1
24,30 (formula:ROUND(24,2991;2)) Reference: A2
12,70 (formula:ROUND(12,6951;2)) Reference: A3
100,01 (formula: SUM(A1:A3)) Reference: A4

This mustn't be case!

7. Staven

As Paul and RG have indicated, if you format B5:D5 to show 2 decimal places, the total remains 100.00 in cell F5. Cells G5:I5 have the rounding function applied and result in 100.01 proving that formatting the cell to 2 decimals using formatting (not the Round function) will not change your result

rounded1.png

HTH,
Maud

8. On desk calculator sum of these 3 numbers is 100,01.

9. perhaps it is the method that your desk desk calculator rounds at each input such as columns G thru I in my example

10. Slaven,

The problem here is you can't expect to round numbers to a lesser precision (2 vs 4 decimal places) and then get the same result in a final answer. Intermediate rounding (e.g. each value that makes up a total) will almost always cause this type of error.
Round2.JPG
You can see in the attached graphic the amount of ROUNDING INCREASE for each number (they all rounded up) which is equal to the .01 error. This is not a problem with Excel but a misunderstanding of how rounding affects numbers. HTH

11. Now final question.

But, firstly, one note: only me have working tables. But, final data will be public. The data from tables will be copied to Word document

What you("you" means all people except me, that commented problem) suggest: to accept Excel's solution of rounding and put a note below table, or to change values myself after it's copying to Word?

Thanks to everyone.

12. It will be a lot easier to put a disclaimer in advising of possible rounding errors.

cheers, Paul

13. Slaven

..using your file as example, if you require the total% of the three values in columns B, C and D to be exactly 100.00%, then perhaps the best 'solution' is to force the result to be exactly 100.00%.
So, in column C use a formula, as per the example file attached.
For your info, I copied the original values for column C to column I to allow you to compare the results.

The only other option I can think of is to use Excel2003 Tools>Options..>Calculation>Precision as displayed

zeddy

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•