Mittelwerte in Excel berechnen – aber richtig!

Wenn Sie mit Microsoft Excel ein arithmetisches Mittel berechnen möchten, können Sie dafür die "Mittelwert"-Funktion verwenden. Doch diese hat gewisse Eigenheiten – wer sie nicht beachtet, erhält leicht ein falsches Ergebnis. Dieter Schiecke beschreibt, wie Sie Fehler vermeiden und wie Sie mit Hilfe einer Matrixformel auch gewichtete Mittelwerte einfach berechnen können.

Mittelwerte in Excel berechnen – aber richtig!

Wenn Sie mit Microsoft Excel ein arithmetisches Mittel berechnen möchten, können Sie dafür die "Mittelwert"-Funktion verwenden. Doch diese hat gewisse Eigenheiten – wer sie nicht beachtet, erhält leicht ein falsches Ergebnis. Dieter Schiecke beschreibt, wie Sie Fehler vermeiden und wie Sie mit Hilfe einer Matrixformel auch gewichtete Mittelwerte einfach berechnen können.

Wenn Sie mit Microsoft Excel ein arithmetisches Mittel berechnen möchten (z.B. die durchschnittlichen Projektkosten), können Sie dafür die "Mittelwert"-Funktion verwenden. Doch diese hat gewisse Eigenheiten - wer sie nicht beachtet, erhält leicht ein falsches Ergebnis. Im Folgenden erfahren Sie, wie Sie Fehler vermeiden und wie Sie mit Hilfe einer Matrixformel auch gewichtete Mittelwerte einfach berechnen können.

Das arithmetische Mittel berechnen

Das arithmetische Mittel lässt sich in Excel komfortabel mit der Funktion MITTELWERT(Zahl1; Zahl2; ...) berechnen. Excel berücksichtigt dabei nur numerische Werte als Argumente. Text, Wahrheitswerte (WAHR, FALSCH) oder leere Zellen werden ignoriert.

Bild 1 verdeutlicht das an einem Beispiel. Die auf der linken Seite abgebildete Tabelle zur Halbjahresauswertung enthält neben den Monatsnamen die Werte für die jeweils aufgelaufenen Kosten. Für den Monat Juni wurden die Kosten bisher noch nicht abgerechnet, die entsprechende Zelle B10 bleibt leer. Da Excel leere Zellen bei der Berechnung ignoriert, liefert die Formel =MITTELWERT(B5:B10) den korrekten Mittelwert aus den fünf in Spalte B eingetragenen Werten (Ergebnis: 555,0=2.775:5).

Die Bedeutung der Null berücksichtigen

Beispiel 2 (Bild 1) enthält die gleichen Werte wie Beispiel 1 mit dem Unterschied, dass hier für den fehlenden Juni-Wert eine Null eingetragen wurde. Welche Folge das hat, sehen Sie am Ergebnis in Zelle I11. Das Resultat weicht von dem in D11 ab und beträgt diesmal 462,5. Excel hat in diesem Fall die Zelle G10, die jetzt einen numerischen Wert enthält, bei der Mittelwertberechnung mit berücksichtigt. (Ergebnis: 462,5=2.775:6)

Da Excel zwischen einer leeren Zelle und einem Nullwert unterscheidet, sollten Sie keine Nullen für (noch) fehlende Werte eingeben, wenn Sie den Mittelwert berechnen wollen.

Bild 1: Eine leere Zelle oder eine Null sind für Excel nicht das Gleiche; folglich kommt es zu unterschiedlichen Ergebnissen.

Nullwerte ausschließen

In der Praxis lässt sich die Empfehlung, keine Nullwerte für fehlende Werte einzugeben, allerdings nicht immer umsetzen. Zum Beispiel kann man bei einem Datenimport aus einem Fremdsystem oft nicht verhindern, dass Wertebereiche, für die später der Mittelwert berechnet werden soll, auch Nullwerte enthalten. Hier hilft folgende Lösung weiter, die den Mittelwert nur für solche Zellen eines Bereichs berechnet, die keine Null enthalten. Die entsprechende Formel lässt sich mit der WENN-Funktion realisieren. Die Anweisung lautet: WENN(B5:B100). In Bild 2 sehen Sie die fertige Formel. An den geschweiften Klammern erkennen Sie, dass es sich dabei um eine Matrixformel handelt – also um eine Formel, die mehrere Wertesätze bearbeiten kann:

{=MITTELWERT(WENN(B5:B100;B5:B10))}

Um die Formel korrekt einzugeben, tippen Sie zunächst =MITTELWERT(WENN(B5:B100;B5:B10)) ein. Beenden Sie die Eingabe dann nicht wie sonst mit "Enter", sondern mit der Tastenkombination "Strg" + "Umschalt" + "Enter".

Bild 2: Mittels einer Matrixformel werden Zellen mit Nullwerten von der Mittelwertberechnung ausgeschlossen.

Wie funktioniert die Formel?

Mit der Formel werden beim Berechnen des Mittelwertes nur die Zellen berücksichtigt, die die Bedingung "ungleich Null" ("0") erfüllen. Die Bedingung wird durch die Funktion WENN überprüft.

Dazu liefert die WENN-Funktion der MITTELWERT-Funktion eine Argumentliste, die einerseits alle von Null verschiedenen Werten enthält und andererseits den Wahrheitswert FALSCH für alle anderen Werte (also für alle Nullwerte). Wie in Bild 3 rechts im Bereich "Gegenprobe" dargestellt, wird den Zellen, die eine Null enthalten, somit der Wahrheitswert FALSCH zugewiesen. Diese Zellen ignoriert die MITTELWERT-Funktion beim Berechnen.

Bild 3: Die "Gegenprobe" im rechten Teil des Bildes hilft, das Funktionieren der Matrixformel nachvollziehbar darzustellen.

Mittelwert mit Gewichtung berechnen

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 19
Kommentare 2

Alle Kommentare

Guest
Kurz, prägnant und Konzentration auf das Wesentliche - das hilft im Alltag.
Guest
In diesem Fall: Mittelwert (D) = average (E) (nicht median) Alles in einem: =SUMME(WENN(B5:B100|B5:B10)*(C5:C10))/SUMME(WENN(B5:B100|C5:C10)) Englisch: =SUM(IF(B5:B100|B5:B10)*(C5:C10))/SUM(IF(B5:B100|C5:C10))
Alle anzeigen