Formula umum
=SUMPRODUCT(weights,values)/SUM(weights)
Ringkasan
Untuk menghitung rata-rata tertimbang, Anda bisa menggunakan fungsi SUMPRODUCT bersama dengan fungsi SUM. Dalam contoh yang ditunjukkan, rumus di G5, disalin ke bawah, adalah:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
dimana bobot adalah rentang bernama I5: K5.
Penjelasan
Rata-rata tertimbang, juga disebut rata-rata tertimbang, adalah rata-rata di mana beberapa nilai lebih dihitung daripada yang lain. Dengan kata lain, beberapa nilai memiliki lebih banyak "bobot". Kita dapat menghitung rata-rata tertimbang dengan mengalikan nilai menjadi rata-rata dengan bobot yang sesuai, lalu membagi jumlah hasil dengan jumlah bobot. Di Excel, ini dapat direpresentasikan dengan rumus umum di bawah ini, di mana bobot dan nilai adalah rentang sel:
=SUMPRODUCT(weights,values)/SUM(weights)
Di lembar kerja yang diperlihatkan, skor untuk 3 tes muncul di kolom C hingga E, dan bobot ada di kisaran bobot bernama (I5: K5). Rumus di sel G5 adalah:
=SUMPRODUCT(weights,C5:E5)/SUM(weights)
Bekerja dari dalam ke luar, pertama-tama kami menggunakan fungsi SUMPRODUCT untuk mengalikan bobot dengan skor yang sesuai dan menjumlahkan hasilnya:
=SUMPRODUCT(weights,C5:E5) // returns 88.25
SUMPRODUCT terlebih dahulu mengalikan elemen yang sesuai dari dua larik bersama-sama, lalu mengembalikan jumlah produk:
=SUMPRODUCT((0.25,0.25,0.5),(90,83,90)) =SUMPRODUCT((22.5,20.75,45)) =88.25
Hasilnya kemudian dibagi dengan jumlah bobot:
=88.25/SUM(weights) =88.25/SUM((0.25,0.25,0.5)) =88.25/1 =88.25
Saat rumus disalin ke kolom G, kisaran bobot I5: K5 tidak berubah, karena berperilaku seperti referensi absolut. Namun, skor di C5: E5, dimasukkan sebagai referensi relatif, diperbarui di setiap baris baru. Hasilnya adalah rata-rata tertimbang untuk setiap nama dalam daftar seperti yang ditunjukkan. Rata-rata di kolom F dihitung untuk referensi hanya dengan fungsi AVERAGE:
=AVERAGE(C5:E5)
Bobot yang tidak berjumlah 1
Dalam contoh ini, bobot dikonfigurasikan untuk dijumlahkan menjadi 1, sehingga pembagi selalu 1, dan hasilnya adalah nilai yang dikembalikan oleh SUMPRODUCT. Namun, fitur bagus dari rumus ini adalah bahwa bobot tidak perlu dijumlahkan menjadi 1.
Misalnya, kita bisa menggunakan bobot 1 untuk dua pengujian pertama dan bobot 2 untuk final (karena bobot final dua kali lebih penting) dan bobot rata-rata akan sama:
Di sel G5, rumus diselesaikan seperti ini:
=SUMPRODUCT(weights,C5:E5)/SUM(weights) =SUMPRODUCT((1,1,2),(90,83,90))/SUM(1,1,2) =SUMPRODUCT((90,83,180))/SUM(1,1,2) =353/4 =88.25
Catatan: nilai dalam kurung kurawal () di atas adalah rentang yang diekspresikan sebagai array.
Transposing bobot
Fungsi SUMPRODUCT mengharuskan dimensi array kompatibel. Jika dimensi tidak kompatibel, SUMPRODUCT akan mengembalikan kesalahan #VALUE. Pada contoh di bawah ini, bobotnya sama dengan contoh asli, tetapi dicantumkan dalam rentang vertikal:
Untuk menghitung rata-rata tertimbang dengan rumus yang sama, kita perlu "membalik" bobot menjadi larik horizontal dengan fungsi TRANSPOSE seperti ini:
=SUMPRODUCT(TRANSPOSE(weights),C5:E5)/SUM(weights)
Setelah TRANSPOSE dijalankan, array vertikal:
=TRANSPOSE((0.25;0.25;0.5)) // vertical array
menjadi:
=(0.25,0.25,0.5) // horizontal array
Dan dari titik ini, rumus berperilaku seperti sebelumnya.
Baca lebih lanjut: array vertikal dan horizontal.
