Live-Forum - Die aktuellen Beiträge
Datum
Titel
13.10.2025 19:31:31
VBA Formel mit Bezug auf Überschrift anstatt Spalte
13.10.2025 15:10:17
Überlappende Zeiträume über mehrere Tabellen mit leeren Zell
13.10.2025 13:34:56
PQ Abfrage mit verschiedenen Werten ausführen + Protokoll
13.10.2025 12:24:02
Zählen in Abhängigkeit einer Jahreszahl
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
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 Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Normalverteilung Mittelwert außerhalb 95% C.L.

Forumthread: 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

Anzeige

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

Anzeige
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

Anzeige
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
;

Forumthreads zu verwandten Themen

bedingte formatierung berechnen lassen unter vba
morbiuz

Hallo,
ich habe eine Spalte A auf der 2 verschiedene bedingte Formatierungen gesetzt sind (rot, grün) für die Scriftfarbe
leider kann ich die Werte nicht über meine Funktion zusammenrechnen lassen:
Sub mtest() Dim zelle As Range Dim bereich As Range Dim a As Integer Set bereich =...
20.08.2008 03:03:00
Thread-ID: 1003056
2 Beiträge
Anzeige
Berechnen von Werten in einer Spalte
Werten

Folgendes Problem:
In der Spalte A möchte ich wissen, wieviel unterschiedliche Werte es gibt.
Wer kann mir helfen.
Danke
Alex
05.10.2008 12:52:00
Thread-ID: 1014231
5 Beiträge
automatisches berechnen für einzelnes blatt aussch
soendi

moin moin alle zusammen,
ist es möglich in excel das automatische berechnen für ein einzelnes tabellenblatt auszuschalten?
das berechnet mir bei jeder änderung ca. 20 sekunden lang und ich finde das zu mühsam... da aber die berechnungen nur auf einem blatt sind, würde ich am liebsten nu...
16.03.2009 17:08:19
Thread-ID: 1060838
3 Beiträge
Anzeige
Zellen "gegeneinander" berechnen
Ron

Guten Morgen liebe Forumteilnehmer,
ich benötige einmal Eure Hilfe. Leider bin ich in VBA noch nicht so gut unterwegs - bediene mich aber bei guten Ideen gern einiger Äußerungen aus dem Forum.
Ich habe im Tabellenblatt "Daten" diverses Material nach folgender Form aufgebaut:
Spalte...
11.04.2009 10:36:03
Thread-ID: 1068279
6 Beiträge
Anzeige
Anzahl 6-stelliger Zahlen einer Zelle berechnen
Claus

Hallo Excelianer,
aus einer ziemlich schlechten Datenlage bräuchte ich folgende Bestimmung (wenn´s geht per Formel)
Anzahl der in Zelle Ax stehenden 6-stelligen Zahlenblöcke, die mit 6 beginnen
Beispiel: "ABC 20 (600691)" Ergebnis: 1
"blablabla" Ergebnis: 0
"ABC 28 Multi (60...
28.10.2009 12:16:25
Thread-ID: 1112753
5 Beiträge
Steigung einer Liste berechnen
Bryan

Hallo liebe Excel-Freunde,
ich habe folgendes Problem:
Aus einer Liste A4:D6 mit verschieden Werten mit den dazugehörigen Zeitpunkten in A4:A6, möchte ich die Zeile mit der höhsten, niedrigsten (auch negativ) und konstantesten (= Null) Steigung selektieren. Dazu reicht es aus, wenn in d...
05.11.2009 14:11:55
Thread-ID: 1114696
3 Beiträge
Zinstermine berechnen
peter

Liebes Forum,
ich wende mich her als Forumsneuling mit einem kleinen Problem, fuer das ich Hilfe suche.
Ich moechte fuer Wertpapiere per Formel (nicht Makro/VBA) den naechsten Zinstermin errechnen.
Dazu gibt es eine Spalte "Zinsbeginn" mit einem beliebigen Datum in der Vergangenheit...
26.11.2009 18:34:09
Thread-ID: 1120329
12 Beiträge
Anzeige
Zinstage unterschiedlich berechnen | Herbers Excel-Forum
Roger Berchtold
Hallo zusammen
Ich habe folgendes Problem:
Gemäss beiliegendem Tabellenblatt sollte ich folgendes neu berechnen können.
1. Zinstage: Beginnend in E1 und endend in G1 nun hat aber der Zinssatz geändert auf ende Jahr. Es sollte jetzt also die Tage bis ende 2009 und ab 2010 zählen damit da...
06.01.2010 19:27:27
Thread-ID: 1127688
12 Beiträge
Einzelne Worksheets berechnen
Peter

