Results 1 to 13 of 13

20150228, 12:13 #1
 Join Date
 Aug 2005
 Location
 Novi Sad, Serbia and Montenegro (Yugoslavia)
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
problems with decimalshelp urgently
Hello!
In attached workbook, in sheet named Analizadio1, 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 porezitotal 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.

20150228, 12:31 #2
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,667
 Thanks
 390
 Thanked 1,511 Times in 1,372 Posts
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. HTHLast edited by RetiredGeek; 20150228 at 12:37.
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150228, 12:42 #3
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,522
 Thanks
 54
 Thanked 1,038 Times in 966 Posts
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

20150228, 12:49 #4
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,667
 Thanks
 390
 Thanked 1,511 Times in 1,372 Posts
Paul,
What version of Excel are you using?
Things seem to work fine in 2010
Round2.JPG
May the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150228, 13:05 #5
 Join Date
 Aug 2005
 Location
 Novi Sad, Serbia and Montenegro (Yugoslavia)
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
RetiredGeek, Paul T, I am talking about sheet Analizadio1(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.Last edited by slaven_savic; 20150228 at 13:17.

20150228, 13:46 #6
 Join Date
 Aug 2005
 Location
 Novi Sad, Serbia and Montenegro (Yugoslavia)
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
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!

20150228, 14:13 #7
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 692 Times in 628 Posts
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,
MaudLast edited by Maudibe; 20150228 at 14:16.

20150228, 14:33 #8
 Join Date
 Aug 2005
 Location
 Novi Sad, Serbia and Montenegro (Yugoslavia)
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
On desk calculator sum of these 3 numbers is 100,01.

20150228, 16:04 #9
 Join Date
 Aug 2010
 Location
 Pa, USA
 Posts
 2,754
 Thanks
 129
 Thanked 692 Times in 628 Posts
perhaps it is the method that your desk desk calculator rounds at each input such as columns G thru I in my example

20150228, 16:29 #10
 Join Date
 Mar 2004
 Location
 Manning, South Carolina
 Posts
 9,667
 Thanks
 390
 Thanked 1,511 Times in 1,372 Posts
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. HTHMay the Forces of good computing be with you!
RG
PowerShell & VBA Rule!
My Systems: Desktop Specs
Laptop Specs

20150228, 17:40 #11
 Join Date
 Aug 2005
 Location
 Novi Sad, Serbia and Montenegro (Yugoslavia)
 Posts
 52
 Thanks
 0
 Thanked 0 Times in 0 Posts
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.

20150301, 01:02 #12
 Join Date
 Dec 2009
 Location
 Earth
 Posts
 8,522
 Thanks
 54
 Thanked 1,038 Times in 966 Posts
It will be a lot easier to put a disclaimer in advising of possible rounding errors.
cheers, Paul

20150302, 11:49 #13
 Join Date
 Mar 2002
 Location
 Newcazzle, UK
 Posts
 3,130
 Thanks
 149
 Thanked 573 Times in 545 Posts
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