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]

but i want to see inbetween if there is for example 40, then it would be 50 times 4 = 200,

Could you clarify this? why times 4?

you can use this formula to find bonus, but it will give you 50 as a result.

=SUMPRODUCT(--(A4:A8<=B1),--(B4:B8>B1),D4:D8)

 

[Jeffreyxx01]

For example the number 100 can change by the person using the worksheet,

if I write 40, then it would reach the first range of 36-45 so 36-40 = 4 then this should be times by 50 for each 4*50=200,

and so on,

 

[skorpionkz]

Try this formula then

=SUMPRODUCT(--(A4:A8<=B1),--(B4:B8>B1),D4:D8*(B1-(A4:A8)))

 

[Jeffreyxx01]

The formula does not work, I only get 0

 

[skorpionkz]

did you set range correctly?

=SUMPRODUCT(--(A4:A8<=B1),--(B4:B8>B1),D4:D8*(B1-(A4:A8)))
Where:
A4:A8 - min
B4:B8 - max
D4:D8 - bonus
B1 - your Variable number

does not include headers.

 

[Jeffreyxx01]

Yes I did and it does not give the right answer,

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
and so on

also using the number in the cell where anyone can change the number to their results.

 

[skorpionkz]

sorry I didn't spot "formula here" column... so do you want this formula to be in each line?

EDIT: Can't paste correct formula:/ keep changing to something different from what I am pasting

 

[skorpionkz]

sorry something wrong, Whenever I am pasting formula it keep changing after I press save button...

 

[Jeffreyxx01]

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
and so on

also using the number in the cell where anyone can change the number to their results.

Also assuming I reach 40 and I don't reach the highest 45, I should receive the bonus of 50*4 = 200 but if I reach 45 then I reach the full payout of the range,

Hope it is clear