Help.... Rumus if..and

  1. tahun lalu
    Di sunting tahun lalu oleh rwinardi

    Maaf newbie mau tanya ke master disini, saya bikin rumus ( if and ) tapi ko' ada error "The specified formula cannot be entered because it uses more than 64 levels of nesting" ada cara lain ga untuk mencari nilai dari tabel berikut

    -image-

    saya pake rumus if berikut :

    =IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=251),$E$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=201,$C$28<=250),$F$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=151,$C$28<=200),$G$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=101,$C$28<=150),$H$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=51,$C$28<=100),$I$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=1,$C$28<=50),$J$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28<=1),$K$6,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=251),$E$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=201,$C$28<=250),$F$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=151,$C$28<=200),$G$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=101,$C$28<=150),$H$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=51,$C$28<=100),$I$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=1,$C$28<=50),$J$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28<=1),$K$7,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=251),$E$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=201,$C$28<=250),$F$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=151,$C$28<=200),$G$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=101,$C$28<=150),$H$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=51,$C$28<=100),$I$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=1,$C$28<=50),$J$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28<=1),$K$8,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=251),$E$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=201,$C$28<=250),$F$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=151,$C$28<=200),$G$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=101,$C$28<=150),$H$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=51,$C$28<=100),$I$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=1,$C$28<=50),$J$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28<=1),$K$9,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=251),$E$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=201,$C$28<=250),$F$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=151,$C$28<=200),$G$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=101,$C$28<=150),$H$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=51,$C$28<=100),$I$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=1,$C$28<=50),$J$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28<=1),$K$10,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=251),$E$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=201,$C$28<=250),$F$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=151,$C$28<=200),$G$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=101,$C$28<=150),$H$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=51,$C$28<=100),$I$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=1,$C$28<=50),$J$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28<=1),$K$11,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=251),$E$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=201,$C$28<=250),$F$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=151,$C$28<=200),$G$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=101,$C$28<=150),$H$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=51,$C$28<=100),$I$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=1,$C$28<=50),$J$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28<=1),$K$12,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=251),$E$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=201,$C$28<=250),$F$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=151,$C$28<=200),$G$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=101,$C$28<=150),$H$13,
    IF(AND($B$28<=4500000000,$B$28>=4000000000,$C$28>=51,$C$28<=100),$I$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=1,$C$28<=50),$J$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28<=1),$K$13,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=251),$E$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=201,$C$28<=250),$F$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=151,$C$28<=200),$G$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=101,$C$28<=150),$H$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=51,$C$28<=100),$I$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=1,$C$28<=50),$J$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28<=1),$K$14,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

    Mohon pencerahannya, terima kasih

    saya tidak paham benar, tapi sepertinya mau mencari nilai dari tabel dengan kriteria baris dan kriteria kolom sendiri ya?
    mungkin bisa digunakan kombinasi INDEX dan 2 MATCH sebagai berikut :

    =INDEX(E6:K25,MATCH(B29,C6:C25,1),MATCH(C29,E5:K5,-1))


    yang maksudnya mencari data dari tabel E6:K25 untuk baris yang memenuhi kriteria B29 (outstanding) dan kolom yang memenuhi kriteria C29 (Net AM)
    yang perlu diperhatikan adalah cara penggunaan MATCH untuk range tertentu, yg pertama mencari B29 pada range C6:C25 menggunakan tipe match 1 (range maju) sedang yg kedua mencari C29 pada range E5:K5 dengan tipe match -1 (range mundur)
    tapi untuk lebih jelasnya silakan dilihat pada excel berikut, formulanya saya taruh di sel E29

  2. apakah agan pakai excel 2007 keatas?
    klo iya,silahkan saveas ke format .xlsx lalu close dan re-open
    atau agan bisa membuat db array dan lookup rangenya

    thanks

    note: karna untuk excel 2003 ke bawah limitnya +- sampai 8 if saja

  3. iya gan kalo sampe 8 bisa gan, karena sebelumnya juga sampe 12 if masih bisa... kalo pake db array atau lookup bisa kasih contohnya ga gan, soalnya saya bingungnya ada 2 parameter, dari tabel di atas ada berdasarkan outstanding sama berdasarkan net anggota masuk, makasih juga masukannya...

  4. pertanyaan ane jawab dulu
    apakah agan pakai excel 2007 keatas atau masih 2003 ?

    lalu coba upload datanya

  5. Di sunting tahun lalu oleh rwinardi

    Maaf gan ane pake excell 2007 pake format file. Xlsx

  6. bisa upload filenya gan?

  7. diditsatriyadi

    30 Mar 2017 Terverifikasi Surabaya + 1.103 Poin

    waduh panjang banget formulanya :o

  8. itu file nya, maaf gan

  9. diditsatriyadi

    30 Mar 2017 Terverifikasi Jawaban Terpilih Surabaya + 1.103 Poin

    saya tidak paham benar, tapi sepertinya mau mencari nilai dari tabel dengan kriteria baris dan kriteria kolom sendiri ya?
    mungkin bisa digunakan kombinasi INDEX dan 2 MATCH sebagai berikut :

    =INDEX(E6:K25,MATCH(B29,C6:C25,1),MATCH(C29,E5:K5,-1))


    yang maksudnya mencari data dari tabel E6:K25 untuk baris yang memenuhi kriteria B29 (outstanding) dan kolom yang memenuhi kriteria C29 (Net AM)
    yang perlu diperhatikan adalah cara penggunaan MATCH untuk range tertentu, yg pertama mencari B29 pada range C6:C25 menggunakan tipe match 1 (range maju) sedang yg kedua mencari C29 pada range E5:K5 dengan tipe match -1 (range mundur)
    tapi untuk lebih jelasnya silakan dilihat pada excel berikut, formulanya saya taruh di sel E29

  10. @rwinardi Maaf newbie mau tanya ke master disini, saya bikin rumus ( if and ) tapi ko' ada error "The specified formula cannot be entered because it uses more than 64 levels of nesting" ada cara lain ga untuk mencari nilai dari tabel berikut

    -image-

    saya pake rumus if berikut :

    =IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=251),$E$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=201,$C$28<=250),$F$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=151,$C$28<=200),$G$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=101,$C$28<=150),$H$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=51,$C$28<=100),$I$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28>=1,$C$28<=50),$J$6,
    IF(AND($B$28<=1000000000,$B$28>500000000,$C$28<=1),$K$6,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=251),$E$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=201,$C$28<=250),$F$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=151,$C$28<=200),$G$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=101,$C$28<=150),$H$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=51,$C$28<=100),$I$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28>=1,$C$28<=50),$J$7,
    IF(AND($B$28<=1500000000,$B$28>1000000000,$C$28<=1),$K$7,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=251),$E$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=201,$C$28<=250),$F$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=151,$C$28<=200),$G$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=101,$C$28<=150),$H$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=51,$C$28<=100),$I$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28>=1,$C$28<=50),$J$8,
    IF(AND($B$28<=2000000000,$B$28>1500000000,$C$28<=1),$K$8,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=251),$E$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=201,$C$28<=250),$F$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=151,$C$28<=200),$G$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=101,$C$28<=150),$H$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=51,$C$28<=100),$I$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28>=1,$C$28<=50),$J$9,
    IF(AND($B$28<=2500000000,$B$28>2000000000,$C$28<=1),$K$9,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=251),$E$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=201,$C$28<=250),$F$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=151,$C$28<=200),$G$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=101,$C$28<=150),$H$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=51,$C$28<=100),$I$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28>=1,$C$28<=50),$J$10,
    IF(AND($B$28<=3000000000,$B$28>2500000000,$C$28<=1),$K$10,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=251),$E$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=201,$C$28<=250),$F$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=151,$C$28<=200),$G$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=101,$C$28<=150),$H$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=51,$C$28<=100),$I$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28>=1,$C$28<=50),$J$11,
    IF(AND($B$28<=3500000000,$B$28>3000000000,$C$28<=1),$K$11,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=251),$E$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=201,$C$28<=250),$F$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=151,$C$28<=200),$G$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=101,$C$28<=150),$H$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=51,$C$28<=100),$I$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28>=1,$C$28<=50),$J$12,
    IF(AND($B$28<=4000000000,$B$28>3500000000,$C$28<=1),$K$12,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=251),$E$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=201,$C$28<=250),$F$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=151,$C$28<=200),$G$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=101,$C$28<=150),$H$13,
    IF(AND($B$28<=4500000000,$B$28>=4000000000,$C$28>=51,$C$28<=100),$I$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28>=1,$C$28<=50),$J$13,
    IF(AND($B$28<=4500000000,$B$28>4000000000,$C$28<=1),$K$13,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=251),$E$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=201,$C$28<=250),$F$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=151,$C$28<=200),$G$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=101,$C$28<=150),$H$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=51,$C$28<=100),$I$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28>=1,$C$28<=50),$J$14,
    IF(AND($B$28<=5000000000,$B$28>4500000000,$C$28<=1),$K$14,0)))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))))

    Mohon pencerahannya, terima kasih

    formulanya greget..hahaha, saya setuju sama mas didit pakai index match aja..hehe

  11. Di sunting tahun lalu oleh rwinardi

    Ok, sudah bener sekarang. makasih buat Agan diditsatriyadi dan agan Fujiansyah92 udah share ilmunya.

 

atau Mendaftar untuk ikut berdiskusi!