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