Menghitung Insentif dengan Omset Berkala Menggunakan Ifs/Nested If

Saya pernah ditanya bagaimana caranya memudahkan input pendapatan insentif di excel supaya tim penjualan punya data sendiri agar bisa banding data dengan administrasi jika ada permasalahan.

Hitungan insentif tersebut berkala, dan berlaku untuk target harian yang nanti akan dibayarkan sebulan sekal

Perhitungannya adalah sebagai berikut:

Setiap pencapain omset 500..000 sampai dengan dibawah 800.000 mendapatkan insentif harian 20.000. Omset 800.000 sampai di bawah 1.000.000 mendapatkan 30.000. Omset 1.000.000 sampai dibawah 1.200.000 mendapatkan insentif harian sebesar 40.000. Omset 1.200.000 sampai dibawah 1.500.000 mendapatkan insentif harian sebesar 45.000. Omset 1.500.000 sampai dibawah 1.700.000 mendapatkan insetif harian sebesar 60.000. Omset 1.700.000 sampai di bawah 2.000.000 mendapatkan insentif sebesar 70.000. Omset 2.000.000 sampai dengan dibawah 2.500.000 mendapatkan insentif sebesar 80.000. Dan untuk omset 2.500.000 ke atas mendapatkan insentif harian sebesar 100.000

Dia mengirimkan file sebagai berikut:

Untuk cara ada dua, karena di excel lama Ifs tidak berfungsi jadi menggunakan nested IF. Untuk lebih singkat mengunakan cara Ifs, jika versi excelnya yang baru.

Dan sebagai catatan ini untuk pengaturan excel yang berbahasa Indonesia ya. Untuk pemisah variabel menggunakan titik koma (;) bukan koma(,). Kalau menggunakan bahasa Ingris baru menggunakan koma(,) jadi silahkan sesuaikan dengan setingannya excel masing-masing.

Penjelasan yang pertama, data omset ada di kolom Omset Kotor dimana celnya D barisnya 6 yang berarti cell D6.

Berikut rumus yang menggunakan menggunakan nested IF untuk excel versi lama, kita isikan di kolom Insentif kolom E bari 6 atau cell E6.

=IF(NOT(ISNUMBER(D6));0;
 IF(AND(D6>=500000;D6<800000);20000;
 IF(AND(D6>=800000;D6<1000000);30000;
 IF(AND(D6>=1000000;D6<1200000);40000;
 IF(AND(D6>=1200000;D6<1500000);45000;
 IF(AND(D6>=1500000;D6<1700000);60000;
 IF(AND(D6>=1700000;D6<2000000);70000;
 IF(AND(D6>=2000000;D6<2500000);80000;
 IF(D6>=2500000;100000;0)))))))))

Untuk kolom insentif selanjut tinggal dicopykan saja nanti cell D6 akan berubah sendiri menjadi D7 dan seterusnya.

·  IF(NOT(ISNUMBER(D6));0;...)
→ Guard clause: kalau D6 bukan angka (mis. “LIBUR”, “Rp510.000” sebagai teks, atau kosong) → langsung 0. Penting supaya teks seperti “LIBUR” tidak dipaksa diproses sebagai angka.

·  IF(AND(D6>=500000;D6<800000);20000; ...)
→ Jika omset antara 500.000 sampai kurang dari 800.000 → insentif 20.000.

·  IF(AND(D6>=800000;D6<1000000);30000; ...)
→ Jika omset 800.000 sampai <1.000.000 → insentif 30.000.

·  (dan seterusnya sampai rentang terakhir)

·  IF(D6>=2500000;100000;0)
→ Jika ≥2.500.000 → 100.000; kalau tidak jatuh ke sini, berarti 0.

Jika menggunakan Ifs sebagai berikut:

=IFS(
 NOT(ISNUMBER(D6));0;
 AND(D6>=500000;D6<800000);20000;
 AND(D6>=800000;D6<1000000);30000;
 AND(D6>=1000000;D6<1200000);40000;
 AND(D6>=1200000;D6<1500000);45000;
 AND(D6>=1500000;D6<1700000);60000;
 AND(D6>=1700000;D6<2000000);70000;
 AND(D6>=2000000;D6<2500000);80000;
 D6>=2500000;100000;
 D6<500000;0
)

·  IFS(…)

• Fungsi IFS mengecek kondisi satu-per-satu. Bila menemukan kondisi pertama yang TRUE, IFS langsung mengembalikan nilai yang terkait dan berhenti memeriksa kondisi berikutnya.
• Setiap pasangan kondisi ; hasil harus berurutan.

·  NOT(ISNUMBER(D6));0 (baris pertama)

• ISNUMBER(D6) = memeriksa apakah isi D6 adalah angka.
• Jika D6 berisi teks seperti “LIBUR” atau “Rp510.000” (format teks), ISNUMBER jadi FALSE.
• NOT(…) membaliknya → jadi TRUE kalau D6 bukan angka.
• Jadi baris ini: jika D6 bukan angka → kembalikan 0 (tidak ada insentif).

·  AND(D6>=500000;D6<800000);20000 (dan baris-baris AND berikutnya)

• AND(…) memastikan dua kondisi benar sekaligus (mis. D6 >= 500000 dan D6 < 800000).
• Jika AND benar, IFS mengembalikan angka insentif yang sesuai (di contoh ini 20000).
• Ada beberapa baris AND(…) untuk setiap rentang omset.

·  D6>=2500000;100000

• Ini menangani semua nilai sama atau di atas 2.500.000 → insentif 100.000.

·  D6<500000;0 (baris terakhir)

• Ini sebagai cadangan: bila semua kondisi sebelumnya tidak terpenuhi dan nilai kurang dari 500.000 → hasil 0.
• Dengan kata lain: nilai omset kecil → tidak dapat insentif.

Download filenya di sini.