Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
summing, grouping percentages in pivot table. | Excel Discussion (Misc queries) | |||
Average Percentages Excluding Zero Percentages | Excel Programming | |||
PivotTable and summing/not summing | Excel Discussion (Misc queries) | |||
percentages | Excel Worksheet Functions |