Abstract

You want seven random numbers with different border values to add up to 100 exactly?

sbfixsumfromrandomcorridors

Leave columns A and I in result rows empty so that you can use one formula in cell B5: =MAX(B$2,$B$1-SUM($A5:A5)-SUM(C$3:$I$3))+RAND()*(MIN(B$3,$B$1-SUM($A5:A5)-SUM(C$2:$I$2))-MAX(B$2,$B$1-SUM($A5:A5)-SUM(C$3:$I$3)))

Then you can copy B5 to B5:H14.

Important note: There will be no solution if the sum of the lower borders exceeds 100 or if the sum of the upper borders is less than 100. This is being checked in cells K2:K3 - see download files.

The Distribution of the Random Numbers

The generated random numbers from above example are quite equally distributed.

With 1,048,572 generated rows of 7 numbers each you will get with the original sort order of the border corridors:

sbfixsumfromrandomcorridors_original

If the corridor widths are sorted descending:

sbfixsumfromrandomcorridors_descending

With ascending sort order of the border limit corridor widths you get:

sbfixsumfromrandomcorridors_ascending

To achieve mainly equally distributed random numbers you should therefore sort the columns by ascending corridor widths, because the generating formulas are reducing the degrees of freedom from left to right. If, by any mishap, you are facing descending corridor widths you can expect more extreme distributions.

Using a Triangular Distribution

If you apply the triangular dustribution sbRandTriang you will get for 10,000 rows created:

sbfixsumfromrandomcorridors_triang

The corresponding formula in cell B5 reads: =sbRandTriang(MAX(B$2,$B$1-SUM($A5:A5)-SUM(C$3:$I$3)),MIN(MAX(MAX(B$2,$B$1-SUM($A5:A5)-SUM(C$3:$I$3)),B$2+($B$1-(SUM($A5:A5)+SUM(B$2:$I$2)))/(SUM(B$3:$I$3)-SUM(B$2:$I$2))*(B$3-B$2)),MIN(B$3,$B$1-SUM($A5:A5)-SUM(C$2:$I$2))),MIN(B$3,$B$1-SUM($A5:A5)-SUM(C$2:$I$2))).

Rounded Results

If you require rounded results, you can embed above general formula in =ROUND(…, 2) for 2 digits, for example.

But keep in mind that you need to round at least to the maximal number of digits used in your border values so that

  • all results still reside within corridors after rounding
  • no parts of the corridors become unreachable
  • the target value will always be achieved.

Download

Please read my Disclaimer.

sbfixsumfromrandomcorridors.xlsx [20 KB Excel file, open and use at your own risk]

sbfixsumfromrandomcorridors_diagrams.xlsm [48 KB Excel file, open and use at your own risk]

sbfixsumfromrandomcorridors_triang.xlsm [50 KB Excel file, open and use at your own risk]