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.

SIMULASI.xls

Sepertinya ada kesalahan dalam upload file, terlampir file dengan rumus yang saya gunakan sebelumnya..SIMULASI.xls

@Caton @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... ;)

Mas @caton terimakasih untuk pencerahannya. Berikut yang ingin saya klarifikasikan :

1. Aplikasi yang saya gunakan adalah openoffice 4.0
2. Saya sudah coba gunakan saran mas yang terakhir (menggunakan INDEX dan MAX), munculnya seperti ini (Eror 504) :

jika disamakan rumusnya ada perbedaan, dimana kalo dari mas @caton punya sebelum eror adalah true, sedangkan di saya jadinya malah angka 1, seperti ini :

Mohon bantuannya.
Salam,

@Caton Begini mas @Siriki... :)

Formula tersebut sudah saya coba dengan menggunakan:
[1]. OpenOffice Calc 4.1.5
[2]. Microsoft Excel 2010 SP2
[3]. WPS Spreadsheet 10.2

dan semuanya berfungsi tanpa kesalahan... :) Pada OpenOffice, Error 504 = Parameter list error (Parameter fungsi tidak valid, misalnya teks bukan angka, atau referensi domain, bukan referensi sel). Perlu diketahui, penjelasan saya di atas menggunakan Excel. Pada OpenOffice Calc, argumen TRUE merupakan fungsi, sehingga formulanya menjadi:

=IF(MAX($Q$2:$Q$15)>0;HLOOKUP($D$12;$K$1:$O$25;MAX($Q$2:$Q$15);TRUE());"ERROR!")

atau bisa juga menggunakan angka 1 sebagai pengganti TRUE(). Coba diperiksa dari file terlampir...

Siaap mas,,coba saya pelajari dulu yaa..Terimakasih