Generation of four random weights in Excel

Hello!

Following my first Excel related post (Creating automatically series of repeated sequential numbers in Excel - Link), below you can find the solution to a different challenge that we faced while working in one of our research projects. The post explains how to generate four random weights in Excel, based on specific limitations.

Our example explains the method used in order to automatically generate X times (in our case X was equal to 1000), a set of four weights under the following specifications:

- Each weight has an original value: 

- The sum of the four weights (original & random) should equal to one: 

- Each weight can have a variation of ±0.1 from its original value (±10% of the scale).

An example of the original values and the minimum and maximum values of each weight is given at the following two tables. We are only interested in the set of 4 weights that are presented on the 2nd line of each table.

A table containing the values (original, low and high) of each weight should be present in a table in the excel file so that the user can call those values in the formulas, when necessary. Avoid including specific values in a formula, as it will limit your flexibility of doing changes that are updated automatically everywhere.

In order to randomly choose a number in a specified interval (e.g. (a,b) ) in Excel, all we need to do is to use this formula in a cell: =rand()*(b-a)+a



1. Initially 

are randomly picked X times in the designated interval, following a uniform distribution.

2. We define the change in

 

3. Due to the restriction for the sum of weights (1), the sum of the changes in weights should be equal to zero. Thus:

4. Due to the restriction of the intervals that the weights should be placed, we know that:

5. From (2), (3) and (4) 

 and therefore:

All terms are known and by solving for

we obtain:

6. By the definition of our project, we know that

7.  At this point, we need to check if the interval of

should be modified to follow our restrictions. This is simply done by creating the intersection of the intervals given by (5) and (6).

8. We randomly pick 

X times in the modified interval that we calculated in step 7 above, following a uniform distribution.

9. From (1) we calculate 

 by subtracting from the unit

and thus: 

In this way, we are able to calculate the four weights for the purposes that we need them. The implementation in Excel is extremely easy:

- Weights w1 and w2 are calculated based on the formula: =rand()*(b-a)+a

- For weight w3, there could be various implementations. The one I used was:

a. To calculate the values w3(low_b) and w3(high_b) provided by formula (5) in two columns

b. To use the formula:
=RAND()*(MIN(w3high;w3high_b)-MAX(w3low;w3low_b))+MAX(w3low;w3low_b)

- Weight w4 is simply calculated based on the formula: =1-w1-w2-w3