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?

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?

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?

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" <[email protected]> wrote in message
news:[email protected]...
> 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?

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?

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

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?