Here's a fun one. Incentive formula in tiered structure.

I'm working on this and as simple as it is, I'm having a major brain fart tonight and forgetting all my education on this from school.
Basically what I am wanting to do, is be able to put in last years sales under the LY column (example attached) and have the incentive structure auto calculate what the potential bonus would be if a 5% increase is met, 10%, 15%, 20%, 25%, and 30%. The way this works is noted above PLUS 1/4% of total sales. (.25% of total sales, not 25%).
So between 25k and 50k with a 10% increase would result in .25% of total sales plus $150.
Anybody who could get me on my way, it would be greatly appreciated!
I usually love doing this stuff but for some reason, I'm drawing blanks tonight. This old age stuff is for the birds!

Thanks for responding. Heres the best way I could think to describe what it is I'm looking for. I just am having major trouble writing the formula! Hopefully, someone can help.
Thanks again!

Originally Posted by FDibbins

if you modify your 1st table, by putting in 0 in B1, 25000 ib c1 etc across, then you can use this, copied down and across...
=IF($C23="","",$C23*0.0025+INDEX($B$1:$G$8,MATCH(D$11,$B$1:$B$8,0),MATCH($C23,$B$1:$G$1,1)+1))

I dont quite agree with the answers you have, so please check both your answers and mine?

You forgot to multiply $C23 by the 5%, 10%, 15%, 20%, or 25% (D11 thru H11).

I used: $C23*(1+D$11)*0.25%

Originally Posted by FDibbins

That was not how I read the OP's request?

Then take 1/4% of sales (0.25% of 42,000)
Equals 105 (Bonus payout for 1/4% of total sales)
Then add growth bonus based on chart above
Falls in the greater than 25k but less than 50k tier of 5 %
Add $75 to the 105 bonus for total bonus equaling 180 and so on for each growth %

there is nothing there about applying the % to the base value?

You left off the part...

40,000
plus 5 % increase =
42,000

The value in C23 is 40,000

BTW, your posted formula prompted me to reevaluate using Match(_,_,-1).

I revised the array and my formula to use [+]1....

You are honestly a genius! I cannot thank you enough. I really mean that. You have made my night!

I like to think of myself as quite handy using excel however im stumped on this one. Im trying to figure out a pay scale to use and I like the idea of having a tiered bonus however I cant figure out how to fill in the bonus line highlighted in blue. I also highlighted in blue the table to correspond with the tiers. Can someone help me find a formula I can use to make this work??

Attached is excel sheet I have created

You should start your own new topic...