Quantil berechnen trotz #NV-Werten in Excel
Schritt-für-Schritt-Anleitung
Um das 95-Quantil in Excel zu berechnen, während du #NV-Werte ignorierst, kannst du folgende Schritte befolgen:
-
Öffne deine Excel-Datei mit den Werten.
-
Stelle sicher, dass deine Daten in einem Bereich (z. B. A1:A11) angeordnet sind.
-
Verwende die folgende Formel, um das Quantil zu berechnen:
=QUANTIL.INKL(WENN(ISTZAHL(A1:A11);WENN(A1:A11>0;A1:A11));0,95)
-
Drücke Strg + Shift + Enter, um die Formel als Matrixformel einzugeben.
Diese Formel überprüft zuerst, ob die Zellen Zahlen enthalten, und schließt alle #NV-Werte aus der Berechnung aus.
Häufige Fehler und Lösungen
Alternative Methoden
Eine alternative Methode zur Berechnung des Quantils, die keine Matrixformel benötigt, ist die Verwendung der AGGREGAT-Funktion:
=AGGREGAT(16;6;A1:A11/(A1:A11>0);0,95)
Diese Formel ignoriert auch #NV-Werte und berechnet das 95-Quantil. Der Vorteil dieser Methode ist, dass sie einfacher zu handhaben ist, da keine geschweiften Klammern erforderlich sind.
Praktische Beispiele
Angenommen, du hast folgende Werte in den Zellen A1 bis A11:
| A |
| 10 |
| 20 |
| #NV |
| 30 |
| 40 |
| 50 |
| #NV |
| 60 |
| 70 |
| 80 |
| 90 |
Um das 95-Quantil zu berechnen, kannst du die oben genannten Formeln verwenden. Das Ergebnis sollte 86 zurückgeben, da dies der Wert ist, der 95% der Daten überschreitet.
Tipps für Profis
- Wenn du regelmäßig mit Daten arbeitest, die #NV-Werte enthalten, erwäge, die Daten vor der Berechnung zu bereinigen.
- Du kannst auch die Funktion FILTER (in neueren Excel-Versionen) verwenden, um nur die relevanten Werte zu extrahieren, bevor du das Quantil berechnest.
-
Nutze die Funktion Mittelwert, um den Durchschnitt der verbleibenden Werte zu berechnen, während du #NV-Werte ignorierst. Verwende dazu:
=MITTELWERT(WENN(ISTZAHL(A1:A11);A1:A11))
FAQ: Häufige Fragen
1. Wie ignoriere ich #NV-Werte bei der Berechnung von Mittelwerten?
Verwende die Funktion MITTELWERT zusammen mit WENN und ISTZAHL:
=MITTELWERT(WENN(ISTZAHL(A1:A11);A1:A11))
2. Gibt es einen Unterschied zwischen QUANTIL und QUANTIL.INKL?
Ja, QUANTIL (in älteren Excel-Versionen) und QUANTIL.INKL geben unterschiedliche Ergebnisse zurück, wenn es um die Berechnung der Quantile geht. QUANTIL.INKL schließt die Extremwerte in die Berechnung ein, während QUANTIL.EXKL sie ausschließt.
3. Kann ich auch andere Quantile berechnen?
Ja, du kannst die Quantil-Funktionen anpassen, um verschiedene Quantile (z.B. 0,25 für das 1. Quartil) zu berechnen, indem du den entsprechenden Wert in der Formel änderst.