Tuesday, December 13, 2005


Excel is a tool that never ceases to surprise me with its capabilities. Here's a spreadsheet that demonstrates the vlookup() function in an attempt to model and simulate a queueing system. The interarrival time of customers is uniformly distributed between 1 and 8 minutes. The random number is generated with the help of the rand function whose output is uniformly distributed between 0 and 1, meaning that it is equally likely to be anywhere between 0 and 1. We needed a non uniform distribution for service times, where service time is 1 minute with probability 0.1, 2 with probability 0.2, 3 with probability 0.3, 4 with probability 0.25, 5 with probability 0.1, and 6 with probability 0.05. I used a table in excel and the vlookup function. In connection with the above defined distribution of service times, we generate a random variable between 0 and 1 and use vlookup to find the row in that table that this random number lies in. The value from another column from that table in that row is picked. For example, if the random variable is between 0 and 0.1, we pick 1, if it is between 0.11 and 0.3 we pick 2 and so on according to the above described distribution. This is one way of generating non uniformly distributed random numbers in Excel. Check out the sheet and pay attention to the formulae. Never underestimate the power of Excel! That's serious advice.

No comments: