=AVERAGEIF(A2:A7,"North",C2:C7) menghitung rata-rata penjualan di C2:C7 pada baris yang kolom A-nya North. Cara kerjanya mirip SUMIF, hanya saja totalnya dibagi dengan banyaknya baris yang cocok.
| 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 |
F2 menghitung rata-rata tiga baris North, yaitu 120, 110, dan 40, lalu menampilkan 90. F3 butuh dua kondisi, North dan Apple, jadi memakai AVERAGEIFS: (120 + 40) / 2 = 80. F4 tidak punya rentang rata-rata terpisah, jadi yang dihitung rata-ratanya adalah nilai penjualan yang cocok itu sendiri.
Sintaks AVERAGEIF dan AVERAGEIFS
=AVERAGEIF(range, criteria, [average_range])
=AVERAGEIFS(average_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)
Urutan argumennya menyimpan jebakan yang sama dengan SUMIF dan SUMIFS: AVERAGEIF menaruh rentang yang dirata-ratakan di posisi terakhir (dan boleh dihilangkan), sedangkan AVERAGEIFS menaruhnya di posisi pertama. Kriteria ditulis dengan cara yang sama di keduanya: "North", ">50", "<>0", "*apple*", atau operator yang digabung dengan sel, ">"&F5. Sel kosong dan teks di rentang rata-rata dilewati.
Rata-rata yang mengabaikan nol
AVERAGE menghitung 0 sebagai nilai, jadi dua siswa yang tidak hadir dengan nilai 0 menurunkan rata-rata kelas. Sel kosong berbeda: AVERAGE melewatinya. =AVERAGEIF(B2:B7,"<>0") juga melewati nol.
| 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 |
AVERAGE membagi 240 dengan 5, karena sel kosong milik Dan dilewati tetapi kedua nol tetap dihitung, lalu menampilkan 48. AVERAGEIF dengan "<>0" membagi 240 dengan 3 dan menampilkan 80. Ketik 60 di B5 dan keduanya berubah; ketik 0 di B5 dan hanya AVERAGE yang bergeser. Untuk mengabaikan nol sekaligus angka negatif, pakai ">0".
Kenapa AVERAGEIF menghasilkan #DIV/0!
Kalau tidak ada yang cocok, AVERAGEIF tidak punya pembagi dan menghasilkan #DIV/0!. Bungkus dengan IFERROR untuk menampilkan tanda strip, pesan, atau sel kosong sebagai gantinya.
| 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! Rumus membagi dengan nol atau dengan sel kosong.Tidak ada baris West, jadi F2 menampilkan #DIV/0! dan F3 menampilkan pesannya. Ubah A3 menjadi West dan keduanya menampilkan 45.
MAXIFS dan MINIFS
F4 sampai F6 di lembar kerja di atas mencari nilai terbesar dan terkecil dengan kondisi. Keduanya memakai urutan AVERAGEIFS, dengan rentang yang dicari di posisi pertama: =MAXIFS(C2:C7,A2:A7,"North") menghasilkan 120 dan =MINIFS(C2:C7,A2:A7,"North") menghasilkan 40. Berbeda dengan AVERAGEIF, keduanya menghasilkan 0 kalau tidak ada yang cocok, bukan error.
MAXIFS dan MINIFS butuh Excel 2019 atau yang lebih baru, atau Microsoft 365. Di Excel 2016 dan versi sebelumnya, =MAX(IF(A2:A7="North",C2:C7)) melakukan hal yang sama; tekan Ctrl+Shift+Enter (Cmd+Shift+Enter di Mac) untuk memasukkannya di versi tersebut.
Latihan: rata-rata dengan dua kondisi
| 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 |
Giliranmu: Hitung rata-rata nilai kelas A, tanpa menyertakan nol (siswa yang absen). Tulis rumusnya di F2.
Rata-rata dari rata-rata: kesalahan yang umum
Merata-ratakan rata-rata dari kelompok yang ukurannya berbeda menghasilkan rata-rata keseluruhan yang salah. North punya tiga baris dan South dua, jadi setiap baris South punya bobot lebih besar dari seharusnya dalam rata-rata dari kedua rata-rata itu.
| 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 |
E4 menampilkan 105, sedangkan E5 menampilkan rata-rata sebenarnya dari kelima baris, yaitu 102. Kalau ukuran kelompoknya berbeda, hitung rata-rata barisnya langsung dengan satu AVERAGEIFS, atau bagi SUMIFS dengan COUNTIFS yang memakai kondisi yang sama:
=SUMIFS(B2:B6,A2:A6,"North")/COUNTIFS(A2:A6,"North")
Nilai yang diberi bobot berdasarkan SKS atau jumlah barang adalah perhitungan yang lain lagi: itu namanya rata-rata tertimbang.
Pertanyaan yang sering diajukan
Apa beda AVERAGEIF dan AVERAGEIFS?
AVERAGEIF menerima satu kondisi dan menaruh rentang rata-rata di posisi terakhir: =AVERAGEIF(A2:A7,"North",C2:C7). AVERAGEIFS menerima beberapa kondisi dan menaruh rentang rata-rata di posisi pertama: =AVERAGEIFS(C2:C7,A2:A7,"North",B2:B7,"Apple").
Bagaimana cara menghitung rata-rata di Excel tanpa menyertakan nol?
Pakai =AVERAGEIF(B2:B7,"<>0"). Rumus ini hanya menghitung rata-rata sel yang bukan 0. Sel kosong sudah dilewati oleh AVERAGE dan AVERAGEIF, jadi hanya nol sungguhan yang butuh kondisi ini.
Kenapa AVERAGEIF menghasilkan #DIV/0!?
Tidak ada sel yang memenuhi kondisi, jadi Excel membagi jumlah 0 dengan banyak data 0. Bungkus rumusnya untuk menampilkan hal lain: =IFERROR(AVERAGEIF(A2:A7,"West",C2:C7),"No data").
Bagaimana cara mencari nilai maksimum dengan kondisi?
Pakai MAXIFS, dengan rentang yang dicari di posisi pertama: =MAXIFS(C2:C7,A2:A7,"North") menghasilkan nilai North terbesar. MINIFS bekerja dengan cara yang sama untuk nilai terkecil. Keduanya butuh Excel 2019 atau yang lebih baru, atau Microsoft 365.
