Gewichteter Mittelwert in Excel: Formel mit SUMMENPRODUKT

=SUMMENPRODUKT(B2:B5;C2:C5)/SUMME(C2:C5) berechnet einen gewichteten Mittelwert: Jede Punktzahl in B wird mit ihrem Gewicht in C multipliziert, die Produkte werden addiert, und die Summe wird durch die Summe der Gewichte geteilt. SUMMENPRODUKT heißt in englischem Excel SUMPRODUCT und SUMME heißt SUM, so zeigt die Tabelle die Formel: =SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5). Du kannst die Formeln in der Tabelle auch deutsch eingeben, mit Semikolons.

FormelnZurücksetzen

F2fx

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5)

	A	B	C	D	E	F
1	Part	Score	Weight		Average	Result
2	Homework	85	20%		Weighted	81.2
3	Quizzes	78	30%		Plain AVERAGE	80.75
4	Midterm	72	20%
5	Final	88	30%

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMENPRODUKT(B2:B5;C2:C5)/SUMME(C2:C5)

Die gewichtete Note ist 81.2, während ein einfacher MITTELWERT (englisch AVERAGE) 80.75 liefert, weil er die Hausaufgaben, die 20 % zählen, so behandelt, als zählten sie so viel wie die Abschlussprüfung mit 30 %. Ändere die Punktzahl der Abschlussprüfung, und die gewichtete Note bewegt sich stärker als bei derselben Änderung der Hausaufgaben.

Excel hat keine Funktion für den gewichteten Mittelwert, also ist SUMMENPRODUKT geteilt durch SUMME die Standardformel. Google Sheets hat AVERAGE.WEIGHTED(B2:B5,C2:C5).

So funktioniert die Formel für den gewichteten Mittelwert

SUMMENPRODUKT multipliziert die beiden Bereiche Zeile für Zeile und addiert die Ergebnisse. Mit einer Hilfsspalte ausgeschrieben ist es eine Spalte mit Produkten und deren SUMME:

FormelnZurücksetzen

D6fx

=SUM(D2:D5)

	A	B	C	D
1	Part	Score	Weight	Score x weight
2	Homework	85	20%	17.0
3	Quizzes	78	30%	23.4
4	Midterm	72	20%	14.4
5	Final	88	30%	26.4
6	Total		100%	81.2
7	Weighted average			81.2

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

Jeder Teil trägt seine Punktzahl mal sein Gewicht bei: 85 × 20 % ist 17.0, 78 × 30 % ist 23.4 und so weiter. Zusammen ergeben sie 81.2. Die Gewichte ergeben zusammen 100 %, also ändert die Division durch C6 hier nichts, aber sie hält die Formel richtig, wenn das nicht der Fall ist.

Gewichte, die nicht 100 % ergeben

Gewichte müssen keine Prozentwerte sein. Ein Notenschnitt (GPA) wird nach Credits gewichtet, ein Durchschnittspreis nach Menge. Die Division durch die SUMME der Gewichte funktioniert bei jeder Summe.

FormelnZurücksetzen

F2fx

=SUMPRODUCT(B2:B6,C2:C6)/SUM(C2:C6)

	A	B	C	D	E	F
1	Course	Grade points	Credits		Average	Result
2	Math	4	4		Weighted GPA	3.51
3	History	3	3		Plain average	3.48
4	Biology	3.7	4		Total credits	14
5	Art	2.7	2
6	Lab	4	1

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMENPRODUKT(B2:B6;C2:C6)/SUMME(C2:C6)

Ohne die Division würde die Formel die Summe aus Notenpunkten mal Credits liefern, hier 49,2, keinen Notenschnitt. Mit ihr zeigt F2 den Schnitt, gewichtet mit 14 Credits. Die Kurse mit vier Credits ziehen den Mittelwert zu ihren Noten hin, und das Labor mit einem Credit bewegt ihn kaum: Ändere B6 in 2 und sieh, wie wenig sich F2 im Vergleich zu F3 ändert.

