Excel Magic Trick 453: VLOOKUP for Commission Brackets Calculation (LOOKUP function also)

[MaJuarez2007]

Yeah VLookup makes it prety easy. I was using a bunch of nested IF functions but that was pretty messy whenever I needed to make changes.

Also, if you are calculating commissions in Excel, I have found a great tool called "OneClick Commissions" which allows sales managers to create professional statements from their Excel commissions spreadsheet.

[ExcelIsFun]

Cool!

[andes2]

soo good thanks

[ExcelIsFun]

You are welcome!

[madmansgates]

Hi Mike,Can you help? Commission rates: 0 - 20,000 - 10% 20,001 - 40,000 - 15% 40,001 - 60,000 - 20% 60,001 - 25% In any 3 months commission is paid monthly depending on sales per month over 3 months commision hurdle. eg: Month 1 Sales of 15k is 15k@10%=1500 commission Month 2 sales of 20k will have commision of 5k@10%(to reach 20k hurdle) and 15k@15% =total of 2750, Month 3 sales of 10k will have commission of 5k @15% (40k hurdle) & 5k at 20% total of 1750 any ideas? Thanks

[ExcelIsFun]

That is a great question! I do not know off the top of my head and right now I have family visiting and a heavy work load. Please post this to the Mr Excel Message Board:

mrexcel[dot]com/forum

Then send me the link via excelisfun YouTube e-mail!

[krn14242]

Mike

Didn't see the download workbook...

thanks

[ExcelIsFun]

Thanks for the heads up! I will upload it tomorrow morning!

[ExcelIsFun]

I just uploaded the workbook!