Please Help, This should be way easier than I'm making it

[tommygunnz]

I'm trying to make a bonus structure for commission.

0-5=$0
6-10=$50 per unit
11+=$150 per unit

so far I have =(M29>=6)*(M29*50)

how do I add another function so m29>=11 * m29*150??

 

[oldbrewer]

0-5=$0											mytable
6-10=$50 per unit
11+=$150 per unit									0	0
										6	50
										11	150
					sales	bonus
					2	0	#####
					7	50
					13	150
					0	0
					4	0
					6	50
					8	50
					9	50
					12	150
					14	150
					####
					=VLOOKUP(F11,mytable,2)

<colgroup><col span="14"></colgroup><tbody>
</tbody>

 

[steve the fish]

Possible way as long as you dont have too many categories:

=M29*LOOKUP(M29,{0,6,11},{0,50,150})

 

[BGY23]

Hi There

This will give you what you've asked for =IF(M29<6,0,IF(M29<11,M29*50,M29*150))

I suspect that isnt what you want, though I could be wrong. I suspect what you want is first 5 units are nil bonus, 6 to 10 units is bonus on units 6,7,8,9 and 10 of $50 each bonus on 11 and greater is $50 on units 6 to 10 and then 150 units on 11+

so 14 units is 5 x 0 +5 x 50 and 4 x150 ?

Which one do you want?

 

[BGY23]

This will give you increments. So first 5 units 0, up to next 5= units x 50 and remainder units > 10 * 150

=IF(M29<6,0,IF(AND(M29>5,M29<11),(M29-5)*50,IF(M29>10,5*50+(M29-10)*150)))