Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
I am trying to mulitply an original number (say 50) by a series of
percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
I usually choose the largest portion to take up the slack. so multiply each
of the percentages by the original except one and make that value equal to the original minus the sum of the other results 50 * 21% = x 50 * 29% = y 50 * 19% = z w = 50 - sum(x, y, z) <== approximates 31% -- Regards, Tom Ogilvy "Stephen" wrote: I am trying to mulitply an original number (say 50) by a series of percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
Thank you, but, in my business, we cannot use the largest one to take up the
slack. I was hoping Excel was "smart" enough to figure out which one was closest to the rounding up or down to make it equal the original total. "Tom Ogilvy" wrote: I usually choose the largest portion to take up the slack. so multiply each of the percentages by the original except one and make that value equal to the original minus the sum of the other results 50 * 21% = x 50 * 29% = y 50 * 19% = z w = 50 - sum(x, y, z) <== approximates 31% -- Regards, Tom Ogilvy "Stephen" wrote: I am trying to mulitply an original number (say 50) by a series of percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
Stephen,
Excel already is being smart; it's the limitations of binary maths that it can't exceed. So it's up to you to decide on a suitable "policy" to employ in such situations. ROUND, TRUNC, +/- 0.00001, Tom's method or some such way to decide on the values you will use, as opposed to what Windows/Excel calculates. Failing that, use a maths library that does not depend on binary representations, but that hardly seems worth it. NickHK "Stephen" wrote in message ... Thank you, but, in my business, we cannot use the largest one to take up the slack. I was hoping Excel was "smart" enough to figure out which one was closest to the rounding up or down to make it equal the original total. "Tom Ogilvy" wrote: I usually choose the largest portion to take up the slack. so multiply each of the percentages by the original except one and make that value equal to the original minus the sum of the other results 50 * 21% = x 50 * 29% = y 50 * 19% = z w = 50 - sum(x, y, z) <== approximates 31% -- Regards, Tom Ogilvy "Stephen" wrote: I am trying to mulitply an original number (say 50) by a series of percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
For clarity, are you concerned that =SUM(50*p1, 50*p2, 50*p3) returns
something like 50.0000000000001 or something like 51? For the first case, you are seeing the impact of finite precision which impacts all software except symbolic math packages like Maple and Mathematica. Depending on the particular percentages that you are using, you might reduce the impact by using BCD (binary coded decimal, imployed by a small minority of software packages) calculations instead of binary (NickHK' s suggestion), but in general it is not possible to eliminate it altogether, except symbolically. In the second case, you are seeing the impact of your own rounding of the multiplication results. No software (not even symbolic packages) can read your mind to know which rounded value you want to arbitrarily distort to restore the unrounded sum. Excel's pie chart will use the strategy that Tom suggested, but you didn't seem happy with that. Note that there may not always be one closest to rounding the other way, as when p1=p2=p3=1/3. Nor is it neccessarily the case that the number closest to rounding the other way would have the smallest percent distortion if forced to round the other way. TANFL Jerry "Stephen" wrote: I am trying to mulitply an original number (say 50) by a series of percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
you can try using integer math tactic. Multply by 20 instead of 0.20 and divide the answer by 100 sometimes that works for me ... the problem origonates in the fact that 1/10 can't be expressed as 1/2^x -- markbpdx ------------------------------------------------------------------------ markbpdx's Profile: http://www.excelforum.com/member.php...o&userid=37610 View this thread: http://www.excelforum.com/showthread...hreadid=571843 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Making % add up to the original whole number?
We have to apportion a fixed amount of cash to employees according to a
formula based on length of service and other factors. Since we must round to the penny, we often would be off by a penny or so. We solved this by fudging the original amount being distributed until the sum of the rounded amounts equals the actual amount being distributed. Using your example, we have a cell (we name ours "FudgeFactor") that we might change to 49.994 or 50.011 or whatever works to make those rounded totals add up to 50. (We use conditional formatting to tell us when we're on the money.) If our original rounded total was, say, 50.01, this might actually result in a couple of employees getting a penny less and one getting a penny more. But nobody gains or loses more than a penny, and the amounts add up to what is being distributed. I don't know if this would work for you, but everyone here is fine with it. Oh, and we could use Goal Seek to change FudgeFactor, but my boss likes to play with the number himself, so it's fine by me. I like to keep the boss happy. Stephen wrote: Thank you, but, in my business, we cannot use the largest one to take up the slack. I was hoping Excel was "smart" enough to figure out which one was closest to the rounding up or down to make it equal the original total. "Tom Ogilvy" wrote: I usually choose the largest portion to take up the slack. so multiply each of the percentages by the original except one and make that value equal to the original minus the sum of the other results 50 * 21% = x 50 * 29% = y 50 * 19% = z w = 50 - sum(x, y, z) <== approximates 31% -- Regards, Tom Ogilvy "Stephen" wrote: I am trying to mulitply an original number (say 50) by a series of percentages adding up to 100%. When Excel adds them together, due to rounding, they don't add up to the original number. Do you know how I can get it to round correctly to get the original number? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiplying by variables based off original number | Excel Worksheet Functions | |||
Making a new XL file form the Original XL data - Misho | Excel Discussion (Misc queries) | |||
making copied cells change with change in original cell | Excel Worksheet Functions | |||
Making different sheet from the original sheets data | Excel Programming | |||
making a cell fixed number to a input number | Excel Programming |