Calculating bonus with different variable

[Jeffreyxx01]

Hi guys,

I am trying to workout a bonus formula, but got stuck on,

I want to see in the formula column, if I change the number in red (100), the fomula would show for the range the bonus, as for example, 45 - 36 = 9 then max payout is 450, but i want to see inbetween if there is for example 40, then it would be 50 times 4 = 200,

Thanks for the help

Number	100
Min	Max	Tier Range	Bonus	Payout	Formula here
0	35		0	0
36	45	9	50	450
45	54	9	100	900
54	63	9	200	1800
63	100	37	300	11100

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

 

[skorpionkz]

... nope... website refuse to accept what I am pasting :/

 

[Jeffreyxx01]

I don't know if it is clear for you,
I will try to make two scenarios,

for example between 0-35, there is no bonus,
36 to 45 I should receive 9*50 = 450
45 to 54 I should receive 9*100 = 900
54 to 63 I should receive 9*200 = 1800
63 to 100 I should receive 37*300 = 11,100

now assuming I don't reach 100 but I reach 40
36 to 45 I should receive 4*50 = 200
45 to 54 = 0
54 to 63 = 0
63 to 100 =0


hope that helps

 

[Jeffreyxx01]

... nope... website refuse to accept what I am pasting :/

Thats unfortunate.

 

[skorpionkz]

Hope it will work today...

	A	B	C	D	E	F
1	Number	46
2
3	Min	Max	Tier Range	Bonus	Payout	Formula here
4	0	35	35	0	0	0
5	35	45	10	50	450	450
6	45	54	9	100	900	100
7	54	63	9	200	1800	0
8	63	100	37	300	11100	0

<colgroup><col style="width: 25pxpx" https:="" www.mrexcel.com="" forum="" usertag.php?do="list&action=hash&hash=DAE7F5"" target="_blank"></colgroup><colgroup><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

​

Cell Formula C4 =B4-A4 F4 =IF(AND($B$1>=A4,$B$1<B4),($B$1-A4)*D4,IF($B$1>=B4,E4,0)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>