Hello All. In this tutorial let’s look at how to calculate commission % on total sales based on the set threshold limits or set targets. Let’s see the below data set:
In the data set, we have columns A & B as the target / threshold values (example, if the sale is between >100 but <250, commission% would be 2%. Next, column E,F,G is the sales data for each day in the first half of January. Column J,K,L,M is the actual output columns.
First, Let’s calculate the total sales. We shall use the sumif function to find the total sales for each sales person. The formula would be =SUMIF(F4:F20,J4,G4:G20). Here, the criteria is the name (J4) being searched in the name column in the sales data (F4:F20) and the sum range is the sales column in sales data (G4:G20).
Now, let’s calculate the commission % based on the total sales. We will calculating % based on the threshold values given. Let’s use Vlookup – with TRUE(1) as range lookup value. In our example, the formula would be =VLOOKUP(K4,$A$3:$B$12,2,1). Here, the lookup value is K4 (total sales), table array would be threshold table (A3:B12), column index is 2 (2nd column in table array) and the range lookup would be 1 (which would return approximate values rather than exact values).
Next, we have to calculate the % payout. Simple sales*commission%; in our case =K4*L4%
Please note that this formula would work only when the threshold slabs are in ascending order.
I hope you like this short tutorial. Please let you know your feedback that would help to improve the content. Thanks for reading.