Microsoft Office Tutorials and References

In Depth Information

**Function Examples**

Note that the commission rate is nonlinear and also depends on the month’s total sales.

Employees who sell more earn a higher commission rate.

You can calculate commissions for various sales amounts entered into a worksheet in several

ways. If you’re not thinking too clearly, you can waste lots of time and come up with a lengthy

formula such as this one:

=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))))

This approach is bad for a couple of reasons. First, the formula is overly complex, making it

difficult to understand. Second, the values are hard-coded into the formula, making the formula

difficult to modify.

A better (non-VBA) approach is to use a lookup table function to compute the commissions. For

example, the following formula uses
VLOOKUP
to retrieve the commission value from a range

named
Table
and multiplies that value by the value in cell A1.

=VLOOKUP(A1,Table,2)*A1

Yet another approach (which eliminates the need to use a lookup table) is to create a custom

function such as the following:

Function Commission(Sales)

Const Tier1 = 0.08

Const Tier2 = 0.105

Const Tier3 = 0.12

Const Tier4 = 0.14

‘ Calculates sales commissions

Select Case Sales

Case 0 To 9999.99: Commission = Sales * Tier1

Case 1000 To 19999.99: Commission = Sales * Tier2

Case 20000 To 39999.99: Commission = Sales * Tier3

Case Is >= 40000: Commission = Sales * Tier4

End Select

End Function

After you enter this function in a VBA module, you can use it in a worksheet formula or call the

function from other VBA procedures.

Entering the following formula into a cell produces a result of 3,000; the amount — 25,000 —

qualifies for a commission rate of 12 percent:

=Commission(25000)