Poisson - Summe Ergebnisse z.B. Unter 3.5 Tore

[XRipperBet]

Gute Morgen Excel-Freunde,

ich möchte wissen, ob jemand eine Idee hat wie ich folgenden Schritt in Abhängigkeit eines Werts automatisieren kann:

Mit Hilfe von Poisson wurde in der linken Tabelle (XRipperBet: Poisson) berechnet, wie wahrscheinlich es ist, dass TH (Heimmannschaft) gegen TA (Auswärtsmannschaft) 0,1,2 ... Tore im Spiel erzielt und umgekehrt, wie wahrscheinlich es ist, dass TA (Auswärtsmannschaft) gegen TH (Heimmannschaft) 0,1,2 ... Tore erzielt. z.B. ist die Wahrscheinlichkeit, dass die Heimmannschaft in diesem Spiel 2 Tore erzielt bei 26,69% (siehe Bild).

Mit Hilfe der % Werte aus der linken Tabelle (XRipperBet: Poisson) wurde in der rechten Tabelle (XRipperBet: Ergebnisse) die Wahrscheinlichkeit berechnet, dass ein bestimmtes Ergebnis eintrifft. In diesem Spiel liegt z.B. die Wahrscheinlichkeit, dass die Auswärtsmannschaft 3:0 gewinnt bei 0,31% (siehe Bild).

Anhand dieser Daten kann man berechnen wie wahrscheinlich es ist, dass ein bestimmtes Ereignis im Spiel eintrift. Wenn ich z.B. wissen möchte, wie hoch die Wahrscheinlichkeit ist, dass in dieser Partie weniger als 4 Tore fallen, muss ich die Wahrscheinlichkeit aller Ergebnisse (siehe Bild) summieren in denen weniger als 4 Tore fallen. In meinem Beispiel ist die Wahrscheinlichkeit, dass weniger als 4 Tore fallen 65,73%.

Jetzt zu meinem Problem. Ich möchte z.B. in Feld X die Zahl 3.5 eintragen und in Feld Y die % Wahrscheinlichkeit für Über 3.5 Tore und in Feld Z die % Wahrscheinlichkeit für Unter 3.5 Tore erhalten.

Im Fussball kann man mit etwas Aufwand sich manuell eine Tabelle erstellen wo die Formeln für mehrere Ereignisse (z.B. Über/Unter 0.5, 1.5, 2.5 Tore usw.) manuell eingetragen werden. Wenn man aber die Wahrscheinlichkeit für die Punkteanzahl im Basketball berechnen will, müsste man wahrscheinlich mehrere Tage investieren um sich eine manuelle Tabelle mit den X Möglichkeiten zu erstellen. Außerdem wäre das Risiko von Fehlern sehr hoch.

Ich hoffe einigermaßen verständlich mein anliegen vorgetragen zu haben. Mir fehlt leider jegliche Idee wie man das realisieren kann, wenn es denn überhaupt möglich ist.

Ich bedanke mich schon mal im Voraus bei allen die diesen Beitrag lesen und vielleicht eine Idee haben die mir weiterhelfen könnte.


Liebe Grüße,

XRipperBet

[Bamberg]

Servus XRipperBet,

eine schlaue Formel fällt mir dazu nicht ein, daher eine Lösung mit einer "eigenen Formel".

In ein Modul kopieren:

Code:

Option Explicit
Public Function MeineSumme(ByVal Limit As Double, _
                           ByVal Bereich As Range, _
                           Optional ByVal KleinerAls As Boolean = True) As Double
    Dim Arr As Variant
    Dim x As Long, y As Long
   
    Arr = Bereich.Value
   
    For x = LBound(Arr, 2) To UBound(Arr, 2)
        For y = LBound(Arr, 1) To UBound(Arr, 1)
            If x <> LBound(Arr, 2) And y <> LBound(Arr, 1) Then
                If KleinerAls Then
                    If Arr(1, x) + Arr(y, 1) < Limit Then MeineSumme = MeineSumme + Arr(y, x)
                Else
                    If Arr(1, x) + Arr(y, 1) > Limit Then MeineSumme = MeineSumme + Arr(y, x)
                End If
            End If
        Next y
    Next x
End Function

... und im Tabellenblatt kannst Du die Formel so verwenden ...

Arbeitsblatt mit dem Namen 'Tabelle1'
	A	B	C	D	E	F
1		0	1	2	3	4
2	0	2%	3%	15%	6%	2%
3	1	3%	8%	6%	5%	2%
4	2	4%	10%	3%	4%	1%
5	3	2%	5%	4%	1%	0%
6	4	3%	4%	6%	1%	0%
7
8
9		Grenzwert	Kleiner als	Größer als
10		3,5	59%	41%

Zelle	Formel
C10	=MeineSumme(B10;A1:F6;WAHR)
D10	=MeineSumme(B10;A1:F6;FALSCH)

Diese Tabelle wurde mit Tab2Html (v2.5.0) erstellt. ©Gerd alias Bamberg

