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,
Search JabSto ::




Custom Search