Run-Up Analysis & Commission Trigger Calculation

[Vlad111]

hello again,

this really is a wonderful site, i do thank you for the assistance you provided. I have two questions, they are unrelated, the first is on run-up analysis, for those unfamilar, it is simply a run-up and a period during which the profit made followed by a period during which the profits made from that run-up are then lost before another run-up can start.

Example, in cell a1=3%, a2=4%, a3=-2%, a4=-10% a5=6%. Now, the run up for profit is from cell a1 to a2, a total of 7% = (a1+a2) then stops in cell a3 because a loss is made, i want to know how you calculate the sum of a1 to a5 and it will tell you what the maximum sum for positive percentages and the maximum sum for negative percentages in that one column. in this instance, the answer would be a maximum positive run up percentage of 7% and maximum negative run up percentage of -12%, but, how do you put that into a formula or combination of formulas to calculate the range a1 to a5 to give you these two outcomes?

Second question involved commission trigger, i set the benchmark of $30,000 in sales and a salesman returns $50,0000, he is over the benchmark, how would you go about calculating this, for example, in cell a1=$50,000 <-- sales made, a2= 2.5% <--- commission rate on sales over benchmark, a3= Yes <--- i use the IF function to state whether he is entitled to a commission or not a4=$5,000 <--- retainer salary if not over the benchmark

Now, since he is entitled to a commission, how do i get excel to multiply the commission rate by the sales he made if he is over that benchmark? if salesman makes $50,000 in sales in a month, his commission rate of 2.5% would be multiplied by $50,000 to give him the commission along with adding his retainer, if he is below the benchmark, there is no commission and the retainer is automatically restored and he is entitled to $5,000. How do i get excel to calculate this function automatically on the basis that he is over or under the benchmark? In a nutshell, excel would compute either he is entitled to $50,000* 2.5% = $1,250 + retainer $5,000, total is $6,250, if under benchmark, an automatic compute of $5,000. the only thing I would want to type in is his sales for the month, and I would want excel to do the rest, and compute whether he is entitled to commission, and compute, or, if he is not, and compute giving you those two results I calculated above.

thank you for taking the time to read it, look forward to hearing from.

[oldchippy]

Hi,

I'm not sure about your first question, you may need to post a question in the Progamming forum for that one if nobody gives you an answer here.

But for the second one, this can be in one formula with your $50,000 in A1 put this in B1

=IF(A1>30000,A1*A2,5000)