Formula to calculate commission based on sliding scale

[Ecel Dumbo]

Hello,


Please refer attached file

http://speedy.sh/DBCFQ/Formual-to-calculation-Commission.xls


I would like to get a formula to calculate commission based on a sliding scale table shown in the file.


Any help will be greatly appreciated.


Regards,

Dumbo

 

[Faseeh]

Hi Dumbo,


Plz explain this: "plus additional 2% for every % over 105.99%." can you give an example??


Regards,

 

[Faseeh]

Hi Dumbo,


Breakup the lookup table into the following, it is present in cells A9:C18, and try this formula:


=IF(B2>1.2739,B3*1.25+((B2-1.0599)*100*0.02*B3),VLOOKUP(B2,$A$9:$C$17,3)*B3)


..and drag to left.


0% 90% 0%

90% 92% 50%

92% 94% 60%

94% 96% 70%

96% 98% 80%

98% 100% 90%

100% 101% 100%

101% 104% 115%

104% 106% 125%

107% 200% 2%


Regards,

 

[Ecel Dumbo]

Thanks a lot Faseeh

 

[Faseeh]

You are welcome.