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]

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