So I have a convoluted calculation that I need assistance with.
I am trying to calculate commission for hair stylists and additional commission for products that they sell.
I have no problem with the calculation that I am using for calculating their commission. Here is an example of what I am using.
=SUMPRODUCT((C13>{0,600,1000,1500,2000,2500,3000})*(C13-{0,600,1000,1500,2000,2500,3000})*{0.38,0.06,0.03,0.03,0.05,0.03,0.02})
This will give them a tiered commission. Again, this works just fine.
Now... I am trying to create a second calculation that pays them a tiered commission based on products that they sell. This would be a tiered commission again, but only if they sell a certain percentage of the services they do.
The employees earn a tiered commission once they sell at least 10% of their total services.
They get 10% of all product sales that are 10-14.9% of their services.
They get 15% of all product sales that are 15-19.9% of their services.
They get 20% of all product sales that are 20% or higher of their services.
Here is the example:
My stylist does $1000 a week in services and $90 in product sales. They would not get any commission for product sales as they are less than 10%.
My stylist does $1000 a week in services and $175 in product sales. They would get 10% of the first $149 which would be $14.90. They would get 15 percent of the $26 which would be $3.90. So their total commission would be $18.80.
My stylist does $1000 a week in services and $275 in product sales. They would get 10% of the first $149 which would be $14.90. They would get 15 percent of the $50 which would be $7.50. They would get 20% of $76 which would be $15.20. So their total commission would be $37.60.
That being said, how can I do this? I put the total of services in one cell and the total of product sales in another cell. I need to use the above example and make a tiered commission.
Thanks in advance,
Lyle
I am trying to calculate commission for hair stylists and additional commission for products that they sell.
I have no problem with the calculation that I am using for calculating their commission. Here is an example of what I am using.
=SUMPRODUCT((C13>{0,600,1000,1500,2000,2500,3000})*(C13-{0,600,1000,1500,2000,2500,3000})*{0.38,0.06,0.03,0.03,0.05,0.03,0.02})
This will give them a tiered commission. Again, this works just fine.
Now... I am trying to create a second calculation that pays them a tiered commission based on products that they sell. This would be a tiered commission again, but only if they sell a certain percentage of the services they do.
The employees earn a tiered commission once they sell at least 10% of their total services.
They get 10% of all product sales that are 10-14.9% of their services.
They get 15% of all product sales that are 15-19.9% of their services.
They get 20% of all product sales that are 20% or higher of their services.
Here is the example:
My stylist does $1000 a week in services and $90 in product sales. They would not get any commission for product sales as they are less than 10%.
My stylist does $1000 a week in services and $175 in product sales. They would get 10% of the first $149 which would be $14.90. They would get 15 percent of the $26 which would be $3.90. So their total commission would be $18.80.
My stylist does $1000 a week in services and $275 in product sales. They would get 10% of the first $149 which would be $14.90. They would get 15 percent of the $50 which would be $7.50. They would get 20% of $76 which would be $15.20. So their total commission would be $37.60.
That being said, how can I do this? I put the total of services in one cell and the total of product sales in another cell. I need to use the above example and make a tiered commission.
Thanks in advance,
Lyle
