The issue I see with marginal rate tables like this is that the end slab quantity does not match the next start slab quantity. Typically these tables are set up to show a difference of 1 to help clarify the boundary, but this leaves "1" completely unaccounted for between each slab. In the example below, I've treated the start slab as though it has the same values as the end slab, except starting at 0. For example, for Customer A, the start slab is {0; 10000; 50000; 125,000}, and the condition applied to the start slab is that a Sales quantity must be greater than the slab/tier lower boundary (not greater than or equal to). This approach doesn't leave the 1's unaccounted for between slabs. Consequently, the results are slightly different from your original post. Another way to think about this is to consider what the commission would be for Customer A if the Sales were 10,000.9. Would it be 10000 * 0.02=200.00 or 10000 * 0.02 + 0.9 * 0.03 = 200.027?
This formula assigns the entire slab data table to a variable called "slab", and then it is filtered to extract the relevant portion for the specified customer...and this portion of the slab array is called "cslab" (short for customer slab). Then the start slab is reestablished based on the point made above, by using the end slab values, except a 0 is used for the starting point....this revised start slab is called "csslab" (short for customer start slab). The commission for the customer is given a convenient variable name, "ccom". Next, the formula approach taken for the final calculation of commission relies on a differential rate between slabs/tiers. So the index of the commission rate array (ccom) is effectively shifted by one so that the difference can be taken between successive slabs. This shifting operation is performed by the formula assigned to the "ccom_shft" variable (short for customer commission shifted). The final calculation is performed by SUMPRODUCT.
| MrExcel_20240301.xlsx |
|---|
|
|---|
| A | B | C | D | E | F | G | H |
|---|
| 1 | | | | | | | | |
|---|
| 2 | Customer Name | Sales | Prospective Commission | | Customer Name | Start Slab | End Slab | Commission |
|---|
| 3 | A | 100000 | 3400.00 | | A | 0 | 10000 | 0.02 |
|---|
| 4 | S | 200000 | 14750.00 | | A | 10001 | 50000 | 0.03 |
|---|
| 5 | | | | | A | 50001 | 125000 | 0.04 |
|---|
| 6 | | | | | A | 125001 | | 0.05 |
|---|
| 7 | | | | | S | 0 | 50000 | 0.06 |
|---|
| 8 | | | | | S | 50001 | 100000 | 0.07 |
|---|
| 9 | | | | | S | 100001 | 175000 | 0.08 |
|---|
| 10 | | | | | S | 175001 | | 0.09 |
|---|
|
|---|
