Summing Percentages

[joeestrada]

I have a number that needs to be decreased based off a % improvement matrixlocated in my excel file. I'd like this number to be decreased by a user select denotation (ie "x") next to the corresponding improvement parameter.Is there a function that will sum the percent improvements if a user selects multiple improvements? For example, if the number is 100 and a user chooses 2 25% improvements, this result in 50, it would result in approx. 57.
Thanks!

 

[Auric__]

I have a number that needs to be decreased based off a % improvement
matrix located in my excel file. I'd like this number to be decreased
by a user select denotation (ie "x") next to the corresponding
improvement parameter. Is there a function that will sum the percent
improvements if a user selects multiple improvements? For example, if
the number is 100 and a user chooses 2 25% improvements, this result in
50, it would result in approx. 57.

Can you explain *EXACTLY* how this is supposed to work? Perhaps make a mockup
spreadsheet showing how you want it to look and post it somewhere on the www.

 

[joeu2004]

I have a number that needs to be decreased based off a
% improvement matrix located in my excel file. I'd like
this number to be decreased by a user select denotation
(ie "x") next to the corresponding improvement parameter.
Is there a function that will sum the percent improvements
if a user selects multiple improvements? For example, if
the number is 100 and a user chooses 2 25% improvements,
this result in 50, it would result in approx. 57.

Aha! I think I figured out what you are talking about. You want to
__compound__ percentages, not "sum" them. Note that 100*(1-25%)*(1-25%) is
56.25, which is "approx 56", not 57, by the way.

First, we need to iron some design requirements.

First you say a user might select percentage by putting "x" next to it.
Then you say the user might be the number 2 next to it to denote compounding
twice. I suggest that you stick with numbers: 1 instead of "x" to compound
once.

Suppose the percentages are in B1:B10, and the user enters his/her selection
(1, 2, etc) in A1:A10, and the base number (e.g. 100) is in C1

Then I think the following array-entered formula (press ctrl+shift+Enter
instead of just Enter) does what you require:

=C1*PRODUCT((1-B1:B10)^A1:A10)

Caveat: The PRODUCT function can be poorly behaved when the multipliers are
very large or very small. Alternatively, use the following normally-entered
formula (just press Enter as usual):

=C1*10^SUMPRODUCT(A1:A10*LOG(1-B1:B10))

 

[Martin Brown]

Aha! I think I figured out what you are talking about. You want to
__compound__ percentages, not "sum" them. Note that 100*(1-25%)*(1-25%)
is 56.25, which is "approx 56", not 57, by the way.

First, we need to iron some design requirements.

Unfortunately the concrete example of 25% is exceptionally ambiguous.

Normally you would consider improving from a starting point of 100
upwards so that he is asking for is the extent of the improvement.

That would be ((1+25%)*(1+25%) -1) = 56.25%

If he specifies the right answer for two 10% compound improvements we
stand a chance of decoding his intentions.

0.9^2 = 0.81
1.1^2-1 = 0.21

=C1*PRODUCT((1-B1:B10)^A1:A10)

Caveat: The PRODUCT function can be poorly behaved when the multipliers
are very large or very small. Alternatively, use the following
normally-entered formula (just press Enter as usual):

=C1*10^SUMPRODUCT(A1:A10*LOG(1-B1:B10))

The multipliers here should all be pretty close to 1 so it shouldn't
explode too horribly. One thing Western businesses tend to forget is
that 100 1% improvements (as might happen in Japan) gets you 2.7x!