On 12/10/2013 03:46, joeu2004 wrote:
wrote:
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.

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!

--
Regards,
Martin Brown