Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.04.2024 21:55:37
Preiserhöhung über mehrere Jahre berechnen
17.04.2024 20:19:32
Endzeitpunkt berechnen aus d:hh:mm errechnen
17.04.2024 18:57:33
Errinnerungsfunktion
17.04.2024 16:56:58
Stunden addieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1332to1336
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1332to1336
1332to1336
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Normalverteilung Mittelwert außerhalb 95% C.L.

Normalverteilung Mittelwert außerhalb 95% C.L.
18.10.2013 15:40:23
Michael

Hallo zusammen,
gibt es eine einfache Lösung, daß man den Mittelwert außerhalb des 95% confidence level einfach berechnen, anstatt es immer manuell zu machen? EIn Beispiel habe ich hier wie ich es manuell machen würde (Spalte E18):
https://www.herber.de/bbs/user/87702.xlsx
Ich würde es auch gerne flexibel haben, wenn ich ein anderes condidence level angebe, sollte sich dann der Wert in E18 direkt ändern.
Danke für jede Hilfe.
Viele Grüße
Michael

10
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Normalverteilung Mittelwert außerhalb 95% C.L.
18.10.2013 21:44:40
Bastian
Hallo Michael,
was rechnest Du denn da mit der Funktion QUANTIL in der Normalverteilung?
Die Quantile der Normalverteilung berechnet man in Excel mit NORMINV(hast Du ja auch in Zelle E16 gemacht).
Gruß, Bastian

AW: Normalverteilung Mittelwert außerhalb 95% C.L.
19.10.2013 10:32:52
Michael
Hi Bastian,
der unterschied ist, dass die Quantil-Funktion das Alpha-Quantil für eine Gruppe von Daten zurückgibt usw.
Leider hilft mir das bei meinem Problem nicht.
Danke.
Viele Grüße
Michael

AW: Normalverteilung Mittelwert außerhalb 95% C.L.
19.10.2013 14:35:04
Bastian
Hallo Michael,
ich bin gerne bereit, Dir bei Deinem Problem zu helfen.
Aber ich verstehe nicht, was Du da ausrechnen möchtest.
Du sprichst von einem 95%-Konfidenzintervall, und rechnest mit einem einseitigen Intervall:
In welchem Bereich zwischen -Unendlich und dem gesuchten Quantil liegen 95% der normalverteilten Werte?
Antwort: Zwischen -Unendlich und 1,645*Standardabweichung liegen 95% der normalverteilten Werte.
Bei einem zweiseitigen Intervall, welches symmetrisch zum Mittelwert liegt, würde die Antwort lauten:
Zwischen -1,9599*Standardabweichung und +1,9599*Standardabweichung liegen 95% der Werte.
Jetzt wendest Du die Funktion QUANTIL (welche nichts mit der Normalverteilung zu tun hat) auf die Funktionswerte der Dichtefunktion an (Die Funktionswerte sind nicht die normalverteilten Werte!). Zur Verdeutlichung: Die Funktion QUANTIL macht (so wie Du sie anwendest) folgendes: Nimm alle Funktionswerte der Dichtefunktion, sortiere Sie der Größe nach, sag mir, bis zu welchem Wert 5% aller Werte (in Deinem Fall sind es 121 Werte) liegen. Antwort: 5% von 121 sind 6,05. Also gibt die Funktion QUANTIL den 7. Wert der (sortierten) Funktionswerte zurück. Das ist der Wert 3,514. Aber welche Aussage hat für Dich dieser Wert? Der hat absolut nichts mit dem eingangs erwähnten 95%-Konfidenzintervall zu tun.
Also: Beschreib mal, was genau Du hier ausrechnen möchtest. Dann helfe ich gerne weiter.
Gruß, Bastian

Anzeige
noch offen! oT
19.10.2013 14:38:45
Bastian

AW: noch offen! oT
19.10.2013 15:50:19
Michael
Hi Bastian,
danke für die Antwort. Was ich machen möchte ist den Expected Shortfall zu berechnen (E18), dass aber eher automatisiert und nicht Manuell was ich jetzt machen.
Hoffe es ist klarer?
Danke.
Viele Grüße
Michael

