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?