rounding total/ smart rounding

[highndry]

I'm sure this has been asked before and I did use the search but can't find anything. Now that we have excel 2010, is smart rounding now available?

What I mean by smart rounding is, for example:

1.2479
2.1269

3.3748 Sum

but if you round and sum then you get

1.25
2.13

3.37 Sum

I hope there is a way around to adjust 2.13 down to 2.12 so that the sum is 3.37.

Thanks in advance.

 

[JB_Scotland]

Format your data tables to numbers to 2 decimal places.

Replace your SUM formulas with =SUM(ROUND(A1:A100,2)) and CONTROL ENTER to get {=SUM(ROUND(E7:E8,2))}

This way your underlying data will be accurate to the limits of Excel but your sums will be the sums of the rounded data.

 

[Andrew Fergus]

That would certainly satisfy the audit requirement that the report appears to add correctly (and 2 values of 2.5 rounded to 0 dp would add to 6) but I believe that method is not correct.

What is the goal / purpose of an audit? To have numbers that appear to be correct? Or to have numbers that are correct?

Andrew

 

[JB_Scotland]

Andrew, my solution is a correct one for what the auditor wants.

What is displayed is rounded data to two decimal places followed by the correct arithmetical sum of that rounded data.

If the auditor wants rounding of the data, then I see nothing wrong with that and it would be confusing an incorrect to show a sum that didn't add up!

 

[Joseph.Marro]

highndry,<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Regardless of accuracy issues, the "Smart" rounding you are looking for does exist in Excel, without the need for an array formula. Excel 2010 should have both the roundup & rounddown functions. The second augment of the function represents the number of decimal points. One key note, if the figures are being used in any other calculations this rounding effect will cascade and skew your results.
<o></o>
Thank you,<o></o>
<o></o>
Joseph Marro<o></o>
<o></o>

 

[abmati]

When you rinding off each value, the sum of those rounded values is ok, no need to round off the sum of old value.
A B C
1 1.2479 1.25 B1=ROUND(A1,2)
2 2.1269 2.13 B2=ROUND(A2,2)
3 3.3748 3.38 B3=SUM(B1:B2) is ok, do not =ROUND(A3,2)

I Hope the concept is clear to you.

 

[abmati]

When you rinding off each value, the sum of those rounded values is ok, no need to round off the sum of old value.
-------A-----------B---------C--------
1 1.2479 1.25 B1=ROUND(A1,2)
2 2.1269 2.13 B2=ROUND(A2,2)
3 3.3748 3.38 B3=SUM(B1:B2) is ok, do not =ROUND(A3,2)

I Hope the concept is clear to you.

 

[Andrew Fergus]

Andrew, my solution is a correct one for what the auditor wants.

What is displayed is rounded data to two decimal places followed by the correct arithmetical sum of that rounded data.

If the auditor wants rounding of the data, then I see nothing wrong with that and it would be confusing an incorrect to show a sum that didn't add up!

I respectfully disagree (but not with the 2nd sentence).

A mathematical function (such as a sum) cannot have a greater degree of accuracy than the data being worked with. Showing the total to the same decimal places as the supporting data is the nub of the problem. I can't seem to state that enough. This is an issue with display formats, not the numbers.

I have nothing more to say on this because I'm repeating myself.

 

[JB_Scotland]

Andrew I entirely see your point, but in this case the format of the displayed data is obviously more important to the auditor than the mathematical accuracy of the sum total, however illogical that may seem.

Hence ROUND then SUM is the solution not SUM then ROUND.

 

[highndry]

This formula {=SUM(ROUND(E7:E8,2))} forces the total but doesn't adjust the individual component.

 

[highndry]

...............................Sales ........................Sales in thousands
Region 1..... 123,456,789..... 123,457
Region 2..... 985,463,214..... 985,463
Region 3..... 658,914,236..... 658,914
Region 4..... 12,687,412........ 12,687
Region 5..... 47,852,369..... 47,852

Total ..... 1,828,374,020 ..... 1,828,374

Ok, in the above example, I would adjust Region 4 sales to 12,688 so that the total ties to 1,828,374. Hope this helps.