Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
FORMULA HELP
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic Range with unused formula messing up x axis on dynamic graph | Charts and Charting in Excel | |||
2 Nesting questions | Excel Worksheet Functions | |||
Formula Problem - interrupted by #VALUE! in other cells!? | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |