[Solved] How to create Sliding Scale Commission Calculator in Excel

[JOJO]

Hello everyone, I am a new member. I am retired and I do exercises in Excel. I want to perform this calculation in the same instruction. Waiting for your contributions.

 

[Lutfor Rahman Shimanto]

Hello everyone, I am a new member. I am retired and I do exercises in Excel. I want to perform this calculation in the same instruction. Waiting for your contributions.View attachment 719

Dear JOJO

Thanks for reaching out and sharing your problem. The problem you describe is not 100% clear to us. However, after going through the screenshot, I understand that you want to perform the Cumulative Sliding Scales. I have divided the goal into two requirements.

Requirement 1: For the first $100,000 sales, the commission will be 35%, and for the second sales up to $ 300,000, it will be 30%, and for the third sales up to $1500000, it will be 20%. Later, you want to sum these for each Sales Rap and put it in another column, Commission Earned.

Requirement 2: If any sales rap's sale does not exceed the second and third criteria, then it will be 35%. If anyone's sales do not exceed third, for The first 100,000 sales, the commission will be 35%, and for the second sales up to 30,000, it will be 30%. Later, you have to sum these for each sales rap and put it in commission earned.

Select cell F19 >> apply the following formula >> drag the Fill Handle icon to F35.

=IF(E19<=100000, E19*0.35, IF(E19<=300000, (200000*0.3+100000*0.35), IF(E19<=1500000, (E19-300000-100000)*0.2+300000*0.3+100000*0.35, "")))

​

I recommend you to visit an article in ExcelDemy that must help.

• Excel Formula to Calculate Sliding Scale Commission​

I am attaching the solution workbook for better understanding. Do not hesitate to ask more queries regarding this problem. We are here to help you.

Regards
Lutfor Rahman Shimanto