|
|
|
|
|
|
|
|
Welcome again to the Family Computer Club. In
the last video I showed
you how to generate random numbers. Today we will simulate values of a
discrete random variable using the RAND() function. For example, a
small shop believes that their demand for a 'Fashion' magazine is
governed by the discrete random variables shown in the training video.
The values indicate that a demand of 15 magazines occurs 10% of the
time, a demand of 20 magazines happens 20% of the time and so on. Now
how can we simulate this data many times in Excel? We associate each value of the
RAND() function with the possible demand probability for the magazine
'Fashion'. We create a lookup table
in the
range E3:F8 with the headers
'Probability' and 'Demand' in F2 and G2 respectively as shown. We can
also name the range to 'data' by selecting 'E3:F8' and writing the name
in the namebox. Random numbers greater than or equal to 0.1 yields a
demand of 15, random numbers greater than 0.1 but less than 0.2 yield a
demand of 20 and so forth.
assumed demand probabilities. |
|
| We can now generate 1000 random numbers
using the RAND() function in cell b2 and then copying the formula upto
b1001. Using vlookup and the 'data' range we can than generate the
respective demand. Now we can count the different demands individually
using the countif function. The range C2:C1001 has been named as
'NEWDATA'. Then dividing the individual data by 1000 (total number of
random data) we calculate the fraction of our 1000 iterations and you
can observe that the stimulated probabilities match reasonably our
|
|
|
|
|
|
|