Microsoft Office Tutorials and References

In Depth Information

**Calculating Sales Commissions**

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 “A function for a simple commission structure” section 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):