Rumus AVERAGEIF: cara menghitung rata-rata dengan syarat
AVERAGEIF seperti AVERAGE, tapi hanya memasukkan baris yang cocok dengan syarat. Cocok untuk rata-rata per sales, per kategori, atau per status.
Susunan argumennya sama dengan SUMIF, jadi kalau kamu sudah paham yang itu, ini tinggal mengganti nama.
Sintaks
=AVERAGEIF(rentang_syarat; syarat; [rentang_rata])
rentang_syarat: Kolom yang diperiksa.syarat: Nilai atau pernyataan yang dicari.rentang_rata: Kolom angka yang dirata-rata. Kalau dikosongkan, rentang syarat yang dirata-rata.
Contoh dan hasilnya
| Sales | Produk | Qty | Harga | Total | Status |
|---|---|---|---|---|---|
| Andi | Dripper V60 | 2 | 120.000 | 240.000 | Lunas |
| Budi | Grinder Manual | 1 | 275.000 | 275.000 | Belum Lunas |
| Citra | Biji Kopi Arabika 250g | 3 | 85.000 | 255.000 | Lunas |
| Andi | Filter Paper 50 lembar | 4 | 45.000 | 180.000 | Lunas |
| Dewi | Timbangan Digital | 1 | 180.000 | 180.000 | Belum Lunas |
| Rumus | Hasil | Penjelasan |
|---|---|---|
=AVERAGEIF(F2:F6; "Lunas"; E2:E6) | 225.000 | Tiga transaksi Lunas: (240.000 + 255.000 + 180.000) dibagi 3. |
Kapan dipakai
Pemilik toko membandingkan nilai transaksi per sales untuk melihat siapa yang biasanya menutup penjualan lebih besar. =AVERAGEIF(A2:A6; "Andi"; E2:E6) memberi 210.000 dari dua transaksinya, sedangkan Budi hanya satu transaksi sebesar 275.000. Hati-hati menafsirkannya: rata-rata dari satu atau dua transaksi masih terlalu tipis untuk menilai orang. Perbandingan yang lebih masuk akal ada di status: rata-rata Belum Lunas 227.500 berbanding Lunas 225.000 menunjukkan bahwa transaksi yang belum dibayar tidak otomatis lebih kecil.
Kalau syaratmu lebih dari satu, misalnya rata-rata transaksi Lunas yang nilainya minimal 200.000, pakai AVERAGEIFS. Kalau datanya punya satu nilai ekstrem, rata-rata bisa menyesatkan, dan MEDIAN bisa lebih mewakili. Rata-rata juga menyesatkan kalau jumlah barisnya sangat berbeda antarkelompok. Tampilkan jumlah barisnya di sebelah rata-rata dengan COUNTIF, supaya pembaca tahu seberapa kuat angkanya.
Langkah memakainya
- Ketik =AVERAGEIF(A2:A6; "Andi"; E2:E6) di sel H2. Hasilnya 210.000, dari (240.000 + 180.000) dibagi 2.
- Ketik =AVERAGEIF(F2:F6; "Belum Lunas"; E2:E6) di H3. Hasilnya 227.500, dari (275.000 + 180.000) dibagi 2.
- Bandingkan dengan rata-rata transaksi Lunas di contoh atas halaman, yaitu 225.000, dan rata-rata semua transaksi, yaitu 226.000.
- Cek silang rumus H3 dengan cara lain: =SUMIF(F2:F6; "Belum Lunas"; E2:E6)/COUNTIF(F2:F6; "Belum Lunas"). Hasilnya 455.000 dibagi 2, juga 227.500.
- Coba dua syarat: =AVERAGEIFS(E2:E6; F2:F6; "Lunas"; E2:E6; ">=200000"). Hasilnya 247.500, dari 240.000 dan 255.000.
- Tampilkan jumlah transaksinya di sebelah rata-rata: =COUNTIF(A2:A6; "Andi") menghasilkan 2, jadi pembaca tahu angka 210.000 berasal dari dua baris.
Kesalahan yang sering muncul
- Tidak ada baris yang cocok: hasilnya #DIV/0!. Bungkus dengan IFERROR kalau itu mungkin terjadi.
- Rentang syarat dan rentang rata berbeda ukuran.
Bedanya dengan rumus lain
AVERAGE merata-ratakan semuanya. AVERAGEIF membatasi pada satu syarat. AVERAGEIFS membatasi pada beberapa syarat, dan seperti SUMIFS, kolom yang dirata-rata ditulis paling depan. Satu hubungan berguna untuk diingat: AVERAGEIF sama dengan SUMIF dibagi COUNTIF dengan syarat yang sama. Itu sebabnya hasil tanpa baris cocok adalah #DIV/0!, karena pembaginya nol. Pilih AVERAGEIF untuk satu kelompok, dan AVERAGEIFS bila kelompoknya ditentukan oleh dua kolom atau lebih. Pada tabel contoh selisih antarkelompok kecil karena datanya hanya lima baris. Pada data ratusan baris, perbedaannya baru terlihat jelas. Untuk tiga syarat atau lebih, AVERAGEIFS tetap dipakai dengan menambah pasangan rentang dan syarat di belakangnya.
Di Excel dan Google Sheets
Sama di Excel dan Google Sheets.
Pertanyaan yang sering muncul
Bagaimana cara merata-ratakan hanya angka yang bukan nol?
Pakai syarat pada rentang yang sama: =AVERAGEIF(E2:E6; ">0"). Karena argumen ketiga dikosongkan, yang dirata-rata adalah rentang syaratnya sendiri. Pada kolom Total tabel contoh semua angka di atas nol, jadi hasilnya sama dengan AVERAGE biasa, yaitu 226.000.
Kenapa hasil AVERAGEIF saya #DIV/0!?
Tidak ada baris yang cocok dengan syaratmu. Periksa ejaan, spasi tersembunyi, dan apakah syarat angkanya ditulis dalam tanda kutip. Kalau kasus kosong memang mungkin terjadi, bungkus dengan IFERROR. Satu huruf yang berbeda di nama saja sudah cukup untuk membuat hasilnya error.
Apakah sel kosong di kolom yang dirata-rata dihitung?
Tidak. Sel kosong di rentang rata-rata dilewati, sedangkan sel berisi 0 tetap dihitung. Bedakan antara data yang belum diisi dan data yang memang nol. Kalau sebuah baris sengaja dikosongkan, rata-rata tidak terpengaruh. Teks di kolom rata-rata juga dilewati, jadi catatan tambahan di kolom itu tidak merusak hasil.
Rumus terkait
- AVERAGE: Menghitung rata-rata dari sekumpulan angka.
- SUMIF: Menjumlahkan angka hanya untuk baris yang memenuhi syarat.
- IFERROR: Mengganti pesan error dengan nilai yang kamu tentukan.
Rumus lain di modul yang sama
- COUNTIF: Menghitung berapa sel yang memenuhi satu syarat.
- COUNTIFS dan SUMIFS: COUNTIF dan SUMIF dengan banyak syarat sekaligus.