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

[TABLE="width: 709"]
<colgroup><col span="5"><col><col></colgroup><tbody>[TR]
[TD]Number[/TD]
[TD="align: right"]100[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Min[/TD]
[TD]Max[/TD]
[TD]Tier Range[/TD]
[TD]Bonus[/TD]
[TD]Payout[/TD]
[TD]Formula here[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]0[/TD]
[TD]35[/TD]
[TD][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]36[/TD]
[TD]45[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]450[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]45[/TD]
[TD]54[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]900[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]54[/TD]
[TD]63[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1800[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]63[/TD]
[TD]100[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]11100[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[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
Number
Min	Max	Tier Range	Bonus	Payout	Formula here

<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>
[TD="align: center"]1[/TD]

[TD="align: right"]46[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]35[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]450[/TD]
[TD="align: right"]450[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]900[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]54[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]200[/TD]
[TD="align: right"]1800[/TD]
[TD="align: right"]0[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]63[/TD]
[TD="align: right"]100[/TD]
[TD="align: right"]37[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]11100[/TD]
[TD="align: right"]0[/TD]

</tbody>

​

[TABLE="width: 85%"]
<tbody>[TR]
[TD][TABLE="width: 100%"]
<thead>[TR="bgcolor: &lt"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: &lt"]C4[/TH]
[TD="align: left"]=B4-A4[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: &lt"]F4[/TH]
[TD="align: left"]=IF(AND($B$1>=A4,$B$1<B4),($B$1-A4)*D4,IF($B$1>=B4,E4,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]