Microsoft Office Tutorials and References

In Depth Information

The VLOOKUP and HLOOKUP are a convenient way to return a value from

a table based on a comparison to another value. Except for the obvious difference

in vertical and horizontal orientation, there is no difference in the cell functions.

Consider the utility of such a function. In Chap. 4 we used the IF() to convert a

dollar value into a
payment category
. In that example, we noted that depending on

the number of categories, we might have more categories than the maximum allowed

number of
nested
IF()s; thus, under these circumstances a lookup function should

be used.

Now, let us take a look at the examples in Exhibit 8.11, lookups that convert

a fractional value from 0 to 1 into a numerical dollar value. We will concentrate

on the VLOOKUP due to the vertical nature of the Poisson cumulative probability

table we used earlier. The function has three important arguments:
value_lookup
,

table_array
, and
col_index_num
. The lookup requires that a table be constructed

with two types of values—a lookup value and a table value. The lookup value used

in the example is a RAND() and represents a random sampling process, and this

value is to be converted by the table to some associated table value.

We begin by generating a RAND(). It is compared to the leftmost column of a

vertical table or the top row of a horizontal table. In Exhibit 8.11 a lookup value is in

cell F4 and the table is located in B4:C12. The random number generated in the ver-

tical example is 0.0625664. The last argument in the lookup function is the column

index number which is to be returned, in this case 2, which will return a value in the

2
nd
column of the table. In simple two column tables, this number is always 2. If a

table has more that two columns to permit return of other associated values, or to

combine several tables into one, then a column number must be chosen to represent

the table value of interest. Finally, the function
takes
the value lookup (0.0625664)

and ﬁnds the region in the table that contains the value. For our example, 0.0625664

is found between 0 (B4) and 0.1 (B5). The convention used for returning a value

from the 2
nd
column is to return the value associated with the topmost value (0) in

the range. In this case, the return is $14 which is adjacent to 0. If the lookup value

from the RAND() function had been exactly 0.1, the value returned would have been

$16.

Now, let’s return to our Poisson table example in Exhibit 8.10 to describe how

we perform random sampling of a Poisson distribution. The VLOOKUP in cell E22

compares the value of a random number, D22, with the table of cumulative prob-

ability numbers. It then returns a value from 0-12 depending of the value of the

RAND(). For example, the random number 0.7398736 is generated in cell D22 and

the VLOOKUP searches values in D7 through D19. When it encounters a value

higher than the random number, in this case 0.7851304, it returns the value in col-

umn F in the row
above
0.7851304. The number returned, 5, is in the 3
rd
column

of the table. If we repeat the process many, many times, the recorded values will

have a frequency distribution that is approximately Poisson distributed, which is, of

course, what we are attempting to achieve. Note that we can use the same approach

for any Discrete distribution: (1) create a cumulative probability distribution and, (2)

sample using a RAND() function to determine a randomly selected value from the

distribution. This simple mechanism is a fundamental tool for generating uncertain

events from a distribution.

Search JabSto ::

Custom Search