While working with Microsoft Excel, sometimes we need to calculate tiered commission. The tiered commission is given to the sales representatives so that a company earns more revenue. Calculating tiered commission in Excel is an easy task. This is a time-saving task also. Today, in this article, we’ll learn three quick and suitable ways to calculate tiered commission in Excel effectively with appropriate illustrations.
Download Practice Workbook
Download this practice workbook to exercise while you are reading this article.
Introduction to Tiered Commission
Sales representatives are motivated by commission rate tiers in a tiered commission system. Tiered commission programs, as opposed to flat compensation plans, motivate sales representatives to meet sales targets. A seller’s commission grows as their performance improves.
For instance, suppose sales representatives get a 3% base commission on every deal until they sell $50,000.00 in total revenue at the start of a new fiscal year. Following that, they’ll make 4% until they sell $100,000.00, at which point they’ll increase to 5%, and so on.
This form of payment plan is designed to encourage sales representatives to fulfill a quota and, ideally, to keep closing transactions in order to meet or surpass their targets. Tiered commission schemes work because the more sales representatives who sell, the more money they make.
3 Suitable Ways to Calculate Tiered Commission in Excel
Let’s assume we have an Excel large worksheet that contains the information about several sales representatives of Armani Group. The name of the sales representatives, the sales range, the percentage of tiered commission, and the Sales value by the sales representatives are given in Columns B, C, E, and F respectively. From our dataset, we will calculate a tiered commission. We can easily calculate a tiered commission in Excel by using the IF, VLOOKUP, and SUMPRODUCT functions. Here’s an overview of the dataset for today’s task.
1. Use IF Function to Calculate Tiered Commission in Excel
Undoubtedly, the most effective way to calculate a tiered commission is in Excel using the IF function. From our dataset, we can easily calculate a tiered commission. This is a time-saving task also. Let’s follow the instructions below to calculate a tiered commission!
- First, select a cell to apply the IF function. From our dataset, we will select cell G5 for the convenience of our work.
- Hence, write down the IF function in that cell. The IF function is,
- Most inside the IF function F6<=D$13 is the logical_test, E$13 is the value_if_TRUE, and 0 is the value_if_FALSE. Similarly, the rest of the IF function returns the same output.
- After that, simply press Enter on your keyboard. As a result, you will get $450.00 as the output of the IF function.
- Further, autoFill the IF function to the rest of the cells in column G which has been given in the below screenshot.
2. Apply VLOOKUP Function to Calculate Tiered Commission in Excel
In this portion, we will apply the VLOOKUP function to calculate tiered commission in Excel. For instance, we will calculate the tiered commission for the sale target of $20,000.00. Let’s follow the instructions below to calculate a tiered commission!
- First, select a cell to apply the VLOOKUP function. From our dataset, we will select cell D16 for the convenience of our work.
- After that, type the VLOOKUP function in the Formula bar. The VLOOKUP function in the Formula bar is,
- Where D15 is the lookup_value.
- C6:E13 is the table_array.
- 3 is the col_index_num.
- TRUE is the approximate match.
- Hence, simply press Enter on your keyboard. As a result, you will get 0.01 as the output of the VLOOKUP function.
- Further, select cell D17 and write down the below mathematical formula in that cell.
- Again, press Enter on your keyboard. As a result, you will get the tiered commission, and the tiered commission is $200.00 which has been given in the below screenshot.
- Now, we will check the VLOOKUP function whether works or not. To do that, we will simply change the sales value. We choose $300,000.00 as the sales value and press ENTER. We will get the output of the VLOOKUP function given in the screenshot below.
3. Perform SUMPRODUCT Function to Calculate Tiered Commission in Excel
Last but not the least, we will apply the SUMPRODUCT function to calculate the tiered commission in Excel. From our dataset, we can easily and effectively calculate the tiered commission. Let’s follow the instructions below to calculate a tiered commission!
- First, select cell G5 to apply the SUMPRODUCT function. After that, type the SUMPRODUCT function in the Formula bar. The SUMPRODUCT function in the Formula bar is,
- Hence, simply press Enter on your keyboard. As a result, you will get $1,249.99 as the return of the SUMPRODUCT function.
- Hence, autoFill the SUMPRODUCT function to the rest of the cells in column G.
Things to Remember
👉 #N/A! error arises when the formula or a function in the formula fails to find the referenced data.
👉 #DIV/0! error happens when a value is divided by zero(0) or the cell reference is blank.
👉 In Microsoft 365, Excel will show the #Value! Error if you don’t select the proper dimension. The #Value! error occurs when any of the elements of the matrices is not a number.
I hope all of the suitable methods mentioned above to calculate tiered commission will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.
- How to Calculate Bonus Percentage in Excel (Step-by-Step Guide)
- Bonus Calculation as per Bonus Act in Excel (3 Suitable Examples)
- Calculate Bonus on Salary in Excel (7 Suitable Methods)
- How to Calculate Bonus in Excel (5 Handy Methods)
- Excel Formula to Calculate Sliding Scale Commission (5 Suitable Examples)