I'm using Excel to allocate an amount based on the percentage of a cell to the total. The formula is Round((A2/A200)*B1,2), copied down the column. All the amounts in column A are 2 decimal amounts, as is the amount in cell B1. The challenge comes when I then sum all the rounded amounts . . . I'm invariably off by a penny or 2. In the past I've just adjusted a cell to plus or minus that difference, but I'd like to find a way to be more precise. Is there a way to fix this rounding difference?
Rounding Errors
- Thread starter snocaps
- Start date
NARAYANK991
Excel Ninja
Hi ,
I have a different opinion ; the problem is your order of operations ; you are dividing by the value in A200 , which is probably a high value , and then multiplying by B1.
If you first multiply by B1 , and then divide by A200 , you may not get any difference.
=ROUND(A2*$B$1/$A$200,2)
may give the correct answer.
Narayan
I have a different opinion ; the problem is your order of operations ; you are dividing by the value in A200 , which is probably a high value , and then multiplying by B1.
If you first multiply by B1 , and then divide by A200 , you may not get any difference.
=ROUND(A2*$B$1/$A$200,2)
may give the correct answer.
Narayan
Thanks all, but those two ideas don't solve the problem. I have to take the calculation & import it into another program, and if the sum doesn't match exactly to the allocated amount, the import won't work. Bobhc, increasing the decimal isn't an option because this is currency . . . there can be no more than 2 decimals. Narayank991, that does not change the result (unfortunately).
Any other ideas?
Any other ideas?
bobhc
Excel Ninja
snocaps
If I type 100.562 into a cell that is formatted as currency then it will show as 100.56 and any values in the third position i.e. the 2 will be lost and the total will be out. If I increase the decimal position by one the cell it will display 100.562 and the totals will be right as the third value is now taken into account. The decimal position is for currency!
If I type 100.562 into a cell that is formatted as currency then it will show as 100.56 and any values in the third position i.e. the 2 will be lost and the total will be out. If I increase the decimal position by one the cell it will display 100.562 and the totals will be right as the third value is now taken into account. The decimal position is for currency!
NARAYANK991
Excel Ninja
Hi ,
Since you are rounding off , there is absolutely no guarantee that the sum total will match ; all I can say is that if you are insistent that they should match , and that the difference is a penny or two , then use the calculation for all but the last entry ; for the last entry take the remaining amount , which may be more by a penny or two , but which probably may not matter.
@Bob : what you say is correct for numbers which are entered , but once you use the ROUND function , you are losing the 0.002 from the 100.562 , and ending up with 100.56
A hundred such losses can total up to a penny or two.
Narayan
Since you are rounding off , there is absolutely no guarantee that the sum total will match ; all I can say is that if you are insistent that they should match , and that the difference is a penny or two , then use the calculation for all but the last entry ; for the last entry take the remaining amount , which may be more by a penny or two , but which probably may not matter.
@Bob : what you say is correct for numbers which are entered , but once you use the ROUND function , you are losing the 0.002 from the 100.562 , and ending up with 100.56
A hundred such losses can total up to a penny or two.
Narayan
Bobhc - I'm trying to understand you, so forgive me if my response seems repetitive. Formatting of the cell is irrelevant, as the results of the calculation will be exported to a .csv file. I have attached a dropbox file for you to reference . . . .
https://www.dropbox.com/s/axvtskb7ruhzx2x/Upload%20to%20dropbox.xlsx
https://www.dropbox.com/s/axvtskb7ruhzx2x/Upload%20to%20dropbox.xlsx
NARAYANK991
Excel Ninja
Hi Bob ,
Regarding your question , I think only the OP can answer it. Let me download the file and get back to you.
The only solution I can think of is that in F295 , have the following formula , instead of the usual formula everywhere else in column F :
=$F$1-SUM(F2:F294)
This will ensure that the total of all the individual amounts in column F , equals the value in F1.
Narayan
Regarding your question , I think only the OP can answer it. Let me download the file and get back to you.
The only solution I can think of is that in F295 , have the following formula , instead of the usual formula everywhere else in column F :
=$F$1-SUM(F2:F294)
This will ensure that the total of all the individual amounts in column F , equals the value in F1.
Narayan
Bobhc, the software to which the file is to be uploaded requires 2 position decimal, so bringing the calculation out 3 digits won't solve the problem. I think narayank991 has the only possible solution, except that I'll have to put the calculation in the first row to allow for changes in row length.
bobhc
Excel Ninja
snocaps
My apologies for wasting your time I did not realise there was a restriction on the upload software.
I do remember a film with Shaun Connery and Katherine Zeta Jones where they broke into the Petronas Towers and stole a huge amount of money by Syphoning off the last decimal digit in millions of banking accounts.......but I am not trying to give ideas
My apologies for wasting your time I did not realise there was a restriction on the upload software.
I do remember a film with Shaun Connery and Katherine Zeta Jones where they broke into the Petronas Towers and stole a huge amount of money by Syphoning off the last decimal digit in millions of banking accounts.......but I am not trying to give ideas