SumProduct calculation

[lylebrous]

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

 

[lylebrous]

Re: Need help with Sum Product calculation

Hi Dave,
The arithmetic approach worked. Thank you!

Last question. If both A2 and B2 are $0, how can I get the resulting cell to display zero instead of the error #DIV/0?

 

[Dave Patton]

Re: Need help with Sum Product calculation

=IF(A2=0,"",formula

If I were you, I would review the business logic of your entire commission calculation.
Make the process as simple as possible.
Ensure that the definition is clear.
Complete several working examples with expected results.
Then if the calculation is still complex, start a new thread and request a UDF User Defined Function

 

[lylebrous]

Re: Need help with Sum Product calculation

Hi Dave,
That worked, thank you.

I adjusted the percentages to be equal numbers. The owners and powers to be are convoluted in general. But I tested many scenarios and they all appear to be corect.

Thanks for all your assistance.

Lyle