Microsoft Office Tutorials and References

In Depth Information

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

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