Simulations with the RAND() Function

Example: Simulating the use of Electricity

Simulation is the process of developing a model to represent a real-life situation. Well-known examples of simulation include wind tunnels for simulating the movement of objects such as aeroplanes and cars through the atmosphere. Other simulations include models of the weather and the economy, both of which are far larger and more complex than anything we will attempt here.

(The world's largest computer is the Earth Simulator in Japan.)

Think about the use of electricity in your own home. The amount of electricity used at different times of the day might be simulated by estimating the probability (or chance) that a light or appliance might be in use. The probability will change in the course of 24 hours, being low in the middle of the night and high in the early evening.

Let us say that a light uses 100W of electricity and an appliance uses 500W. The chance of one of these items being used in any given hour might be modelled by a random value in the range 1-100:

=TRUNC(RAND()*100)

Let us divide the day into three parts as follows:

• 10% probability: 12.00am to 6.00a.m.
• 20% probability: 6.00a.m. to 5.00p.m.
• 60% probability: 5.00p.m. to 12.00a.m.

(You might choose different periods or values according to what happens in your house; the problem for planners is that they have to work on averages.)

We can now start to develop the spreadsheet.

• Add a heading for each hour in the day (column A)
• Add the probability value for each hour (column B: 10, 20, 60, etc., as above)
• Add a RAND() function to calculate the probability that electricity will be used (column C)
• Add the amounts in Watts consumed by the devices in your house (just two categories, light and appliance, for now)
• Write an IF function to determine whether the light or appliance was used (columns E and G)
• Add totals using SUM to calculate the total amount of electricity consumed and also express this is KWHrs

The IF functions in E3 and G3 are very similar and can be drag-copied into the cells underneath. The IF function does the following:

• looks at cells in the C and B columns to see if the random value is in the probability range for an event in that hour
• if the value in C? is inside the limits specified in B? then the amount of electricity used is specified as the watts used by the light or appliance times the number of houses in the simulation
• if the value in C? is greater than the limit in B? then the amount of electricity used in that hour is 0

Note that the calculation of electricity used (in E3) is $B$2 * $E$2. The $ symbols are used to fix the values used in the formula when drag-copy is applied. The corresponding formula in G3 is $B$2 * $G$2.

Press the Back Button to return