Berapa maximum IF yg bisa digunakan?

  1. 8 bulan yang lalu

    Assalamu'alaikum,

    Kenapa rumus yg saya buat muncul notifikasi "the specified formula cannot be entered because it uses more levels of nesting than are allowed in the current file format"
    Berapakah maximum IF yg bisa digunakan dalam rumus saya di bawah ini? dan bagaimana solusinya?.
    -image-

    =IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6,X13=$AV$6,T13=$AV$6,R13=$AV$6,N13=$AV$6),L13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6,X13=$AV$6,T13=$AV$6,R13=$AV$6),N13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6,X13=$AV$6,T13=$AV$6),R13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6,X13=$AV$6),T13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6),X13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6),Z13,
    IF(AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6),AD13)))))))

  2. Caton

    8 Des 2017 Terverifikasi Indonesia + 10.289 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Informasinya, Excel 2003, 7 tingkat IF. Excel 2007 sampai versi terakhir, 64 tingkat...

  3. @Caton Informasinya, Excel 2003, 7 tingkat IF. Excel 2007 sampai versi terakhir, 64 tingkat...

    makasih untuk info nya, adakah ide lain untuk mengatasi kendala ini, saya mau bikin rumus IF 12 tingkat, karena harus di save ke excel format 2003, karena mau di pakai user open office

  4. Caton

    9 Des 2017 Terverifikasi Indonesia + 10.289 Poin

    Mungkin saja bisa... baiknya upload dahulu contoh datanya, biar rekan-rekan di forum ini bisa lebih mudah membantu memberikan solusinya ;)

  5. Caton

    9 Des 2017 Terverifikasi Indonesia + 10.289 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Ralat sedikit. Mungkin bisa dicoba dahulu dengan menggunakan kombinasi fungsi CHOOSE dan operasi logika, misalnya:

    =CHOOSE(
    (AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6,X13=$AV$6)*1)+
    (AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6,Z13=$AV$6)*1)+
    (AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6,AD13=$AV$6)*1)+
    (AND(AR13=$AV$6,AP13=$AV$6,AL13=$AV$6,AJ13=$AV$6,AF13=$AV$6)*1),
    AD13,Z13,X13,T13)

    Formula di atas saya potong dari formula yang mas @Ridwan Anwar berikan. Coba buang fungsi IF dan diganti sesuai pola formula yang saya contohkan... Mudah-mudahan bisa.

  6. @Caton Mungkin saja bisa... baiknya upload dahulu contoh datanya, biar rekan-rekan di forum ini bisa lebih mudah membantu memberikan solusinya ;)

    sudah saya coba, tapi masih belum bisa, ini saya lampirkan file nya, semoga ada yg bisa bantu :)
    result jika benar
    -image-

    result jika salah
    -image-

  7. Caton

    10 Des 2017 Terverifikasi Indonesia + 10.289 Poin

    Hmm... mas @Ridwan Anwar menyusun formulanya:

    =CHOOSE(
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6;X6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6)*1);
    AD6;Z6;X6;T6)

    pada sel AV6... :( Akibatnya terjadi kesalahan Circular References. Tidak bisa demikian mas. Formula yang mas susun seharusnya ada pada sel yang berbeda dengan sel AV6, karena dalam hal ini sel AV6 akan dijadikan sel acuan operasi logika di dalam formula tersebut.

  8. Caton

    10 Des 2017 Terverifikasi Indonesia + 10.289 Poin

    Hal kedua yang perlu diperhatikan, oleh karena kalkulasi dilakukan menggunakan fungsi CHOOSE, maka jika seluruh operasi logika dalam argumen Index_Number bernilai 0, maka formulanya akan menghasilkan kesalahan. Triknya, tambahkan fungsi IFERROR atau pada formulanya dimodifikasi menjadi:

    =CHOOSE(
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6;X6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6)*1)+1;
    "";AD6;Z6;X6;T6)

    Dengan demikian, nilai terkecil yang dihasilkan oleh operasi logika pada argumen Index_Number adalah 1. Proses dari formula di atas adalah, jika baris pertama operasi AND:

    ...
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6;X6=$AV$6)*1)+
    ...

    bernilai 1 (semua TRUE), maka otomatis hasil operasi AND kedua sampai dengan keempat akan menghasilkan 1 juga. Oleh karena ada 4 kelompok operasi AND, maka operasinya OR (tanda tambah) akan menghasilkan 1 + 1 + 1 + 1 = 4. Guna menghindari nilai 0 (argumen Index_Number pada fungsi CHOOSE harus dimulai dengan angka 1), maka ditambahkan notasi +1. Dengan demikian, fungsi CHOOSE pada formula di atas akan menghasilkan minimal nilai 1 dan maksimal (pada formula di atas) nilai 5. Nilai yang dihasilkan pada argumen Index_Number (kita misalkan untuk operasi AND masing-masing bernilai 1, maka 1 + 1 + 1 + 1 = 4 dan ditambah dengan notasi +1 di bagian akhir maka total menjadi 4 + 1 = 5), digunakan untuk memilih salah satu nilai dari argumen selanjutnya yakni:

    ... ;"";AD6;Z6;X6;T6)

    Jika misalkan nilai pada argumen Index_Number adalah 5, maka fungsi CHOOSE akan mengembalikan nilai pada sel T6. Jika Index_Number = 4, maka hasilnya adalah nilai pada sel X6, dan demikian selanjutnya.

  9. Caton

    10 Des 2017 Terverifikasi Indonesia + 10.289 Poin
    Di sunting 8 bulan yang lalu oleh Caton

    Formula di atas, jika kita teruskan akan menjadi:

    =CHOOSE(
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6;X6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6;Z6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6;AD6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6;AF6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6;AJ6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6;AL6=$AV$6)*1)+
    (AND(AR6=$AV$6;AP6=$AV$6)*1)+
    ((AR6=$AV$6)*1)+1;
    "";AR6;AP6;AJ6;AF6;AD6;Z6;X6;T6)

    Untuk menambah sel selanjutnya yang akan dikomparasi dengan operator AND, maka tambahkan saja notasi (AND( ... )*1)+ pada bagian awal argumen Index_Number, namun sel hasilnya ditambahkan pada argumen Value terakhir: "";AR6;AP6;AJ6;AF6;AD6;Z6;X6;T6; ... ). Fungsi CHOOSE setahu saya bisa sampai 254 pilihan nilai. Selain dengan fungsi CHOOSE, mungkin juga bisa menggunakan kombinasi fungsi INDEX, MATCH dan SUMPRODUCT.

    Namun, karena sudah ada contoh file yang akan dikerjakan, mungkin ada rekan-rekan (mungkin mas @Fujiansyah92 atau mas @Diditsatriyadi) yang dapat memberikan solusi yang lebih tepat dan benar... ;)

  10. diditsatriyadi

    12 Des 2017 Terverifikasi Surabaya + 1.097 Poin

    saya belum pernah coba... menarik juga menggunakan fungsi CHOOSE

 

atau Mendaftar untuk ikut berdiskusi!