Fictional business - Random Suppliers in Excel

This blog resumes creating a fictional business focusing on about suppliers.

We begin by creating a small suppliers list using Excels random number function.

What is a random number?
Computers are predictable in their computation power as it is possible to select a different number at a certain time. The number is always between 0 and 1. Wikipedia (2001-present) [accessed 25 April 2012] has a good overview about Random Number Generators.

Excel has two in-built functions called RAND and RANDBETWEEN as both function returns a number between 0 and 1 to many decimal places after a decimal point.

Take note that each time there is a change in a cell or formula. Excel will by default update all calculations automatically. For functions like RAND and RANDBETWEEN is calculated with a new number.

If the random result appears suitable for business data, we simply copy or cut and paste the values in another cell or range of cells in order to preserve the data.

Why do we require the random?
To generate a fictional data for business, we need the computer to select different numbers for a range of items like company supplier names and eventually select supplier’s goods and prices. In essence, the computer will select a supplier name from a database.

How do I get a random number?
The picture attached shows when we enter “=RAND()” formula in Excel. We need the brackets as the results returning to a cell contains no extra information provided.

Thus, to generate a number between 1 and 10, we need to change “=RAND()” as “=INT(RAND()*10)+1)”. The use of INT is another formula to return an integer that the decimal point is not expressed.

On the other hand, =RANDBETWEEN(Bottom,Top) needs information about the lowest number namely Bottom and the highest number Top. The picture below shows the difference between these two functions.
This can be simplified by using =RANDBETWEEN(1,10) which produces the same results as =INT(RAND()*10+1) does.

How to make a random list of supplier names?
To achieve this, we need a list that expected not to change when the RAND() function is called. The small list here is shown as an example.

The next picture introduces another column called Result.

I use the VLOOKUP formula (short for vertical lookup) to ask Excel return the supplier name from a range of cells. The information for the formula is as follows. VLOOKUP(,,,).

Thus, when combining both the random information above with the VLOOKUP, the formula to enter becomes =VLOOKUP(RANDBETWEEN(1,5),$A$3:$B$8,2,FALSE)

The $ dollar signs are important in the formula to keep the range of cells the same should we drag the list down like the next picture shows. It is a matter of personal choice on how large the list can go.

The next blog will apply these principles into a VBA module.

Til then, Peter.