Syntax:
=MITTELWERTWENN([bereich], [kriterien])
Beispiel:
CH/DE: =MITTELWERTWENN(D2:D6;"<>0")
EN: =MITTELWERTWENN(D2:D6,"<>0")
Wer mit der Funktion MITTELWERT in Excel arbeitet, sollte wissen: Auch Nullwerte (0) werden standardmäßig mitgerechnet. Das kann zu verfälschten Ergebnissen führen – vor allem bei großen Datensätzen, in denen leere Zellen durch Formeln als 0 dargestellt werden.
In diesem Artikel zeigen wir, wie Sie den Mittelwert ohne Nullwerte korrekt berechnen – und welche Alternativen zu MITTELWERT Sie verwenden können, um aussagekräftige Ergebnisse zu erhalten.
NULL bei MITTELWERT ignorieren
Syntax
Die Funktion MITTELWERTWENN gibt einen Mittelwert in Abhängigkeit von einer Bedingung zurück, wobei die folgende Syntax verwendet wird:
=
[bereich]: Der Bereich der Zellen, für die der Mittelwert berechnet werden soll, einschliesslich Zahlen, Namen, Arrays oder Bezügen, die Zahlen enthalten.
[kriterien]: Die Kriterien in Form einer Zahl, eines Ausdrucks, eines Zellbezugs oder eines Texts, mit denen definiert wird, für welche Zellen der Mittelwert berechnet werden soll.
Beispiel
[kriterien] ist der Ausdruck, den die referenzierten Werte erfüllen müssen. Sie verwenden das Kriterium <>0, um Nullwerte auszuschliessen. Die Funktion =MITTELWERTWENN(D2:D6,“<>0″) in D7 bildet den Durchschnitt der Werte in D2:D6 nur dann, wenn sie ungleich 0 sind.
In Spalte C wird die Standardfunktion MITTELWERT() verwendet, die auch Nullwerte berücksichtigt. Der Durchschnitt wird in Spalte A mit der Formel =SUMME(B2:B7)/ANZAHL(B2:B7) berechnet.
Bei der Verwendung von MITTELWERTWENN() ist zu beachten, dass leere Zellen ignoriert werden, was möglicherweise nicht erwünscht ist.
Eine alternative Formel könnte so lauten: =SUMME(Bereich)/ZÄHLENWENN(Bereich;“<>0″)
Benötigen Sie Excel-Hilfe?
Sind Sie an weiterenen Tipps interessiert? Informationen zum SVERWEIS finden Sie hier und eine Anleitung zur Index-Vergleich-Formel hier.
Haben Sie ein Problem mit Ihrer Excel-Datei? Für nur 49 Fr. analysieren wir Ihre Datei und erstellen einen konkreten Vorschlag. Weitere Infos dazu auf: www.exact-construct.ch
„Die Funktion =MITTELWERTWENN(D2:D6,“>0″) in D7 bildet den Durchschnitt der Werte in D2:D6 nur dann, wenn sie ungleich 0 sind.“
Die Funktion sagt größer 0 aus, nicht ungleich 0, ungleich 0 wäre „0“
Vielen Dank für den Hinweis. Da hast du natürlich recht. Das war im Text nicht korrekt beschrieben. Wir haben es korrigiert.
Kann ich den Bereich durch einzelne Zellen ersetzen z.b. A3;D3;G3?
wie muss ich die Formel dann anpassen wenn 0 bleiben soll?
Gute Frage, dies habe ich mir so noch nicht überlegt. Vorab: Einen Bereich brauchst du in dieser Formel. Aber was ich vorhin getestet habe:
1. Ich definiere die Zellen A3;D3;G3 als Bereich indem ich unter Formeln -> Namensmanager ->Namen definieren den gewählten Zellen einen Namen zuweise.
2. In der Formel selber verweise ich dann auf diesen Namen.
Lange Rede kurzer Sinn: Dieser Ansatz funktioniert nur wenn die Zellen zusammenhängend sind bspw. A3:G3. Ansonsten kommt auch damit ein Fehler zurück.
Wenn jemand anders einen Lösung mit der Mittelwertwenns-Formel hat, gerne in die Kommentare schreiben.
Viele Grüsse
Adrian