FORMULA HELP

[aarif]

Hi to all good people,

I am looking for formula help that can save my life,
My question is

Target Achieved Percentage Payout

100cases >=30% Nil
30-40% 15Per case
40-50% 25Per case
50-60% 40Per case
<=60% 50Per cas
If a sales person Achieves >=30% he is not gentting any commission but
salary only. When he is doing 30%+ he is getting commission from very first
case ie. Target is 200 and Achieved cases are 70 means 35% he gets 15 per
cases Amount is 70*15 = 1050.

I hope it is enough to make u understand my problem and also hopeful will
get the answer from community's good people,I am thanking you all in advance

Aarif

 

[Chris Marlow]

Hi,

Use a VLOOKUP - where the final parameter (range look up) is TRUE & order
your percentages as follows;

30% 0
40% 15
50% 25
60% 40
100% 50

Regards,

Chris.

 

[aarif]

hi chris,

i am thankful for your answer but i dont get understand how to use the
formula, will u explain it to me ? my problem is i have to make a worksheet
in which i can calculate the commission of all sales person. please help.

Aarif

 

[Chris Marlow]

Hi,

Toy solution from what you have written, you will need to amend it;

Sheet1 cells a1:d4
Name Target Actual Amount
Bob 200 100

Sheet2 cells a1:b5
30% 0
40% 15
50% 25
60% 40
100% 50

Formula in sheet1 cell d2
=VLOOKUP(c2/b2,Sheet2!a1:b5,2,TRUE)*c2

Regards,

Chris.

 

[aarif]

it works, you made my day

thanks & regards,

Aarif