Forum Discussion
Excel formula with commission based tiers
Hello!
I can't wrap my head around how to create this formula.
In cell I20 I have an employee's net sales. In I21 I want to create a formula that will give their commission earnings.
So for example, if the employee has $132,000 in annual sales, their commission tier would be 2%, BUT they wouldn't just get a blanket 2% on their annual sales. They would have 0% commission on the first $50,000, 1% on the $50k-100k, etc.
Thank you!
Let me suggest you arrange your table something like the IRS does its tax tables.
Doing so, and you'll want to play with this to make sure it's giving you the results you want, I came up with the following.
See the attached
kittenmeants I will also suggest a table change and although I concur with mathetes option here is an alternative:
The sales becomes 2 columns to make reference much easier and then a single formula:
=SUMPRODUCT((E2>B2:B6)*(B2:B6-A2:A6)*(C2:C6)+(E2>A2:A6)*(E2<B2:B6)*(E2-A2:A6)*(C2:C6))I would even recommend using named regions so it could be:
=SUMPRODUCT((E2>Sales_high)*(Sales_high-Sales_low)*(Commission)+(E2>Sales_low)*(E2<Sales_high)*(E2-Sales_low)*(Commission))which can make it much more readable
