Abstract

Sie benötigen sieben Zufallszahlen aus verschiedenen Zahlenbereichen, die zusammen genau 100 ergeben?

sbfixsumfromrandomcorridors

sbfixsumfromrandomcorridors_formulas

Wichtiger Hinweis: Es existiert keine Lösung, wenn die Summe der Untergrenzen größer als 100 ist oder wenn die Summe der Obergrenzen kleiner ist als 100! Dies wird in den Zellen K2:K3 geprüft.

Die Verteilung der Zufallszahlen

Die erzeugten Zufallszahlen im obigen Beispiel sind relativ gleichverteilt.

Bei 1.048.572 erzeugten Reihen von jeweils 7 Zahlen erhält man für die originale Sortierung der Grenzwert-Korridore:

sbfixsumfromrandomcorridors_original

Bei absteigender Sortierung der Grenzwerte nach Korridorbreite erhält man:

sbfixsumfromrandomcorridors_absteigend

Bei aufsteigender Sortierung nach Korridorbreite:

sbfixsumfromrandomcorridors_aufsteigend

Um möglichst gleichverteilte Zufallszahlen zu erhalten, sollte man deshalb die Spalten nach aufsteigenden Korridorbreiten sortieren, weil die erzeugenden Formeln die Freiheitsgrade von links nach rechts reduzieren. Falls Sie - aus welchen Gründen auch immer - absteigend sortierte Grenzwertbereiche vorliegen haben, müssen Sie mit deutlich extremeren Verteilungen rechnen.

Mit einer Dreiecksverteilung

Mit der Dreiecksverteilung sbRandTriang erhält man bei 10.000 erzeugten Reihen:

sbfixsumfromrandomcorridors_triang

Die entsprechende Formel in Zelle B5 lautet: =sbRandTriang(MAX(B$2;$B$1-SUMME($A5:A5)-SUMME(C$3:$I$3));MIN(MAX(MAX(B$2;$B$1-SUMME($A5:A5)-SUMME(C$3:$I$3));B$2+($B$1-(SUMME($A5:A5)+SUMME(B$2:$I$2)))/(SUMME(B$3:$I$3)-SUMME(B$2:$I$2))*(B$3-B$2));MIN(B$3;$B$1-SUMME($A5:A5)-SUMME(C$2:$I$2)));MIN(B$3;$B$1-SUMME($A5:A5)-SUMME(C$2:$I$2))).

Gerundete Ergebnisse

Müssen die Ergebnisse auf eine bestimmte Anzahl von Nachkommastellen gerundet werden, dann kann die oben genannte allgemeine Formel z. B. für 2 Nachkommastellen einfach in =RUNDEN(…; 2) eingebettet werden.

Wichtig ist nur, dass mindestens auf die maximale Anzahl der in den Grenzwerten verwendeten Nachkommastellen gerundet wird, damit

  • die Ergebnisse nach Rundung noch im Korridorbereich sind
  • nicht Teile des Korridorbereiches durch die Rundung unerreichbar werden
  • das Zielergebnis immer erreicht wird.

Download

Bitte den Haftungsausschluss im Impressum beachten.

sbfixsumfromrandomcorridors.xlsx [20 KB Excel Datei, Download und Nutzung auf eigene Gefahr]

sbfixsumfromrandomcorridors_diagrams.xlsm [48 KB Excel Datei, Download und Nutzung auf eigene Gefahr]

sbfixsumfromrandomcorridors_triang.xlsm [50 KB Excel Datei, Download und Nutzung auf eigene Gefahr]

Hinweis: Eine umfassende Dokumentation meiner Excel Implementierungen finden Sie in Excel VBA Eine Sammlung