Another Excel Formula Help - Rounding Issue

[arvarr]

Hi
Hoping someone can help me with a formula dilemma. I am facing issues with rounding.
Sometimes its off by a cent or so.

I have tried rounding it to 2 decimal places but still facing same issue.

Just a

Within Excel, it has more than 2 decimal places and I need to upload the excel file into the system whereby it truncates to the nearest 2 decimal places and this sometimes causes it to vary by a cent or so.

Hope I am making sense.

Thanks,
Arv

 

[mintjulep]

I guess your are formating, Not rounding.

Rounding is different from truncating.

Which do you need?

 

[arvarr]

Would not be formatting.
I have tried rounding and didn't work.
Thanks,
Arv

 

[SkipVought]

Hi,

First off you just posted a picture. WORTHLESS!

Then you referred to a formula, but there is no formula to be found. WORTHLESS!

How about some real COPY 'n' PASTE data and formula(e), where required?

I got almost all the time in the world, sitting here in my recliner and sipping my morning coffee, waiting for my SS check. But all the other poor blokes have to hustle for a dollar.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[Andrzejek]

Skip is right.
Asking the question the ‘right way’ is half of a battle. Help others to help you. I know that YOU know what the issue is. When anybody ELSE reads your question, they (we) have no clue about your problem.

So, state the problem again, use proper descriptions of what you do (‘your are formatting, Not rounding’) and attach your Excel file with appropriate data, formulas, etc. Help us to help you.



---- Andy

There is a great need for a sarcasm font.

 

[SkipVought]

Well here I am, in locus purtibo. No nuance to nuisance.

Took your pic and divined how your pic was generated in sheet.
[pre]
Row 1: Source values
Column A: Source values
Split values: =B$1*$A6
Split sum: =SUM(A6:A10)
[/pre]

HOWEVER, did you intend the SUM of your source values in column A to be [highlight #FCE94F]100.25%[/highlight]?
[pre]
Amount 1000 2000.52 2000.55 1731.23 327.65 100
Pct Split Split Split Split Split Split
15.25% 152.5000 305.0793 305.0839 264.0126 49.9666 15.2500
12.50% 125.0000 250.0650 250.0688 216.4038 40.9563 12.5000
12.50% 125.0000 250.0650 250.0688 216.4038 40.9563 12.5000
17.30% 173.0000 346.0900 346.0952 299.5028 56.6835 17.3000
42.70% 427.0000 854.2220 854.2349 739.2352 139.9066 42.7000
[highlight #FCE94F]100.25%[/highlight] 1002.5000 2005.5213 2005.5514 1735.5581 328.4691 100.2500
[/pre]
???


Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[arvarr]

HI Skip and Andy
Apologies for not putting the correct information for your to help me.

I have attached file for you to review.

Thanks,
Arv

 

[combo]

It is a problem to solve by you rather than excel formula. Suppose you need to divide 1.00 into three equal parts. After rounding you get 0.33 each. But 3 x 0.33 = 0.99 <> 1.00.
Sum of displayed values in col. B from your example is different from displayed sum, the reason is the same, in general: sum of rounded values is different from roundrd sum of exact values.

combo

 

[Deniall]

Combo has nailed the problem.[&nbsp;] I first came across this problem many years ago when I was writing some financial software to allocate interest payments between accounts.[&nbsp;] As a naïve non-accountant I calculated each interest payment as a floating point number, then rounded it to the nearest cent.[&nbsp;] First run of the shiny new software and the accountants had apoplexy:[&nbsp;] the sum of the individual interest payments did not equal the total amount of interest that had been set aside to be distributed.[&nbsp;] It might have been out by only a few cents, but it might as well have been out by the national budget of a medium sized nation.

The way I dealt with this, and the way I have continued to deal with such "round-off" problems when I have come across them in other contexts ever since, is:
»[&nbsp;] Do the allocations.
»[&nbsp;] (Check whether there is a discrepancy)*
»[&nbsp;] Scan the list of N items that should add to whatever-the-total-is but don't, finding the item with the largest absolute allocation.[&nbsp;] Call this item number I.
»[&nbsp;] Calculate the sum of the N-1 allocations that are not item I.
»[&nbsp;] Recalculate item I's allocation by subtraction.

* This parenthesized step is a waste of time, so don't bother with it.[&nbsp;] There will almost always be a discrepancy.[&nbsp;] As a result of my embarrassing introduction to this phenomenon I looked into the underlying statistics.[&nbsp;] If you have N accounts, and you are rounding the numbers to the nearest cent, you have an average rounding error of 0.25 cents per account in magnitude.[&nbsp;] Some of these will be positive rounding errors and others will be negative.[&nbsp;] You will tend to get equal numbers of positive ones and negative ones, but you will seldom get exactly equal numbers of each.[&nbsp;] It can be quite easily shown that the expected value of the magnitude of sum of the rounding errors is
Sqrt(2*N/pi) * (0.25)
(where the 0.25 is the size of the individual discrepancies).

EDIT: Worded the above five steps a bit more clearly, about five minutes after the post was originally lodged.]

 

[arvarr]

Hi combo
Thanks for your information.
Understand where you are coming from.
My challenge is because i may have many rows of data and its quite challenging to identify impacted lines.

What would be the best way to overcome this matter?

Thanks,
Arv

 

[combo]

In case of most simulations I work with, I simply use exact formulas and use display rounding only. So in my example I have 1/3 displayed as 0.33 summing up to 1 displayed as 1.00.
Occasionally when I need rounded values (esp. financial calculations), I perform exact calculations, round input data and sum up rounded. The same example again, displayed 0.33 and the sume exact numbers, sum up to 0.99.

combo

 

[SkipVought]

If you're just referring to your latest example of rounding to 2 decimal places for the upload, I'd round before sending...
[tt]
=ROUND(B4,2)
[/tt]




Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein