Selamat Siang Juragan Excel,
Mohon bantuannya, saya ada kendala dalam membuat simulasi hitung angsuran. Jadi, saya berencana membuat rate bunga otomatis pada cell B20, parameternya ada banyak yaitu cell B3, B4, B5, B7 dan D1. Untuk tabel pengisian cell 20 ada pada G1 s/d O 25 saya sudah mencoba menggunakan rumus if digabung dengan and, tapi sepertinya ada yang salah sehingga muncul err 512. Mungkin juragan sekalian ada solusinya.
Terlampir filenya.
Terimakasih.
Terjawab oleh Caton
Lihat Jawaban terkait@Siriki...
Sepertinya kesalahan ini terjadi pada OpenOffice Calc (Error 512 = Formula Overflow) ya? Karena pada aplikasi Microsoft Excel, kesalahan ini dimunculkan dalam bentuk berbeda. Dan karena ekstensi file yang dilampirkan merupakan format dokumen Excel 2003, saya akan membahas masalah ini berdasarkan aplikasi Microsoft Excel saja.
Pada file yang mas @Siriki lampirkan di atas, terdapat beberapa kesalahan yang harus diperbaiki. Berikut saya urai kesalahan apa saja pada formula tersebut:
1. — Kesalahan awal ada pada notasi 0.25
pada fungsi AND yang digunakan. Dari informasi formula yang akan digunakan (sel A22), saya anggap PC yang mas @Siriki gunakan menggunakan pengaturan Regional Setting Indonesia (terlihat dari penggunaan tanda pembatas titik koma). Dengan pengaturan Regional Setting tersebut, maka nilai angka berbasis desimal harus dipisahkan dengan tanda koma, bukan titik.
2. — Perbaikan dari kesalahan tersebut di atas, akan membawa kesalahan seperti berikut:
[attachment:5a89871fec942]
Ini adalah kesalahan utamanya, yakni penggunaan fungsi IF bersarang (Nested IF) yang melebihi batas maksimum yang didukung. Pada Excel 2003, maksimum fungsi IF bersarang hanya sampai dengan 7 tingkat saja, sedangkan pada Excel 2007 sampai versi terakhir, mendukung maksimum fungsi IF bersarang sampai dengan 64 tingkat.
Pada Excel 2010 yang saya gunakan, pesan kesalahan di atas terjadi oleh karena file yang saya buka masih dalam mode kompatibiltas (Excel 2010 menganggap file tersebut merupakan dokumen Excel 2003), sehingga Excel secara otomatis akan mengkondisikan formula yang digunakan sesuai dengan versi dokumen tersebut disimpan. Oleh karena Excel 2003 membatasi tingkatan fungsi IF bersarang hanya sampai 7 tingkatan saja, maka Excel 2010 yang saya gunakan mengkondisikan hal yang sama terhadap fungsi IF yang digunakan tersebut. Alhasil, muncullah pesan kesalahan tersebut.
Solusinya, jika mas @Siriki bisa menggunakan Excel 2007+ (tanda plus menandakan mulai versi 2007 sampai versi terakhir), coba simpan dokumen ke dalam format Excel Workbook (XLSX), tutup lalu buka kembali dokumen yang sudah disimpan dalam format Excel 2007+ tersebut. Maka jika tidak ada masalah lainnya, sampai tahap ini, masalah batas maksimum fungsi IF tersebut akan selesai, kecuali fungsi IF tersebut kemudian dibuat bersarang lebih dari 64 tingkat.
Jika mas @Siriki masih menggunakan Excel 2003 atau OpenOffice Calc, ada beberapa solusi yang dapat dilakukan, yakni:
[A] Membagi fungsi IF bersarang tersebut per 7 tingkatan, dan kemudian menggabungkannya dengan operasi penjumlahan (+). Solusi ini berhasil pada Excel namun tidak pada OpenOffice Calc.
[attachment:5a89bc562ad30]
[B] Menggunakan fungsi CHOOSE sebagaimana yang pernah saya bahas pada diskusi di sini. Solusi ini mungkin tidak akan berhasil juga pada OpenOffice Calc.
[C] Membuat tabel bantu indeks baris untuk fungsi HLOOKUP, misalkan saja seperti tampak pada gambar berikut:
[attachment:5a89b1b036599]
Pada gambar di atas, setiap baris pada range Q2:Q15 menggunakan fungsi AND untuk menguji kondisi yang diinginkan kemudian hasilnya dikonversikan menjadi indeks baris yang dituju dengan notasi *n
(misalkan notasi *2
yang berguna untuk menghasilkan nilai indeks baris ke-2 pada range K1:O25). Solusi ini berhasil pada OpenOffice Calc.
Saya sendiri lebih suka menggunakan solusi terakhir ini dikarenakan mudah untuk disusun dan dikembangkan (dapat ditambahkan kondisi-kondisi lainnya, bahkan sampai ribuan kondisi kalau perlu) dan juga mempersingkat formulasi pada sel B20.
3. — Kesalahan minor lainnya adalah sel rujukan yang salah atau tidak tepat, seperti rujukan ke sel D17 (seharusnya ke sel B17), rujukan ke sel F12 (seharusnya ke sel D12), rujukan ke range V11:Z35 (seharusnya ke range K1:O25). Namun ini hanya kesalahan minor saja.
Demikian kesalahan yang saya temukan dan solusi yang dapat saya berikan. Semoga bermanfaat... ;)