NVT Mittelwert größer 95%
19.10.2013 17:10:03
Beverly
Hi Michael,
das Problem ist, dass man die 95% nicht immer genau auf der X-Achse lokalisieren kann, da der Wert durchaus auch zwischen den aufgelisteten X-Werten liegen kann. Deshalb wäre (aus meiner Sicht) nur eine ungefähre Ermittlung möglich. Dazu könnte man eine Hilfstabelle benutzen, in der mittels VERGLEICH() die benötigten Werte (bzw. deren Zellposition) ermittelt werden:



Tabelle1
 DEFG
15'confidence level0,95'X-Werte für ca. 95%'Y-Werte für ca. 95%
16'Quantil1,644853626951473,13,26681905619982E-03
17'Alpha-Quantil3,51395509482043E-08-3,099999999999973,26681905620023E-03
18'Mittelwert > 95%0,157863870487242   

verwendete Formeln
Zelle Formel Bereich R1C1 für Add In
F15=" X-Werte für ca. "&E15*100&"%"  =" X-Werte für ca. "&RC[-1]*100&"%"
G15=" Y-Werte für ca. "&E15*100&"%"  =" Y-Werte für ca. "&RC[-2]*100&"%"
E16=STANDNORMINV(E15)  =NORMSINV(R[-1]C)
F16=INDEX(A7:A127;VERGLEICH(MAX(B7:B127) *E15/100;B7:B127;1) )  =INDEX(R[-9]C[-5]:R[111]C[-5],MATCH(MAX(R[-9]C[-4]:R[111]C[-4]) *R[-1]C[-1]/100,R[-9]C[-4]:R[111]C[-4],1) )
G16=INDEX(B7:B127;VERGLEICH(MAX(B7:B127) *E15/100;B7:B127;1) )  =INDEX(R[-9]C[-5]:R[111]C[-5],MATCH(MAX(R[-9]C[-5]:R[111]C[-5]) *R[-1]C[-2]/100,R[-9]C[-5]:R[111]C[-5],1) )
E17=QUANTIL(B7:B127;1-E15)  =PERCENTILE(R[-10]C[-3]:R[110]C[-3],1-R[-2]C)
F17=INDEX(A7:A127;VERGLEICH(F16*-1;A7:A127;-1) )  =INDEX(R[-10]C[-5]:R[110]C[-5],MATCH(R[-1]C*-1,R[-10]C[-5]:R[110]C[-5],-1) )
G17=INDEX(B7:B127;VERGLEICH(F17;A7:A127;0) )  =INDEX(R[-10]C[-5]:R[110]C[-5],MATCH(RC[-1],R[-10]C[-6]:R[110]C[-6],0) )
E18=MITTELWERT(INDIREKT("B"&VERGLEICH(F16;A7:A127;0) &":B"&VERGLEICH(F17;A7:A127;0) ) )  =AVERAGE(INDIRECT("B"&MATCH(R[-2]C[1],R[-11]C[-4]:R[109]C[-4],0) &":B"&MATCH(R[-1]C[1],R[-11]C[-4]:R[109]C[-4],0) ) )

http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip
http://hajo-excel.de/tools.htm
XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007
Add-In-Version 12.02 einschl 64 Bit

Ob diese Lösung deinen Vorstellungen entspricht, kann ich natürlich nicht bewerten.

Anzeige
Expected Shortfall bei Normalverteilung
19.10.2013 20:40:50
Bastian
Hallo Michael,
musste mich erst ein bisschen belesen.
Anbei eine Möglichkeit, wie Du den Expected Shortfall bei Normalverteilung mit Excel berechnen kannst:
 ABC
1Expected Shortfall und Normalverteilung:  
2   
3my0 
4Sigma1 
5Wahrscheinlichkeit95% 
6ES(0,95%)2,06271281*Sigma

