MITTELWERTWENN in Excel: Mittelwert mit Bedingung

=MITTELWERTWENN(A2:A7;"North";C2:C7) bildet den Mittelwert der Umsätze in C2:C7 in den Zeilen, in denen Spalte A North ist. Die Funktion, in englischem Excel AVERAGEIF, funktioniert wie SUMMEWENN, nur dass sie die Summe durch die Anzahl der passenden Zeilen teilt. Die Tabelle zeigt die englische Schreibweise, =AVERAGEIF(A2:A7,"North",C2:C7); du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons.

FormelnZurücksetzen

F2fx

=AVERAGEIF(A2:A7,"North",C2:C7)

	A	B	C	D	E	F
1	Region	Product	Sales		Condition	Average
2	North	Apple	120		North	90
3	South	Pear	45		North, Apple	80
4	North	Pear	110		Over 50	120
5	East	Apple	55
6	South	Apple	195
7	North	Apple	40

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =MITTELWERTWENN(A2:A7;"North";C2:C7)

F2 bildet den Mittelwert der drei North-Zeilen, 120, 110 und 40, und zeigt 90. F3 braucht zwei Bedingungen, North und Apple, also nimmt es MITTELWERTWENNS (englisch AVERAGEIFS): (120 + 40) / 2 = 80. F4 hat keinen eigenen Mittelwertbereich, also bildet es den Mittelwert der passenden Umsätze selbst.

Syntax von MITTELWERTWENN und MITTELWERTWENNS

=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

Die Reihenfolge der Argumente ist dieselbe Falle wie bei SUMMEWENN und SUMMEWENNS: MITTELWERTWENN setzt den Bereich, aus dem der Mittelwert gebildet wird, zuletzt (und du darfst ihn weglassen), MITTELWERTWENNS setzt ihn zuerst. Die Kriterien schreibst du in beiden gleich: "North", ">50", "<>0", "*apple*" oder ein Operator mit angehängter Zelle, ">"&F5. Leere Zellen und Text im Mittelwertbereich werden übersprungen.

Mittelwert ohne Nullen

MITTELWERT (englisch AVERAGE) zählt eine 0 als Wert, also ziehen zwei abwesende Schüler mit der Punktzahl 0 den Klassendurchschnitt nach unten. Leere Zellen sind etwas anderes: MITTELWERT überspringt sie. =MITTELWERTWENN(B2:B7;"<>0") überspringt auch die Nullen.

FormelnZurücksetzen

E3fx

=AVERAGEIF(B2:B7,"<>0")

	A	B	C	D	E
1	Student	Score		Method	Result
2	Ana	80		AVERAGE	48
3	Ben	0		Ignore zeros	80
4	Cara	90		Count of zeros	2
5	Dan			Count of numbers	5
6	Eva	70
7	Finn	0

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =MITTELWERTWENN(B2:B7;"<>0")

MITTELWERT teilt 240 durch 5, weil Dans leere Zelle ausgelassen wird, die beiden Nullen aber mitzählen, und zeigt 48. MITTELWERTWENN mit "<>0" teilt 240 durch 3 und zeigt 80. Tippe 60 in B5, und beide ändern sich; tippe 0 in B5, und nur MITTELWERT bewegt sich. Um auch negative Zahlen zu ignorieren, nimmst du ">0".

Warum MITTELWERTWENN #DIV/0! liefert

Wenn nichts passt, hat MITTELWERTWENN nichts, durch das es teilen kann, und liefert #DIV/0! (in deutschem Excel heißt der Fehler genauso). Fang ihn mit WENNFEHLER ab, um stattdessen einen Strich, eine Meldung oder eine leere Zelle zu zeigen.

FormelnZurücksetzen

F2fx

=AVERAGEIF(A2:A7,"West",C2:C7)

	A	B	C	D	E	F
1	Region	Product	Sales		Formula	Result
2	North	Apple	120		West average	#DIV/0!
3	South	Pear	45		With IFERROR	No sales
4	North	Pear	110		North max	120
5	East	Apple	55		North min	40
6	South	Apple	195		Apple max	195
7	North	Apple	40

#DIV/0! Die Formel teilt durch null oder durch eine leere Zelle.Im deutschen Excel: =MITTELWERTWENN(A2:A7;"West";C2:C7)

Es gibt keine West-Zeile, also zeigt F2 #DIV/0! und F3 die Meldung. Ändere A3 in West, und beide zeigen 45.

MAXWENNS und MINWENNS

F4 bis F6 in der Tabelle oben finden den größten und den kleinsten Wert mit einer Bedingung. MAXWENNS (englisch MAXIFS) und MINWENNS (englisch MINIFS) verwenden die Reihenfolge von MITTELWERTWENNS, mit dem durchsuchten Bereich zuerst: =MAXWENNS(C2:C7;A2:A7;"North") liefert 120 und =MINWENNS(C2:C7;A2:A7;"North") liefert 40. Anders als MITTELWERTWENN liefern sie 0, wenn nichts passt, keinen Fehler.

MAXWENNS und MINWENNS brauchen Excel 2019 oder neuer, oder Microsoft 365. In Excel 2016 und älter erledigt =MAX(WENN(A2:A7="North";C2:C7)) dasselbe; in diesen Versionen gibst du die Formel mit Strg+Umschalt+Eingabe ein (Cmd+Umschalt+Eingabe auf dem Mac).

Übung: Mittelwert mit zwei Bedingungen

FormelnZurücksetzen

F2fx

	A	B	C	D	E	F
1	Student	Class	Score		Condition	Average
2	Ana	A	80		Class A, no zeros
3	Ben	B	75
4	Cara	A	0
5	Dan	B	60
6	Eva	A	90
7	Finn	B	0
8	Gus	A	70

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.

Du bist dran: Bilde den Mittelwert der Punktzahlen der Klasse A und lass dabei die Nullen (abwesende Schüler) aus. Schreib die Formel in F2.

PrüfenLösung zeigen

Mittelwert von Mittelwerten: ein häufiger Fehler

Den Mittelwert aus den Mittelwerten unterschiedlich großer Gruppen zu bilden, ergibt den falschen Gesamtmittelwert. North hat drei Zeilen und South zwei, also zählt jede South-Zeile im Mittelwert der beiden Mittelwerte mehr, als sie sollte.

FormelnZurücksetzen

E4fx

=AVERAGE(E2:E3)

	A	B	C	D	E
1	Region	Sales		Formula	Result
2	North	120		North	90
3	South	45		South	120
4	North	110		Average of the two	105
5	South	195		All rows	102
6	North	40

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =MITTELWERT(E2:E3)

E4 zeigt 105, E5 den echten Mittelwert der fünf Zeilen, 102. Sind die Gruppen unterschiedlich groß, bildest du den Mittelwert der Zeilen selbst mit einem MITTELWERTWENNS, oder du teilst ein SUMMEWENNS durch ein ZÄHLENWENNS mit denselben Bedingungen:

=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")

In deutschem Excel: =SUMMEWENNS(B2:B6;A2:A6;"North")/ZÄHLENWENNS(A2:A6;"North").

Eine Punktzahl, die nach Credits oder nach Menge gewichtet ist, ist wieder eine andere Berechnung: ein gewichteter Mittelwert.