Generate N random numbers between A and B, sum up to X with special condition

[johnmpl]

Hi to all!


I bring you a challenge to do without the help of macros or Excel tools (like Solver). The idea is to use only Excel formulation, and, if possible, use the same formulation for the entire matrix.


What is needed is to be able to generate a square matrix n x n that results in sum of each of its columns some specific values ​​defined by a given resultant vector.


There are some conditions (restrictions) that must be met:


1. Each value of the matrix must be between 0 and 1, including the extremes (it can be 0 and / or 1 the result).
2. The values ​​of the main diagonal must always be 0.
f1c1, f2c2, ..., fncn = 0
3. The sum of the opposite values ​​between row and column must always be 1. For example
f2c1 + f1c2 = 1
f3c1 + f1c3 = 1
...
f3c2 + f2c3 = 1
f4c2 + f2c4 = 1
...
and so on.
4. Values ​​should not be negative (clearly this condition is only for clarity, due to condition 1 that includes it).


Possibly several solutions are found to the problem, but just one solution is needed that satisfies all the given conditions. The example given below satisfies all restrictions.

n = 7

[TABLE="width: 378"]
<colgroup><col span="7"></colgroup><tbody>[TR]
[TD]0,0[/TD]
[TD]0,3[/TD]
[TD]0,3[/TD]
[TD]0,3[/TD]
[TD]0,4[/TD]
[TD]0,0[/TD]
[TD]0,1[/TD]
[/TR]
[TR]
[TD]0,7[/TD]
[TD]0,0[/TD]
[TD]0,5[/TD]
[TD]0,5[/TD]
[TD]0,6[/TD]
[TD]0,1[/TD]
[TD]0,2[/TD]
[/TR]
[TR]
[TD]0,7[/TD]
[TD]0,5[/TD]
[TD]0,0[/TD]
[TD]0,5[/TD]
[TD]0,6[/TD]
[TD]0,1[/TD]
[TD]0,2[/TD]
[/TR]
[TR]
[TD]0,7[/TD]
[TD]0,5[/TD]
[TD]0,5[/TD]
[TD]0,0[/TD]
[TD]0,4[/TD]
[TD]0,2[/TD]
[TD]0,3[/TD]
[/TR]
[TR]
[TD]0,6[/TD]
[TD]0,4[/TD]
[TD]0,4[/TD]
[TD]0,6[/TD]
[TD]0,0[/TD]
[TD]0,0[/TD]
[TD]0,1[/TD]
[/TR]
[TR]
[TD]1,0[/TD]
[TD]0,9[/TD]
[TD]0,9[/TD]
[TD]0,8[/TD]
[TD]1,0[/TD]
[TD]0,0[/TD]
[TD]0,7[/TD]
[/TR]
[TR]
[TD]0,9[/TD]
[TD]0,8[/TD]
[TD]0,8[/TD]
[TD]0,7[/TD]
[TD]0,9[/TD]
[TD]0,3[/TD]
[TD]0,0[/TD]
[/TR]
</tbody>[/TABLE]

Given Vector:
[TABLE="width: 378"]
<tbody>[TR]
[TD="class: xl65, width: 54"]4,6[/TD]
[TD="class: xl65, width: 54"]3,4[/TD]
[TD="class: xl65, width: 54"]3,4[/TD]
[TD="class: xl65, width: 54"]3,4[/TD]
[TD="class: xl65, width: 54"]3,9[/TD]
[TD="class: xl65, width: 54"]0,7[/TD]
[TD="class: xl65, width: 54"]1,6[/TD]
[/TR]
</tbody>[/TABLE]


Any help will be appreciated. Blessings!

 

[gsbelbin]

Maybe I'm missing something but surely all you need to do is define your main diagonal as zeroes then enter =RAND() in all the cells below the main diagonal. Then in each of the cells above the main diagonal just enter =1-<the cell="" corresponding="">(the corresponding cell) eg in B1 enter =1-A2</the>

 

[johnmpl]

Thx gsbelbin for your answer

No...

I know that RAND() function give a random number between 0 and 1... but this numbers must give a specified sum by column, depends on the given vector:

In the above example:
0.0 + 0.7 + 0.7 + 0.7 + 0.6 + 1.0 + 0.9 = 4.6
0.3 + 0.0 + 0.5 + 0.5 + 0.4 + 0.9 + 0.8 = 3.4
... and so on.

And the random numbers must have some conditions:

1. Each value of the matrix must be between 0 and 1, including the extremes (it can be 0 and / or 1 the result).
2. The values ​​of the main diagonal must always be 0.
f1c1, f2c2, ..., fncn = 0
3. The sum of the opposite values ​​between row and column must always be 1. For example
f2c1 + f1c2 = 1
f3c1 + f1c3 = 1
...
f3c2 + f2c3 = 1
f4c2 + f2c4 = 1
...
and so on.
4. Values ​​should not be negative (clearly this condition is only for clarity, due to condition 1 that includes it).

Blessings!

 

[rlv01]

Where does "random" factor into this? It seems like your four conditions would mean the matrix values must by definition be non-random.

 

[johnmpl]

Hi, rlv01! thx for your answer.

No... there is no factor. Is "random" because you could get any value between 0 and 1 (with several decimals, if you needed). But is "random with conditions", because you have to take care about some conditions explained above. There could be multiple answer, but just with one is enough. Blessings!