Formeln der Tabelle
ZelleFormel
A6="ES("&B5&"%)"
B6=NORMVERT(NORMINV(B5;B3;B4); B3;B4;FALSCH)/(1-B5)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Bei 95% Wahrscheinlichkeit liegt der Expected Shortfall ca. 2,1 * Standardabweichung.
Bei 99% ergibt sich ein Expected Shortfall ca. 2,7 * Standardabweichung.
Quellen: http://www.bwl.uni-wuerzburg.de/fileadmin/wifak/_temp_/Foliensatz______w__rzburg_SS07.pdf, S.13
sowie http://www.mi.uni-koeln.de/~jeisenbe/Vortrag1.pdf, S.11
Ich lasse den Thread trotzdem mal offen.
Gruß, Bastian

Anzeige
AW: Expected Shortfall bei Normalverteilung
20.10.2013 12:13:05
Bastian
Hallo Michael,
hab noch zwei interessante YouTube Videos zu dem Thema gefunden. Die dürften für Dich auch interessant sein:
http://www.youtube.com/watch?v=eHGJFOjyzr4
http://www.youtube.com/watch?v=ykCEWHRmrlI
Hier noch eine Tabelle, die VaR und ES für unterschiedliche Confidence-Level aufzeigt:
 ABCD
1Expected Shortfall und Value at Risk bei Normalverteilung:   
2    
3my0  
4Sigma1  
5    
6CalphaVaRES
750%50,00%0,0000,798
890%10,00%1,2821,755
995%5,00%1,6452,063
1097,50%2,50%1,9602,338
1199%1,00%2,3262,665
1299,90%0,10%3,0903,367
1399,99%0,01%3,7193,958

Formeln der Tabelle
ZelleFormel
B7=1-A7
C7=NORMINV(A7;$B$3;$B$4)
D7=NORMVERT(NORMINV($A7;$B$3;$B$4); $B$3;$B$4;FALSCH)/(1-$A7)


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß, Bastian

Anzeige
AW: Expected Shortfall bei Normalverteilung
20.10.2013 14:52:36
Michael
Hi Bastian,
vielen Dank für die Beispiele. Wenn man einfach eine Verteilung hat die nicht normalverteilt ist, wie würde ich den ES dann berechnen in Excel? Sagen wir mal wir würden uns den ES für ein Confidence Level von 99% anschauen, dann würde ich mir den Mittelwert aller Werte nach dem 99% Quantil anschauen und das wäre mein ES. Wie würde das dann laufen?
Danke nochmals.
Viele Grüße
Michael

AW: Expected Shortfall bei Normalverteilung
20.10.2013 20:29:21
Bastian
Hallo Michael,
um Deine Frage zu beantworten habe ich in Spalte A 10.000 normalverteilte Zufallszahlen (Mittelwert=0, Standardabweichung =1) erzeugt (mit der Formel "=NORMINV(ZUFALLSZAHL();0;1)"). Ich habe normalverteilte Zufallszahlen genommen, weil wir für diesen Fall das Ergebnis des ES schon kennen: für einen CL von 99% in etwa 2,665. Die Methode kann aber (wie von Dir gewünscht) auf nicht normal verteilte Werte angewendet werden.
Bei der Formel, die ich angewendet habe, brauchen die Werte nicht der Größe nach sortiert werden.
Wenn Dich die 1% kleinsten Werte interessieren, kannst Du KGRÖSSTE durch KKLEINSTE ersetzen:
 ABCD
1-0,1162986 C99%
20,12388749 alpha1%
3-0,3616935 n10000
4-0,0072462 1% von n100
51,43623174 ES2,60698554
61,32103103   
70,04244152   
80,95928017   
90,09980548   
10-0,5713861   

Formeln der Tabelle
ZelleFormel
A1=NORMINV(ZUFALLSZAHL(); 0;1)
D2=1-D1
D3=ANZAHL(A1:A10000)
D4=D3*D2
D5{=MITTELWERT(KGRÖSSTE($A$1:$A$10000;ZEILE(INDIREKT("A1:A"&$D$4))))}
Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!
Matrix verstehen


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Der so ermittelte ES von 2,607 stimmt also gut mit dem genauen Wert bei Normalverteilung von 2,665 überein.
Gruß, Bastian
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige