Two thumb up buat Mbak Siti Vi, Terima kasih. Allah bless you On 5/13/10, siti Vi <[email protected]> wrote: > > > Great idea, pak Ben ...!! > > Fungsi SMALL gagal ?! > mungkin karena anda mencoba men-small array data yg BUKAN BILANGAN > (tetapi TEXT seperti bilangan; inga'..inga'.. : 5 bukan "5") > Semua fungsi pengolah teks (left, right, mmid, text dsb) menghasilkan > data string walaupun karakternya berupa angka. > > Rumus bantuan2 bisa diperingkas > =SMALL(IF(E8:E79=--TEXT(G7,"YYYYMMDD"),ROW(1:72)),ROW(1:72)) > > double minus itu mengkonversi [text seperti bilangan] menjadi [bilangan] > sebagai pengganti fungsi Value. > > ________________________________ >> >> ----- Original Message ----- >> From: benny ismanto >> To: [email protected] >> Sent: Thursday, May 13, 2010 7:38 PM >> Subject: Re: [belajar-excel] Pengambilan Data >> >> Terima kasih, Mbak. >> >> Berhubung nilai datanya tidak mesti berupa integer maka : >> >> formula penghasil HASIL diubah menjadi : >> =IF(ISNUMBER($F8),--(OFFSET($E$7,$F8,0),"") dan hasilnya OK >> >> berhubung HASIL tidak merefer ke helper 1 maka >> formula helper 1 diubah menjadi : >> =(--IF(C8<>"",TEXT(B8,"yyyymmdd"))) dan hasilnya OK >> dg harapan menjadi lebih ringkes >> >> berhubung helper 1 sudah lebih ringkes maka >> formula helper 2 (array formula ditulis sekaligus di 72 cell sekolom) >> dicoba diubah menjadi >> >> =SMALL(IF((E8:E79)=TEXT(G7,"YYYYMMDD"),ROW(1:72)),ROW(1:72)) tetapi >> GAGAL, sementara diubah menjadi >> =SMALL(IF(TEXT(E8:E79,"00000000")=TEXT(G7,"YYYYMMDD"),ROW(1:72)),ROW(1:72)) >> >> Kenapa usaha ketiga gagal, ya Mbak ? >> > >
------------------------------------ ---------------------------------------------- bergabung ke milis (subscribe) kirim mail kosong ke: [email protected] posting ke milis kirimkan ke: [email protected] melihat file archive / mendownload lampiran http://www.mail-archive.com/[email protected]/ menhubungi moderators & owners: [email protected] ExcelWeb <http://opi.110mb.com/> ---------------------------------------------- Yahoo! Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/belajar-excel/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/belajar-excel/join (Yahoo! ID required) <*> To change settings via email: [email protected] [email protected] <*> To unsubscribe from this group, send an email to: [email protected] <*> Your use of Yahoo! Groups is subject to: http://docs.yahoo.com/info/terms/
