Multi Tiered Commission Function

Good Morning,

I have been working on trying to create a multi tiered commission table and I am having no luck writing the function. The commission breaks are 0-9 no commission, 10-19 is times 10 of the total appointments from 10-19, 20-29 is times 20 of the total appointments from 20-29, and the final is 30-higher is 30 times the total number appointments after 30. So if the person set 31 appointments they would get $360. If the person set 22 they would get $140.

Thanks
Brett

How do you get to 360? Can you break it down? I'm not following the math

Edit: nevermind, i understand, i'll respond with a formula in a minute

One method:


Create a column with the following formula inserted and copy it down for 1000 rows or so (however many will never be exceeded by the Agent Appointments value)

=IF(INT(ROW(1:1)/10)*10>30,30,INT(ROW(1:1)/10)*10)

Assuming you use Column G, use this formula to get your commission:

=SUM(INDIRECT("G1:G" & D2))

Hi,

One way

Formula:

Please Login or Register  to view this content.

The only prolem is that when I put in 10 appointments it should be 10 bucks but my sheet shows -170.

Another option
=LOOKUP(A1,{0,10,20,30},{0,10,20,30}*(A1-{0,9,19,29})+{0,0,100,300})
Note: 22 should get 160, not 140 as you posted.

Originally Posted by bbeards2

The only prolem is that when I put in 10 appointments it should be 10 bucks but my sheet shows -170.

Hi,

OK, typo in middle element. Herewith correction.

Formula:

Please Login or Register  to view this content.