Excel Problem - Zufallszahlen sollen feste Summe ergeben

[CyberGuard]

Hallo Leute,
ich schlage mich jetzt einen gefühlte Ewigkeit mit folgendem Problem herum.
Ich möchte 8 zufällig generierte Zahlen im Bereich zwischen 2,5 und 8 erzeugen und das in Abstufungen von 0,25.
Am Ende sollen die 8 Zahlen immer die Summe 45 ergeben.

Den ersten Teil habe ich hinbekommen in dem ich folgende Formel nutze:

=RUNDEN(ZUFALLSBEREICH(2500;8000)/1000/0,25;0)*0,25

Soweit so gut nur wie bekomme ich es jetzt hin das Excel die 8 Zahlen so generiert das am Ende immer genau 45 raus kommt?

Wie ich es auch Versuche ich krieg es nicht hin.
Hat vielleicht von euch jemand eine Idee?
Geht das überhaupt?

Vielen Dank im voraus für eure Hilfe.

 

[flotus1]

Interessantes Problem.
Wenn dich Korrelationen zwischen den Zufallszahlen nicht stören könntest du folgenden Ansatz (Ansatz, keine fertige Lösung ) probieren:
Die erste Zufallszahl wird anhand deiner Formel erzeugt. Für die weiteren Zufallszahlen hat die Summe der bisher erzeugten Zufallszahlen Einfluss auf den Zufallsbereich. Ganz grob: liegt die Summe über dem Durchschnitt wird der das Maximum niedriger, liegt die Summe unter dem Durchschnitt wird das Minimum höher. Die letzte Zahl ist dann nur noch die Differenz zwischen 45 und der Summe der ersten 7 Zahlen.
Dass die Zufallszahlen mit so einer Methode noch gleichverteilt sind glaube ich kaum. Ein anderer Ansatz wäre eine Brute-Force Methode bei der so lange Sequenzen von 8 Zahlen erzeugt werden bis die Summe passt. Das wäre dann eine Gleichverteilung ohne Korrelationen, aber das in Excel zu pressen ist definitiv nur für Fortgeschrittene.

 

[Silvergray]

Der Brute Force Ansatz wäre effektiv, weil die Wahrscheinlichkeit für einen Erfolg nicht klein ist. Wenn du die letzte Zahl aus der Differenz bildest, ist die Erfolgswahrscheinlichkeit hoch. Ich halte diesen Trick für legitim.
Es ist eine Weile her, dass ich Angewandte Kryptographie von Bruce Schneier gelesen habe. Eine Zufallszahl lässt sich immer vorhersagen, wenn alle anderen bekannt sind, egal, ob sie gewürfelt ist, oder durch Differenzbildung entsteht. Würfeln würdest du ja so lange, bis zufällig die Differenz zu 45 herauskommt. Das sollte keinen Unterschied ergeben. Die Summe ist immer 45 und die Zahl damit vorhersagbar.

Wie man es in Excel umsetzt, weiß ich nicht. Per Script geht das sicher einfach. Per Formel kenne ch keinen einfachen Weg mit unbegrenzt vielen Versuchen. Ich bin allerdings ein Excel-Hasser mit begrenzter Erfahrung.

Funktionieren sollte der folgende Ansatz mit begrenzt vielen Versuchen. Es könnte also sein, dass du kein Ergebnis erhältst und mit neuem Seed von vorn beginnen musst. Du kannst die Zahl der Versuche hoch ansetzen um die Wahrscheinlichkeit zu senken. Null wird sie aber nie.

1. Erzeuge sieben Zufallszahlen in sieben Feldern einer Spalte.
2. Erzeuge die achte Zahl durch die Differenz der Summe der sieben Felder zur Zielsumme. Nun hast du eine Spalte gefüllt. Sie könnte ungültig sein, wenn die letzte Zahl außerhalb des Bereiches liegt
3. Wiederhole beide Schritte in der Nachbarspalte, aber mit einer Wenn-/If-Bedingung: Liegt die achte Zahl der Vorgängerspalte im gültigen Bereich, dann kopiere die Werte aller Felder aus den Feldern der Vorgängerspalte. Falls sie ungültig ist, dann erzeuge neue Zufallszahlen wie in 1.
4. Wiederhole Punkt 3 sehr oft, bis die Wahrscheinlichkeit, keinen Erfolg zu haben, klein genug ist. Das kannst du ausrechnen.

 

[flotus1]

Ein kleines Problem hat übrigens schon dein Ansatz.
Wenn Menschen ohne Vorbelastung von "Zufallszahlen" reden meinen sie damit in der Regel eine Gleichverteilung. Das bedeutet in deinem Beispiel dass jede Zahl aus dem gewählten Zahlenbereich die gleiche Wahrscheinlichkeithat erzeugt zu werden. So wie du deine Zufallszahlen jetzt erzeugst sind aber die Extremwerte (hier 2,5 und 8) nur halb so wahrscheinlich wie die anderen Werte dazwischen. Willst du das nicht mach folgendes:

Ein bisschen Nomenklatur:
min=2.5
max=8
diff=max-min
dx=0.25

Die Zufallszahlen erzeugst du dann mit folgender Formel:
=Abrunden((Zufallszahl()*(diff+dx)+min)/dx,0)*dx