Syntax:
=MITTELWERTWENN([bereich], [kriterien])
Beispiel:
CH/DE: =MITTELWERTWENN(D2:D6;"<>0")
EN: =MITTELWERTWENN(D2:D6,"<>0")
Bei der Verwendung der Funktion MITTELWERT in Excel muss man wissen, dass auch Nullwerte miteinbezogen werden. Inbesondere bei grossen Datensätzen, welche mittels Formeln berechnet wurden, können NULLER vorhanden sein. Beispielsweise wenn die Formel auf eine leere Zelle verweist. Üblicherweise macht es in so einem Szenario keinen Sinn, dass die NULLER in die MITTELWERT-Berechnung mit einbezogen werden.
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