Excel 2007 Formulas (Mr. Spreadsheets Bookshelf)

Sales managers often need to calculate the commissions earned by their sales forces. The calculations in the function example presented here are based on a sliding scale: Employees who sell more earn a higher commission rate (see Table 25-1). For example, a salesperson with sales between $10,000 and $19,999 qualifies for a commission rate of 10.5 percent.

You can calculate commissions for various sales amounts entered into a worksheet in several ways. You can use a complex formula with nested IF functions, such as the following:

=IF(A1<0,0,IF(A1<10000,A1*0.08, IF(A1<20000,A1*0.105, IF(A1<40000,A1*0.12,A1*0.14))))

This may not be the best approach for a couple of reasons. First, the formula is overly complex, thus making it difficult to understand. Second, the values are hard-coded into the formula, thus making the formula difficult to modify.

A better approach is to use a lookup table function to compute the commissions. For example:

=VLOOKUP(A1,Table,2)*A1

Using VLOOKUP is a good alternative, but it may not work if the commission structure is more complex. (See the next subsection for more information.) Yet another approach is to create a custom function.

A Function for a Simple Commission Structure

The following COMMISSION function accepts a single argument (Sales) and computes the commission amount:

Function COMMISSION(Sales As Double) As Double ' Calculates sales commissions Const Tier1 As Double = 0.08 Const Tier2 As Double = 0.105 Const Tier3 As Double = 0.12 Const Tier4 As Double = 0.14 Select Case Sales Case Is >= 40000 COMMISSION = Sales * Tier4 Case Is >= 20000 COMMISSION = Sales * Tier3 Case Is >= 10000 COMMISSION = Sales * Tier2 Case Is < 10000 COMMISSION = Sales * Tier1 End Select End Function

The following worksheet formula, for example, returns 3,000 (the sales amount-25,000-qualifies for a commission rate of 12 percent):

=COMMISSION(25000)

This function is very easy to understand and maintain. It uses constants to store the commission rates as well as a Select Case structure to determine which commission rate to use.

A Function for a More Complex Commission Structure

If the commission structure is more complex, you may need to use additional arguments for your COMMISSION function. Imagine that the aforementioned sales manager implements a new policy to help reduce turnover: The total commission paid increases by 1 percent for each year that a salesperson stays with the company.

The following is a modified COMMISSION function (named COMMISSION2). This function now takes two arguments: the monthly sales (Sales) and the number of years employed (Years).

Function COMMISSION2(Sales As Double, Years As Long) As Double ' Calculates sales commissions based on ' years in service Const Tier1 As Double = 0.08 Const Tier2 As Double = 0.105 Const Tier3 As Double = 0.12 Const Tier4 As Double = 0.14 Select Case Sales Case Is >= 40000 COMMISSION2 = Sales * Tier4 Case Is >= 20000 COMMISSION2 = Sales * Tier3 Case Is >= 10000 COMMISSION2 = Sales * Tier2 Case Is < 10000 COMMISSION2 = Sales * Tier1 End Select COMMISSION2 = COMMISSION2 + (COMMISSION2 * Years / 100) End Function

Figure 25-3 shows the COMMISSION2 function in use. The formula in cell D2 is

=COMMISSION2(B2,C2)

Figure 25-3: Calculating sales commissions based on sales amount and years employed.