Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 1
Default Summing Percentages

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.
Thanks!
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 538
Default Summing Percentages

joeestrada 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.


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.

--
Order the Iodine-113 to start. It's spun to perfection and served piping
hot. Eat it up quick because it turns to mush after about 7 seconds.
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 829
Default Summing Percentages

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.

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 requi

=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))

  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 230
Default Summing Percentages

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
summing, grouping percentages in pivot table. mje1975 Excel Discussion (Misc queries) 2 August 20th 08 03:06 PM
Average Percentages Excluding Zero Percentages MPHernandez Excel Programming 0 March 19th 08 07:54 PM
PivotTable and summing/not summing ~*Amanda*~[_2_] Excel Discussion (Misc queries) 1 March 14th 07 08:35 PM
percentages chedd via OfficeKB.com Excel Worksheet Functions 5 June 12th 06 12:25 PM


All times are GMT +1. The time now is 02:04 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"