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 fo...
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