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

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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

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" wrote:

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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" wrote:

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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

it works, you made my day

thanks & regards,

Aarif

"Chris Marlow" wrote:

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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" wrote:

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.

--
Chris Marlow
MCSD.NET, Microsoft Office XP Master


"aarif" wrote:

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