Rata-rata Tertimbang di Excel: Rumus SUMPRODUCT

=SUMPRODUCT(B2:B5,C2:C5)/SUM(C2:C5) menghitung rata-rata tertimbang: setiap skor di kolom B dikalikan dengan bobotnya di kolom C, hasil kalinya dijumlahkan, lalu totalnya dibagi dengan jumlah bobot.

RumusReset

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%

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Nilai tertimbangnya 81,2, sedangkan AVERAGE biasa memberi 80,75, karena AVERAGE memperlakukan pekerjaan rumah yang berbobot 20% seolah-olah sama pentingnya dengan ujian akhir yang berbobot 30%. Ubah skor ujian akhir, nilai tertimbangnya bergeser lebih banyak dibanding jika perubahan yang sama terjadi pada pekerjaan rumah.

Excel tidak punya fungsi WEIGHTED.AVERAGE, jadi SUMPRODUCT dibagi SUM adalah rumus standarnya. Google Sheets punya AVERAGE.WEIGHTED(B2:B5,C2:C5).

Cara kerja rumus rata-rata tertimbang

SUMPRODUCT mengalikan kedua range baris demi baris lalu menjumlahkan hasilnya. Jika ditulis dengan kolom bantu, isinya adalah satu kolom hasil kali dan SUM-nya:

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Setiap bagian menyumbang skornya dikali bobotnya: 85 × 20% adalah 17,0, 78 × 30% adalah 23,4, dan seterusnya. Jumlahnya 81,2. Bobotnya berjumlah 100%, jadi membagi dengan C6 tidak mengubah apa pun di sini, tetapi pembagian itulah yang menjaga rumusnya tetap benar jika jumlah bobotnya tidak 100%.

Bobot yang tidak berjumlah 100%

Bobot tidak harus berupa persen. IPK diberi bobot berdasarkan SKS, harga rata-rata berdasarkan jumlah barang. Membagi dengan SUM dari bobotnya bisa menangani total berapa pun.

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Tanpa pembagian, rumusnya akan mengembalikan jumlah angka mutu dikali SKS, di sini 49,2, bukan IPK. Dengan pembagian, F2 menampilkan IPK yang diberi bobot dari 14 SKS. Mata kuliah 4 SKS menarik rata-ratanya mendekati nilai mereka, sedangkan praktikum 1 SKS hampir tidak menggesernya: ubah B6 menjadi 2 dan lihat betapa sedikitnya F2 berubah dibanding F3.

Jika bobotmu berupa persen yang berjumlah tepat 100%, =SUMPRODUCT(B2:B5,C2:C5) saja sudah memberi hasil yang sama. Tetap pertahankan /SUM(...): begitu ada bobot yang diubah dan totalnya menjadi 105%, rumus tanpa pembagian itu salah dan tidak ada apa pun di sheet yang memberi tahu.

Rata-rata tertimbang dengan kondisi

Untuk memberi bobot hanya pada sebagian baris, kalikan dengan sebuah kondisi di dalam SUMPRODUCT, lalu jumlahkan bobot yang cocok dengan SUMIF. Di bawah ini, harga rata-rata per wilayah diberi bobot berdasarkan jumlah barang yang terjual.

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

North menjual 100 apel, 60 pir dan 40 plum, jadi harga rata-ratanya $1,45, lebih dekat ke harga apel daripada rata-rata biasa dari ketiga harga itu. Kondisi (A2:A7=F2) bernilai 1 di baris North dan 0 di baris lain, jadi baris lain tidak menambah apa pun di bagian pembilang, dan SUMIF hanya menjumlahkan jumlah barang North di bagian penyebut.

Latihan: harga rata-rata tertimbang

RumusReset

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

Klik sel untuk melihat rumusnya. Ubah angka atau rumus, dan lembar kerja langsung menghitung ulang.

Giliranmu: Kamu membeli barang yang sama dalam lima kali pembelian dengan harga yang berbeda. Hitung harga rata-rata per unit, dengan bobot berupa jumlah barang di setiap pembelian. Tulis rumusnya di F2.

PeriksaTampilkan jawaban

Kesalahan yang membuat rata-rata tertimbang salah

• AVERAGE dari hasil kalinya. =AVERAGE(D2:D5) pada kolom skor × bobot membagi dengan jumlah baris, bukan dengan bobotnya, dan menghasilkan angka kecil yang tidak bermakna. Bagi SUM dari hasil kalinya dengan SUM dari bobotnya.
• Membagi dengan jumlah data, bukan dengan bobot. =SUMPRODUCT(B2:B6,C2:C6)/COUNT(B2:B6) hanya benar jika setiap bobotnya 1.
• Range yang tidak sejajar. =SUMPRODUCT(B2:B6,C3:C7) memasangkan setiap nilai dengan bobot di baris berikutnya. Kedua range harus dimulai dan berakhir di baris yang sama; ukuran yang berbeda menghasilkan #VALUE!.
• Bobot yang kosong. Bobot kosong dihitung sebagai 0, jadi baris itu terlewat tanpa pemberitahuan. Jika bobot yang hilang seharusnya menghentikan perhitungan, periksa dulu dengan =COUNTBLANK(C2:C6).
• Merata-ratakan rata-rata. Dua rata-rata kelas, 70 (10 siswa) dan 90 (30 siswa), tidak menghasilkan rata-rata 80. Beri bobot berdasarkan jumlah siswa di setiap kelas dan hasilnya 85; halaman AVERAGEIF punya jebakan yang sama dengan kondisi.