Guten Abend
Ich habe in einem Workbook verschiedene UDFs in mehreren Worksheets, die erst nach einer Eingabe berechnet werden.
Nun möchte ich nach dem Öffnen gleich bei allen Tabellen das Berechnen ausführen, so dass auch ohne eine weitere Eingabe die UDFs aktualisiert sind.
Was ist...
11.05.2010 20:50:10
Thread-ID: 1156719
4 Beiträge
VBA-Array "durcheinander" berechnen (ohne For..Nex
Alex

Hallo zusammen,
ich möchte gerne eine (eigentlich gut funktionierende) Excel/VBA-Routine in ein VBA-Array portieren. Das hat den einfachen Grund, dass die Excelroutine mit Sverweisen arbeitet und mittlerweile knapp 10.000 "Kenner" mit rund 90.000 Ausgabezellen zu versorgen sind. Ein VBA-Arr...
01.09.2010 14:08:01
Thread-ID: 1175226
5 Beiträge
Permanentes Berechnen einer function erzwingen
b.

Hallo liebe Excelexperten,
ich scheitere gerade mal wieder ... weil ich nicht weiss wonach ich suchen muss und wäre für Tipps sehr dankbar.
Ich habe eine Funktion geschrieben, die auch genau das tut was sie soll - aber die Zellen, in denen sie eingesetzt ist, aktualisieren sich nicht au...
25.04.2011 22:59:50
Thread-ID: 1211757
10 Beiträge
differenz berechnen veränderung in +/-
beatles

hallo
habe das forum bereits etwas durchsucht aber bin nicht wirklich auf eine lösung gestossen.
ich benötige eine differenzrechnung von jeweils zwei zahlen. die startzahl kann grösser oder kleiner sein
als die aktuelle zahl: + oder -.
die berechnung erfolgt immer zwischen zwei...
25.10.2011 17:17:38
Thread-ID: 1235358
7 Beiträge
Entdecke relevante Threads

Schau dir verwandte Threads basierend auf dem aktuellen Thema an

Alle relevanten Threads mit Inhaltsvorschau entdecken
Anzeige
Anzeige

Infobox / Tutorial

Normalverteilung und Expected Shortfall in Excel berechnen

Schritt-für-Schritt-Anleitung

Um den Expected Shortfall (ES) bei einer Normalverteilung in Excel zu berechnen, kannst du folgende Schritte befolgen:

  1. Zufallszahlen generieren:

    • Erzeuge eine Liste von normalverteilten Zufallszahlen. Verwende die Formel: 
      =NORMINV(ZUFALLSZAHL(); 0; 1)
    • Dies gibt dir 10.000 Werte mit einem Mittelwert von 0 und einer Standardabweichung von 1.

  2. Value at Risk (VaR) berechnen:

    • Berechne das 99%-Quantil (VaR) mit der Formel: 
      =NORMINV(0,01; 0; 1)
    • Dies gibt dir den Wert, unter dem 99% der Verteilung liegen.

  3. Expected Shortfall berechnen:

    • Identifiziere die Werte, die unter dem berechneten VaR liegen. Verwende die Formel: 
      {=MITTELWERT(KGRÖSSTE(A1:A10000;ZEILE(INDIREKT("1:100"))) }
    • Diese Matrixformel gibt den Durchschnitt der 1% schlechtesten Werte zurück.

  4. Flexible Anpassung:

    • Um das Konfidenzniveau zu ändern, passe die Eingabewerte in deinen Formeln an (z.B. für 95%).

Häufige Fehler und Lösungen

  • Fehler: #VALUE!

    • Dies kann auftreten, wenn die Eingabewerte nicht korrekt sind. Stelle sicher, dass alle Bereichsangaben und Formeln richtig geschrieben sind.

  • Problem mit der Matrixformel:

    • Stelle sicher, dass du die Matrixformel mit STRG + SHIFT + RETURN abschließt, nicht nur mit ENTER.

  • Unterschied zwischen VaR und ES:

    • Verwechsel nicht den Value at Risk mit dem Expected Shortfall. VaR gibt den Verlust an einem bestimmten Punkt an, während ES den durchschnittlichen Verlust über den VaR hinaus misst.

Alternative Methoden

  • Verwendung von Excel-Funktionen:

    • Du kannst auch die Funktion NORMVERT verwenden, um Wahrscheinlichkeiten für bestimmte Werte zu berechnen. Beispiel: 
      =NORMVERT(x; Mittelwert; Standardabweichung; FALSCH)

  • Simulation:

    • Eine Monte-Carlo-Simulation kann genutzt werden, um verschiedene Szenarien zu durchspielen und eine robuste Schätzung des Expected Shortfall zu erhalten.

Praktische Beispiele

Hier ist ein Beispiel, wie du das 95%-Konfidenzintervall in Excel berechnen kannst:

=1-NORMVERT(1,645; 0; 1; FALSCH)

Um das 95%-Quantil zu berechnen, verwende:

=NORMINV(0,95; 0; 1)

Diese Formeln helfen dir, die Verteilung zu verstehen und den Verlust bei einem bestimmten Konfidenzniveau abzuschätzen.

Tipps für Profis

  • Verwende benannte Bereiche: Dies macht deine Formeln leichter verständlich und wartungsfreundlicher.
  • Dokumentation: Halte alle Schritte und Formeln in einem separaten Arbeitsblatt fest, um die Nachvollziehbarkeit zu gewährleisten.
  • Visualisierung: Stelle deine Ergebnisse grafisch dar, um Trends und Extremwerte visuell zu identifizieren (z.B. mit Histogrammen).

FAQ: Häufige Fragen

1. Wie berechne ich den Expected Shortfall für nicht normalverteilte Daten? Um den ES für nicht normalverteilte Daten zu berechnen, kannst du die gleichen Schritte wie oben befolgen, aber benutze die tatsächlichen Daten anstelle von normalverteilten Zufallszahlen.

2. Was ist der Unterschied zwischen VaR und Expected Shortfall? VaR ist der Verlust, der mit einer bestimmten Wahrscheinlichkeit nicht überschritten wird, während der Expected Shortfall den durchschnittlichen Verlust beschreibt, der über diesem Schwellenwert liegt.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige