Uniform Random Integer Distribution
FSintegerinv(First, Second, Optional Random Number)
First is lower integer and Second is the upper integer of the range. The probability, which usually is the random input is optional. If none is supplied the function will generate a random probability and return a value between the two limits inclusively. This function will simulate integer values (whole numbers) between any two numbers. It is very similar to the built-in Excel Randbetween function, the added feature in FSintegerinv is that it will accept a probability as input to generate the value. This is an advantage in that you can repeat the cycle. This normally used when you want to repeat a simulation with different parameter inputs (see FSRandseq).
Here (Table 1) is the coin toss example again using FSintegerinv function (the green values below the cells show the formula used).
Panel 1 shows the coin toss using FSintegerinv(0,1) to generate either a 0 or 1 with equal/uniform probability, since the value is a 1 it would be a "head". Panel 2 shows the shows the function being used with optional random number, which in this case is FSrandseq(834) which would allow the us to repeat the sequence of random numbers if necessary. Panel 3 shows a formatting trick. The actual result is a 0, we know this since it uses the same random number as panel 2, but it is formatted to show the word "Tail." As far as Excel is concerned the underlying result is a number and we could treat it as such. So it is possible to add, subtract, multiply etc. the result. FinPak would also treat this a number if you were to use it in a simulation.
One more game of chance could be simulated using the this function. A game of craps could be simulated with the toss of two dice. Here is the setup:
Here is the result from 20,000 tosses of the pair of dice.
Was this a reasonable result? Here is a comparison of the actual outcomes and the expected outcomes.
It matches very well and a Chi-square test passes at all reasonable confidence levels as shown below 1.
The FSinteger can be used to simulate non-integer uniform numbers.Suppose you believe that over the life of the project that you will face somewhat uncertain tax rates. You believe there are chances that the tax will be between 35 and 45 percent and that all numbers over the range are equally likely. To simulate this you could use: FSintegerinv(35,45)/100. Notice that this will produce 2 decimal places. Here is the distribution of simulated tax rates. The average simulated tax, given this distribution is 39.86%. You could get three digit numbers by using: FSintegerinv(350,450)/1000. Thus this function is very flexible.
1 These types of test are used to insure that the FinPak routines return the correct distributions.
Copyright © 2009 Pieter Vandenberg