Rubayed Razib Suprov
The Calculation of commission is one of the basic tasks of Excel applications for a long period of time. Calculating commission based on the sales level is also a very common operation. Both types of problems are taken care of in this article. In this article, we will discuss how you can calculate the commission in Excel using the IF function.
Download Practice Workbook
3 Separate Ways to Calculate the Commission Values in Excel Using IF Function
Below, we have the dataset that contains the commission rate depending on the sales figure on the range of cells B12:C13. We just need to calculate the commission value in the range of cells D5:D8. And those commission values are calculated using the rates depending on the sales values themselves. To accomplish this, we had to adopt varieties of formulas in this article. In order to avoid any kind of compatibility issue, try to use the Excel 365 edition.
1. Determine Commission with Nested IF Functions
Commission values stored in the range of cells can be retrieved and used to calculate the commission value according to the sales. For this, we used the Nested IF functions.
Steps:
- The formula will be set up in a way that will calculate the commission based on the sales range. The commission rates are mentioned in the range of cells B12:C14.
- Select cell D5 and enter the following formula:
=IF(C5<10000,C5*$C$12, IF(C5>=10000, C5*$C$13,0))
- Press Enter after this.
🔎 Formula Breakdown
- IF(C5>=10000, C5*$C$13,0)
In this part of the formula, the IF function checks whether the sales value mentioned in cell C5 is greater than 1000 or not. If the value is greater than or equal to 1000, then it will multiply the value with the commission rate mentioned in cell C13. Otherwise, the IF function will simply return a null value.
- IF(C5<10000,C5*$C$12, IF(C5>=10000, C5*$C$13,0))
This part of the formula will check whether the value mentioned in cell C5 is less than 1000 or not. If the cell value is less than 1000, then it will multiply the cell value in C5 with the commission rate mentioned in cell C12. Otherwise, it will check whether the value is greater than 1000 or not, in the next formula argument. That is the output mentioned in the previous step.
- Then drag the Fill Handle to cell D8.
- Doing this will calculate the commission based on the sales figure.
- And obviously, those commissions depend upon the rate mentioned in the range of cell B12:C13.
Read More: How to Calculate Bonus Percentage in Excel (Step-by-Step Guide)
2. Combine IF with AND Function to Get Commission Using Multiple Rates
A combination of IF and AND logical functions are used here to find the commission of the sales figure. This combination allows the use of multiple commission rates based on sales value.
Steps:
- Select cell D5 and enter the following formula:
=IF(C5<10000,C5*$C$12,IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0)))
- Press Enter after this.
🔎 Formula Breakdown
- IF(AND(C5<40000,C5>10000),C5*$C$13,0)
In this part of the formula, the IF function checks out whether the sales values mentioned in cell C5 are greater than 1000 and less than 40000 at the same time. If the value is greater than 10000 and less than 4000, then it will multiply the value with the commission rate mentioned in cell C13. Otherwise, the IF function will simply return a null value.
- IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0))
This part of the formula will check whether the value mentioned in cell C5 is greater than 40000 or not. If the cell value is greater than 40000, then it will multiply the cell value in C5 with the commission rate mentioned in cell C13. Otherwise, it will return the output value in the previous step.
- IF(C5<10000,C5*$C$12,IF(C5>40000,C5*$C$14,IF(AND(C5<40000,C5>10000),C5*$C$13,0)))
Finally, this part of the formula checks out whether the value in cell C5 is less than 10000 or not
If it is less than 10000 then it will multiply the value in cell C5 with the commission value in cell C12. If not, then it will jump to the next part of the formula which is in the previous part of the formula.
- After entering the formula, you will notice that the commission of the sales figure is based on the rat mentioned in the range of cells B12:C13.
Read More: Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)
3. Use IF and VLOOKUP Functions
This combination of IF and VLOOKUP functions allows us to extract the commission rate with the type value range of the cell efficiently. Later, using those commission rates, we can calculate the total commission.
Steps:
- The formula will be set up in a way that will calculate the commission on the sales range. The commission rates are in the range of cells B12:C14.
- Select cell D5 and enter the following formula:
=IF(B5>=C5, B5*VLOOKUP(D5,$B$12:$E$14,3,FALSE),"Target Not Met")
- Press Enter after this.
- VLOOKUP(D5,$B$12:$E$14,3,FALSE),”Target Not Met”)
This part of the formula will check whether the value mentioned in cell D5 is in the range of cell B12:E14. And after finding the match, it will then return the corresponding stored commission value in the number 3 column in the selected range. FALSE set to indicate that we want an exact match in the range of cells.
- IF(B5>=C5, B5*VLOOKUP(D5,$B$12:$E$14,3,FALSE),”Target Not Met”)
Finally, this part of the formula checks out whether the value in cell B5 is greater or equal to the target value mentioned in cell C5. If it is greater than C5 then it will multiply the value in cell B5 with the commission value returned in the previous formula. If not, then it will jump to the next part of the formula which is in the previous part of the formula.
- Then drag the Fill Handle to cell D8.
- Doing this will calculate the commission based on the sales figure.
- And obviously, those commissions’ value depends upon the rate mentioned in the range of cell B12:C13.
Read More: How to Calculate Commission in Excel Using VLOOKUP
Conclusion
To sum it up, how we can calculate the commission in Excel using the IF function in three separate ways. For this problem, a workbook is available to download where you can practice these methods. Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.