LG Gerd

[neopa]

Hallo,

alternativ zu Gerds Vorschlag, geht es auch ohne VBA mit mit einer Matrixfunktion(alität)sformel (diese benötigt keines spez. Formelabschluss wie eine klassische Matrixformel).
In meiner unten stehenden Formellösung werden Daten bis Zeile 12 ausgewertet. Wenn eine größere Datenmatrix auszuwerten ist  braucht man lediglich in der Formel die 12 durch die max auszuwertende Datenzeile zu ersetzen.  Dir Formeln können natürlich auch in ein anderes Tabellenblatt verschoben werden.

	A	B	C	D	E	F	G
1		0	1	2	3	4
2	0	2%	3%	15%	6%	2%
3	1	3%	8%	6%	5%	2%
4	2	4%	10%	3%	4%	1%
5	3	2%	5%	4%	1%	0%
6	4	3%	4%	6%	1%	0%
7
8
9
10
11
12
13	Wert	< als	> als
14	3,5	59%	41%
15

Formeln der Tabelle

Zelle Formel B14 =SUMME(INDEX(B2:INDEX(1:12;2+MAX(1:1);2+MAX(1:1))*(SPALTE(B2:INDEX(1:12;2+MAX(1:1);2+MAX(1:1)))-1<(1+VERGLEICH(A14;1:1)-ZEILE(B2:INDEX(1:12;2+MAX(1:1);2+MAX(1:1)))+1));)) C14 =1-B14

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4

[Bamberg]

Hallo Werner,

super Formel :)

LG Gerd

[AnneWaterkant]

Hallo,

wenn ich das richtig verstanden habe, reicht doch 

=SUMMENPRODUKT(B2:F6;N(SPALTE(B2:F6)+ZEILE(B2:F6)<4+A14)) 

in B14 aus, oder?

[XRipperBet]

Hallo an Alle!

Da kommt man gerade von Freunden zurück nach Hause und wird positiv überrascht! :)

Sehr geile Lösungsansätze von Allen! Vielen Dank dafür! Ich werde mich morgen früh sofort reinhängen und es genauer ausprobieren und dann mitteilen welche Lösung in meinem Fall die sinvollste war! :)

Gute Nacht an Alle!

Liebe Grüße,

XRipperBet

[neopa]

Hallo Anne,

... im Prinzip hast Du schon Recht. Danke für Deinen Beitrag.

Allerdings ist in Deiner Formel noch ein kleiner aber teils entscheidender Fehler, der sich jedoch leicht beheben lässt. Anstelle der Prüfung auf kleiner, ist die Prüfung auf kleiner gleich notwendig.  Die Funktion N() hättest Du Dir auch sparen können, wenn Du multipliziert hättest.

Ich nutze nur anstelle SUMMENPRODUKT() oft (und auch hier) das Formelkonstrukt SUMME(INDEX()) und die Formel würde dann bei mir dann wie unten stehend aussehen:

Das was ich gestern in meine Formel zusätzlich noch eingebaut hatte, sollte ursprünglich lediglich dazu dienen, die Formel "dynamisch" an die Größe der Datenmatrix anzupassen. Dies war wohl mehr Spielerei, denn sie ist nicht wirklich notwendig.

Und da war ich mit der dem Ausgabefehler meines Forumbeitrages zu sehr beschäftigt, als dass ich darauf noch hingewiesen hätte.

Code:

=SUMME(INDEX(B2:F6*(SPALTE(B2:F6)+ZEILE(B2:F6)<=4+A14);))

[AnneWaterkant]

Hallo Werner,

da kann man sich möglicherweise streiten. Denn wer 3,5 Tore für möglich hält, tut dies aus Sicherheitsgründen, um dem Streit "< oder <=" von vornherein aus dem Weg zu gehen. Nicht etwa deshalb, weil tatsächlich eine halbe Kugel ins Tor trudelt. ;) Einen anderen Grund sehe ich nicht. Vielleicht hilft der TE?

SUMMENPRODUKT ist den meisten geläufig.

Semikolon soll im SUMMENPRODUKT schneller rechnen, als *. Zumindest, wenn nicht dafür umgewandelt werden muss, wie hier mit N(). Daran habe ich mich gehalten, aber:

SUMME(INDEX ist (hier) genauso schnell.

Und "dynamische Ausdehnung" betrifft jede Formel dieser Welt. Nicht nur diese. Daher wundert mich das Argument.

Viele Grüße!

P.S.: Rechner völlig runtergefahren? Antivirus oder Defender aktiv? Nicht, dass Du Dir immer wieder neu etwas einfängst, beim Start von CEF. Dort einmal komplett ausgeloggt? Kontrolliert, ob entweder beim TE, einem anderen unserer Beiträge oder bei Dir ein Steuerzeichen von vorher eingeschleift ist, welches Deine Länderliste erzeugt?

[WillWissen]

gelöscht, da erledigt