Tiered Commission Rates

[Baroda32]

I know this has been discussed before but I can't apply the solutions to my example. We have several different commission bands and each of those bands has a tiered commission rate e.g. Band1, First 0-10K = 1.95%, then from 10k to 20K = .8%, then all the rest is .5%. So what I am looking for is a formula that first of all looks at the band rate and then looks at the sale value and works out the commission based on these bands. I have a sample file but can't see how to upload it here. Any help gratefully received!

 

[joeu2004]

In that case, my use of CHOOSE is not a good one. It is feasible; but the formula is long and tedious to write, even one time. I will consider a different organization of the tables that lends itself to a single SUMPRODUCT formula. However, I think it is important to avoid volatile functions like OFFSET and INDIRECT in this case.

To facilitate design, all the tables must have the same size and spacing between tables. For example, in the Bands worksheet, as you did originally.

[TABLE="class: grid, width: 300"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Band 1[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="align: right"] 1,282.05[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.95%[/TD]
[TD="align: right"]1.95%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]0.80%[/TD]
[TD="align: right"]-1.15%[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.30%[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Band 2[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD="align: right"] 2,499.99[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]1.00%[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.50%[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]0.00%[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]Band 3[/TD]
[TD][/TD]
[TD="align: right"]Rate[/TD]
[TD="align: right"]Diff_Rate[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD="align: right"] 1,428.57[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]1.75%[/TD]
[TD="align: right"]1.75%[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD="align: right"] 10,000.00[/TD]
[TD="align: right"]10,000.00[/TD]
[TD="align: right"]1.00%[/TD]
[TD="align: right"]-0.75%[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD="align: right"] 20,000.00[/TD]
[TD="align: right"]20,000.00[/TD]
[TD="align: right"]0.50%[/TD]
[TD="align: right"]-0.50%[/TD]
[/TR]
</tbody>[/TABLE]

Note the redundant entry in row 10. It is needed so that the Band 2 table is the same size.

But note that the lower limits in column A and B do not need to be the same; we just need the same number of entries.

Aside.... Suppose that in Band 2, we did not pay commission on amounts over 100,000. Then A10 and B10 would be 100,000, and C10 would be 0%.

The formula in A8, for example, is:

=ROUNDUP(25/C8,2)-0.01

That specifies a lower limit on the amount that commissions are awarded for, so that the least commission is 25. Ostensibly, the formula might be:

=ROUNDDOWN(25/C8,2)

But that does not work in A8 because 25/C8 = ROUNDDOWN(25/C8,2).

Note: It would be prudent to write =ROUND(ROUNDUP(25/C8,2)-0.01,2) to ensure that floating-point comparisons always work. Long story!

---

Then in the Data worksheet, we might have:

[TABLE="class: grid, width: 200"]
<tbody>[TR]
[TD="align: right"][/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD="align: right"]Comm_Code[/TD]
[TD="align: right"] Consideration[/TD]
[TD="align: right"] Commission[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD="align: right"]Band 1[/TD]
[TD="align: right"]132,855.29[/TD]
[TD="align: right"]839.28[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD="align: right"]Band 2[/TD]
[TD="align: right"]118,642.15[/TD]
[TD="align: right"]643.21[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD="align: right"]Band 3[/TD]
[TD="align: right"]109,924.12[/TD]
[TD="align: right"]724.62[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD="align: right"]Band 2[/TD]
[TD="align: right"]299.42[/TD]
[TD="align: right"] -[/TD]
[/TR]
</tbody>[/TABLE]

To facilitate design, column C contains just the band number (1, 2 or 3), formatted as
Custom "Band "General.

Ostensibly, the formula in column E is (E2 for example):

=ROUND(SUMPRODUCT(--(D2>OFFSET(Bands!$A$2:$A$4,6*(C2-1),0)),
D2-OFFSET(Bands!$B$2:$B$4,6*(C2-1),0),
OFFSET(Bands!$D$2:$D$4,6*(C2-1),0)), 2)

However, since OFFSET is a volatile function, that formula might be inefficient if we have more than a few hundred. Using OFFSET, all such formulas (and their directly and indirectly dependent cells) are recalculated every time any cell in any worksheet is edited.

Alternatively, the formula might be:

=ROUND(SUMPRODUCT(--(D2>INDEX(Bands!$A:$A,6*(C2-1)+2):INDEX(Bands!$A:$A,6*(C2-1)+4)),
D2-INDEX(Bands!$B:$B,6*(C2-1)+2):INDEX(Bands!$B:$B,6*(C2-1)+4),
INDEX(Bands!$D:$D,6*(C2-1)+2):INDEX(Bands!$D:$D,6*(C2-1)+4)), 2)

or

=ROUND(SUMPRODUCT(--(D2>INDEX(Bands!$A:$A,6*C2-4):INDEX(Bands!$A:$A,6*C2-2)),
D2-INDEX(Bands!$B:$B,6*C2-4):INDEX(Bands!$B:$B,6*C2-2),
INDEX(Bands!$D:$D,6*C2-4):INDEX(Bands!$D:$D,6*C2-2)),2)

The second form is just an algebraic simplfication of the first form.

Although it is more tedious to write, it is more efficient because INDEX is not a volatile function.

Usually, I deprecate the use of ranges like $A:$A because that usually causes Excel to access 1,048,576 cells in Excel 2007 and later. But INDEX($A:$A,...) is acceptable because Excel merely indexes into that range; it does not need to access the entire range.


Hope that helps.

 

[Baroda32]

I replied to this earlier - don't know where it went! Thank you for your help. I haven't had time to do everything - going to try later on tonight as my eyes needed an Excel break! Will let you know how I get on. Thanks again - very helpful.

 

[Baroda32]

Dear Joeu2004 - I am getting in a bit of a muddle here - I think some of it is a bit beyond me. I've fiddled with my data and taken out the stuff we don't need and wondered if you would look at it for me? I copied and pasted your formulas and edited them but no luck so I have stripped them out. Here is the link to the file: https://db.tt/U4g07vYs

Thank you in advance!

 

[joeu2004]

I am getting in a bit of a muddle here - I think some of it is a bit beyond me. I've fiddled with my data and taken out the stuff we don't need and wondered if you would look at it for me? I copied and pasted your formulas and edited them but no luck so I have stripped them out. Here is the link to the file:
https://db.tt/U4g07vYs

I don't see any difference between that file and the previously uploaded file.

No matter. I have modified and annotated changes in "NEW Test Data for Tiered Commission v2.xlsx" (click here). See the (new) Data and Bands worksheets.

I hope that helps. Let me know if you have any questions.


-----
[1] https://www.dropbox.com/s/cujnz7tdny8bhjv/NEW%20Test%20Data%20for%20Tiered%20Commission%20v2.xlsx

 

[Baroda32]

stupid me - I uploaded the wrong one! https://db.tt/A0CWLhjE Here is the real link - I've been in front of the PC too long today - making too many mistakes. Thanks - I'll have to go to bed and get up early to have a look. So sorry when you are doing all this to help me!

 

[joeu2004]

stupid me - I uploaded the wrong one! https://db.tt/A0CWLhjE Here is the real link

Okay. But hopefully you can make use of my modifications of the original file and apply them to the new file. I do not see any conceptual difference. So hopefully I do not need to duplicate my previous efforts. "The exercise is left to the student".

 

[joeu2004]

I have modified and annotated changes in "NEW Test Data for Tiered Commission v2.xlsx" (click here). See the (new) Data and Bands worksheets. I hope that helps. Let me know if you have any questions.

making too many mistakes. Thanks - I'll have to go to bed and get up early to have a look.

How are you doing? Were you able to adapt the formulas? Is your problem resolved?

 

[Baroda32]

Hi joeu2004 - I couldn't work on it yesterday as was on customer site all day - there's something I am still getting wrong but I was tired so it is my aim to do it this morning. Thank you for all your help and I will let you know!