Multiplying odd numbers by percentages to total to original number

[leepea42]

I have odd numbers in a spreadsheet that I need to multipy by percentages and then have those solved numbers all add up to the orginal odd number. Example: 3,541.67 x 50%, x 25%. and x 25% gives me the answers of 1,770.84, 885.42, and 885.42. Those three answers add up to 3,541.68, not 3,541.67. I have tried the ROUND function but that doesn't help. I know there must be an easy answer but don't know how to search for it.

[dilipandey]

HI leepea42,

Its fine in the attached workbook..
multiplying % and getting to original.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[leepea42]

Thank you but I need the resulting figures of the percentages to be in whole numbers, rounded to 2 decimals. In this case, the 1770.835 needs to be 1770.83, not 1770.84, which is what the ROUND function provides. I do not what to have to change the ROUND function to ROUNDDOWN manually. I just want Excel to do it automatically. There must be a setting somewhere in Excel that works that way.

[dilipandey]

There must be a setting somewhere in Excel that works that way.

There is a formula solution to this.. use below formula:-

=MID(B2,1,SEARCH(".",B2)+2)*1

see attached:- multiplying % and getting to original.xlsx

Regards,
DILIPandey
<click on below * if this helps>

[leepea42]

Okay, that works but now all the numbers add up to 3,541.65.
I used to work on spreadsheets for another business and their spreadsheets didn't have this problem. I guess I need to see if I can find out why from them. But thank you for your help.

[dilipandey]

Hi leepea42,

As per mathematics, it should not be happening on other's spreadsheets, would appreciate if you could attach a small sample of theirs .. just for my knowledge. thanks.

Regards,
DILIPandey
<click on below * if this helps>

[FDibbins]

ok, hows this for a sneaky/cheating approach? If you want all 3 numbers to exactly match that total, then just find the % for the 1st 2, using rounddown() to 2 places. then, for the 3rd value make it = the total less the sum of the 1st 2 values

[dredwolf]

Also, you have to remember, displayed value is rounded to closest level of display formatting, usually a .*5 or higher gets DISPLAYED as next highest value(ie- 0.0005, rounded to 3 decimals will display as 0.001, however...the value is still 0.0005, so mathematically, they are the same, just visually they change...unless you force a round off error using the round(several) functions...

[vlady]

as Fd and dredwolf stated...

another way to cheat is to compute in a hidden column then in the desired "visual" cells use trunc

multiplying % and getting to original.xlsx