Sind deine Gewichte Prozentwerte, die genau 100 % ergeben, liefert =SUMMENPRODUKT(B2:B5;C2:C5) allein dasselbe Ergebnis. Behalte das /SUMME(...) trotzdem: An dem Tag, an dem ein Gewicht geändert wird und die Summe 105 % beträgt, ist die Formel ohne es falsch, und nichts auf dem Blatt weist darauf hin.

Gewichteter Mittelwert mit einer Bedingung

Um nur einige Zeilen zu gewichten, multiplizierst du in SUMMENPRODUKT mit einer Bedingung und addierst die passenden Gewichte mit SUMMEWENN. Unten wird der Durchschnittspreis pro Region mit der verkauften Menge gewichtet.

FormelnZurücksetzen

G2fx

=SUMPRODUCT((A2:A7=F2)*C2:C7*D2:D7)/SUMIF(A2:A7,F2,D2:D7)

	A	B	C	D	E	F	G
1	Region	Product	Price	Qty		Region	Average price
2	North	Apple	$1.20	100		North	$1.45
3	South	Pear	$1.50	40		South	$1.36
4	North	Pear	$1.50	60
5	South	Apple	$1.20	120
6	North	Plum	$2.00	40
7	South	Plum	$2.00	20

Klicke auf eine Zelle, um ihre Formel zu sehen. Ändere eine Zahl oder eine Formel, und die Tabelle rechnet neu.Im deutschen Excel: =SUMMENPRODUKT((A2:A7=F2)*C2:C7*D2:D7)/SUMMEWENN(A2:A7;F2;D2:D7)

North hat 100 Äpfel, 60 Birnen und 40 Pflaumen verkauft, also liegt sein Durchschnittspreis bei $1.45, näher am Apfelpreis, als es ein einfacher Mittelwert der drei Preise wäre. Die Bedingung (A2:A7=F2) ist 1 in North-Zeilen und sonst 0, also tragen die anderen Zeilen oben nichts bei, und SUMMEWENN addiert unten nur die North-Mengen.

Übung: gewichteter Durchschnittspreis

FormelnZurücksetzen

F2fx

	A	B	C	D	E	F
1	Batch	Price	Qty		Average	Result
2	Jan	$4.20	100		Weighted price
3	Feb	$4.50	40
4	Mar	$3.90	250
5	Apr	$4.80	10
6	May	$4.10	120

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

Du bist dran: Du hast denselben Artikel in fünf Lieferungen zu unterschiedlichen Preisen gekauft. Berechne den Durchschnittspreis pro Stück, gewichtet mit der Menge jeder Lieferung. Schreib die Formel in F2.

PrüfenLösung zeigen

Fehler, die den falschen gewichteten Mittelwert ergeben

• MITTELWERT der Produkte. =MITTELWERT(D2:D5) über eine Spalte mit Punktzahl × Gewicht teilt durch die Anzahl der Zeilen, nicht durch die Gewichte, und liefert eine kleine, sinnlose Zahl. Teile die SUMME der Produkte durch die SUMME der Gewichte.
• Durch die Anzahl statt durch die Gewichte teilen. =SUMMENPRODUKT(B2:B6;C2:C6)/ANZAHL(B2:B6) stimmt nur, wenn jedes Gewicht 1 ist.
• Bereiche, die nicht übereinanderliegen. =SUMMENPRODUKT(B2:B6;C3:C7) paart jeden Wert mit dem Gewicht der nächsten Zeile. Beide Bereiche müssen in denselben Zeilen beginnen und enden; unterschiedliche Größen liefern #WERT! (englisch #VALUE!).
• Ein leeres Gewicht. Ein leeres Gewicht zählt als 0, also fällt diese Zeile ohne Meldung weg. Soll ein fehlendes Gewicht die Berechnung stoppen, prüfst du zuerst mit =ANZAHLLEEREZELLEN(C2:C6).
• Mittelwerte mitteln. Zwei Klassendurchschnitte von 70 (10 Schüler) und 90 (30 Schüler) ergeben im Mittel nicht 80. Gewichtet mit den Klassengrößen ist das Ergebnis 85; die Seite zu MITTELWERTWENN zeigt dieselbe Falle mit Bedingungen.