Microsoft Office Tutorials and References

In Depth Information

**Calculating Sales Commissions**

A more general function, one that accepts array constants as well as ranges, is shown here:

Function DRAWONE2(rng As Variant) As Variant

‘ Chooses one value at random from an array

Dim ArrayLen As Long

If TypeName(rng) = “Range” Then

DRAWONE2 = rng(Int((rng.Count) * Rnd + 1)).Value

Else

ArrayLen = UBound(rng) – LBound(rng) + 1

DRAWONE2 = rng(Int(ArrayLen * Rnd + 1))

End If

End Function

This function uses the VBA built-in
TypeName
function to determine whether the argument

passed is a
Range
. If not, it’s assumed to be an array. Following is a formula that uses the

DRAWONE2
function. This formula returns a text string that corresponds to a suit in a deck of

cards:

=DRAWONE2({“Clubs”,”Hearts”,”Diamonds”,”Spades”})

Following is a formula that has the same result, written using Excel’s built-in functions:

=CHOOSE(RANDBETWEEN(1,3),”Clubs”,”Hearts”,”Diamonds”,”Spades”)

I present two additional functions that deal with randomization later in this chapter (see the

“Advanced Function Techniques” section).

Calculating Sales Commissions

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.

Table 25-1:
Commission Rates for Monthly Sales

Monthly Sales

Commission Rate

Less than $10,000

8.0%

$10,000 to $19,999

10.5%

$20,000 to $39,999

12.0%

$40,000 or more

14.0%