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.

 

[Andrew Fergus]

Hi

I'm not understanding the issue here (but I do understand the problem) - what I mean is if you round then add or add then round then you run the risk of not quite getting the same values each time.

How about working to one more decimal point than you desire in the workings, but round the final result to the number of decimal points you want?

Andrew

 

[highndry]

I'm not following you, I'm working with percentages that gives infinite decimals so to speak about. However, in the summary sheet I need to show it in 2 decimal places with the total agreeing (for auditors).

Rounding before the summary sheet is not an option, right now I have to manually adjust it to agree to the total. Wish there is smart rounding type function like Access in Excel.

 

[Andrew Fergus]

Ok, now I'm not understanding. If you are adding the values with a greater degree of accuracy and then rounding the result to two decimal places on the summary, what is this not agreeing to?

Andrew

 

[Andrew Fergus]

Ok, let's start again using your first post.

You have :
1.2479
2.1269

the sum of which is:
3.3748

this is rounded on the summary report to:
3.37

So far no problems. You show the 3.37 to the auditors per the summary, and the supporting values show a greater degree of accuracy. What is the issue / question?

Andrew

 

[highndry]

On the summary sheet I show it in 2 decimal places, that is:

1.25 and 2.13; however the sum of them visually is 3.38. So I need to force 2.13 down to 2.12 so that the total is 3.37. I hope I'm explaining it ok.

 

[Andrew Fergus]

Ok. How about showing 3 decimal places in the list and 2 decimal places for the total? I believe that changing the data is the incorrect solution.

By showing the list as 2 decimal places with an unadjusted total will end up with the issue you described (like I said in my first post - round and sum may give a different result to sum and round). There is nothing incorrect with what is going on and there should be nothing to concern the auditors. It is in the way the numbers are presented / rounded. If the auditors don't get that then you need to get new auditors!

Andrew

 

[highndry]

Its not the auditors but the audit rules. What you show must add up and tie, hence the problem.

 

[Andrew Fergus]

And therein is the issue : it does.

 

[Andrew Fergus]

Let's take an extreme example.

Data:
2.5
2.5

Adds to 5.0 but I want to show 0 decimal places, so this total is now shown as:
5

If I force my list to 0 dp this now gets shown as:
3
3

with a total of:
5

Yes this looks absurd but it doesn't mean we go and change the data. We change the presentation.

I believe changing the data to match the total is a worse audit offence than showing an extra decimal place.

So the presentation might be something like this:
Data:
2.5
2.5

Total : 5.0

Rounded Total : 5

Andrew