How to calculate commissions in excel?

---

Often you may want to calculate commissions for an employee based on their sales numbers.

For example, you might want to use the following table in Excel to determine the commission to pay an employee based on their total sales for the year:

Here is how to interpret this table:

• If an employee has sales between $0 < $5,000, they receive 0% commission.
• If an employee has sales between $5,000 < $10,000, they receive 2% commission.
• If an employee has sales between $10,000 < $20,000, they receive 5% commission.
• If an employee has sales between $20,000 < $40,000, they receive 8% commission.
• If an employee has sales > $40,000, they receive 12% commission.

The following example shows how to use a VLOOKUP function in Excel to determine the sales commission to pay an employee based on their total sales.

Example: How to Calculate Commissions in Excel

Suppose an employee has a total of $11,000 in sales for a given year.

We can type this sales amount into cell E1 and then type the following formula into cell E2 to determine their commission rate:

=VLOOKUP(E1, $A$2:$B$6, 2, TRUE)

The following screenshot shows how to use this formula in practice:

The formula returns a commission rate of 5% since the sales value of $11,000 fell between the range $10,000 and $20,000.

Suppose we change the sales value to $27,000:

The formula now returns a commission rate of 8% since the sales value of $27,000 fell between the range $20,000 and $40,000.

How This Formula Works

Note that the VLOOKUP function uses the following basic syntax:

VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

where:

• lookup_value: The value to look up
• table_array: The range of cells to look in
• col_index_num: The column number that contains the return value
• range_lookup: TRUE = look for approximate match, FALSE = look for exact match

By using a value of TRUE for the last argument, you can look up a value that falls in a range.

For example, when we looked up the value $11,000 in the commission table this exact value didn’t exist in the Sales column so the VLOOKUP function looked for the next largest value in the Sales column that was less than $11,000.

The formula identified the value of $10,000 and returned the commission rate that corresponded to this value, which was 5%.

Note that the values in the Sales column are already sorted from least to greatest, which is why the VLOOKUP function was able to correctly identify the next largest value that was less than the lookup value.

If the first column in your lookup range is not sorted, the VLOOKUP function could return unexpected results.