Microsoft Office Tutorials and References

In Depth Information

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 re-

turns 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,4),”Clubs”,”Hearts”,”Diamonds”,”Spades”)

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

tion 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 commis-

sion rate (see Table 26-1). For example, a salesperson with sales between $10,000 and $19,999 qualifies for a

commission rate of 10.5%.

Table 26-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%

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,