A single-argument function is designed for sales managers who need to calculate the commissions earned by their salespeople. The commission rate depends on the monthly sales volume; those who sell more earn a higher commission rate. The function returns the commission amount based on the monthly sales (which is the function's only argument — a required argument). The calculations in this example are based on Table 21-1.

Table 21-1 |
Commission Rates by Sales |

Monthly Sales |
Commission Rate |

80-89,999 |
8.0% |

810,000-819,999 |
10.5% |

820,000-839,999 |
12.0% |

840,000+ |
14.0% |

You can use several approaches to calculate commissions for sales amounts entered into a worksheet. You could write a lengthy worksheet formula such as this:

=IF(AND(A1>=0,A1<=9999.99),A1*0.08,IF(AND(A1>=10000,A1<=19999 .99),A1*0.105,IF(AND(A1>=20000,A1<=39999.99),A1*0. 12,IF(A1>=40000,A1*0.14,0))))

A couple reasons make this a bad approach. First, the formula is overly complex. Second, the values are hard-coded into the formula, making the formula difficult to modify if the commission structure changes.

A better approach is to create a table of commission values and use a LOOKUP table function to compute the commissions:

=VLOOKUP(A1,Table,2)*A1

Another approach, which doesn't require a table of commissions, is to create a custom function:

Function Commission(Sales) | ||

' Calculates sales commissions | ||

Dim Tierl As Double, Tier2 As Double | ||

Dim Tier3 As Double, Tier4 As Double | ||

Tierl = 0.08 | ||

Tier2 = 0.105 | ||

Tier3 = 0.12 | ||

Tier4 = 0.14 | ||

Select Case Sales | ||

Case 0 To 9999.99: Commission = Sales |
* Tierl | |

Case 10000 To 19999.99: Commission = |
Sales * Ti |
er2 |

Case 20000 To 39999.99: Commission = |
Sales * Ti |
er3 |

Case Is >= 40000: Commission = Sales |
* Tier4 | |

End Select | ||

Commission = Round(Commission, 2) | ||

End Function |

After you define this function in a VBA module, you can use it in a worksheet formula. Entering the following formula into a cell produces a result of 3,000. The amount of 25000 qualifies for a commission rate of 12 percent:

=Commission(25000)

Figure 21-1 shows a worksheet that uses this new function.

Figure 21-1:

Using the Commission function in a worksheet.

Figure 21-1:

Using the Commission function in a worksheet.

l-¿¿] Microsoft Excel - custom functions.xls |
a â\m | |||||||||

: File Edit View insert Format loo Is Data Window Help : J J J J 1 J a 1 V fi 1 t ^ a - J M - ' 1A E - Il mm 4 too*. |
B X 't | |||||||||

flrial - m . I B I a |
U1LS % ' ?P5 i™ I |
1FÍFIL |
i^iA'a | |||||||

C2 jf, fe =commission(B2) | ||||||||||

mui s m |
D |
E l e |
e h |
a | ||||||

1 |
Hiriie |
Sales Commissinn | ||||||||

2 |
Adams |
feS93.00| $8,677J62 |
■ | |||||||

3 |
BatiÉ |
MIMffW (280.4B | ||||||||

4 |
Bouqlas |
:(38':973.00 S4.67E..7B | ||||||||

S |
Fmrrrett |
(32,092.00 (3,851 04 | ||||||||

6 |
FrankEin |
(27:354.00 (iï8ï:i8 | ||||||||

7 |
Johnsorr |
(17-,333.00 (1,872 43 | ||||||||

8 |
Kb tit": |
(41,598.00 (5:,823 72 | ||||||||

9 |
Ï32 |
(3,840 00 | ||||||||

10 |
Quincy |
(5,000.00 |
(400 00 | |||||||

11 |
Randall |
(88;.793:00 |
(9 £31 02 | |||||||

■:. |
Smith |
131,093.00 |
(3,731.IB | |||||||

w |
Walker |
(24:609:00 |
(2,941.03 | |||||||

14 |
Zeller |
(41,544.00 (5,816.1B | ||||||||

15 |
v* | |||||||||

n k |
► >f[\ Sheetl\Shset2/i |
l< „,, i |
> | |||||||

A function with two arguments

The next example builds on the preceding one. Imagine that the sales manager implements a new policy: The total commission paid increases by 1 percent for every year the salesperson has been with the company.

I modified the custom Commission function (defined in the preceding section) so that it takes two arguments, both of which are required arguments. Call this new function Commission2:

Function Commission2(Sales, Years)

' Calculates sales commissions based on years in service Dim Tierl As Double, Tier2 As Double Dim Tier3 As Double, Tier4 As Double Tierl = 0.08 Tier2 = 0.105 Tier3 = 0.12 Tier4 = 0.14 Select Case Sales

Case 0 To 9999.99: Commission2 = Sales * Tierl Case 10000 To 19999.99: Commission2 = Sales * Tier2

Case 20000 |
To 39999.99: Commission2 = |
Sales * |
Tier3 |

Case Is >= |
40000: Commission2 = Sales |
* Tier4 | |

End Select | |||

Commission2 = |
Commission2 + (Commission2 |
* Years |
/ 100) |

Commission2 = |
Round(Commission2, 2) | ||

End Function |

I simply added the second argument (Years) to the Function statement and included an additional computation that adjusts the commission before exiting the function. This additional computation multiplies the original commission by the number of years in services, divides by 100, and then adds the result to the original computation.

Here's an example of how you can write a formula by using this function. (It assumes that the sales amount is in cell A1; cell B1 specifies the number of years the salesperson has worked.)

Was this article helpful?

## Post a comment