LUARRR BIASA.... ketemu aja yah Mr Kid ini, otaknya kayaknya beda sama kita-kita nih. Hehehehehe.... Dua Jempol buat Mr Kid. ________________________________________ From: [email protected] [[email protected]] Sent: Monday, March 09, 2015 4:04 PM To: BeExcel Subject: Re: [belajar-excel] Update Harga
Walaupun sudah selesai, ikutan nimbrung ah... Pas DD, pinjem formulanya ya... Bunyi formula seperti buatan pak DD berikut : =IF(C4<=29;VLOOKUP(B4;$H$5:$J$10;3;0);IF(C4>=30;VLOOKUP(B4;$H$5:$J$10;2;0))) mengatakan bahwa ambil kolom ke-3 kalau c4<=29. Jika c4>29, maka ambil kolom ke-2. Berarti ada bunyi vLookUp dasar yang berbunyi : VLOOKUP(B4;$H$5:$J$10;2;0) untuk c4>29 sedangkan untuk c4<=29 harus berbunyi : VLOOKUP(B4;$H$5:$J$10;3;0) alias VLOOKUP(B4;$H$5:$J$10;2+1;0) Bagian +1 hanya berlaku kalau c4<=29. Jika c4>29, maka harus menjadi +0. Karena TRUE setara nilai 1 dan FALSE setara nilai 0, maka bagian +1 bisa diganti dengan +(c4<=29) Jadi formula vLookUp yang berbunyi VLOOKUP(B4;$H$5:$J$10;2+1;0) jika bagian +1 diubah menjadi +(c4<=29) dan berbentuk VLOOKUP(B4;$H$5:$J$10;2+(c4<=29);0) akan menjadi formula VLOOKUP(B4;$H$5:$J$10;3;0) ketika c4<=29 karena bagian +(c4<=29) menjadi +1 dan akan menjadi VLOOKUP(B4;$H$5:$J$10;2;0) ketika c4>29 karena bagian +(c4<=29) menjadi +0 Nah bunyi formula pak DD bisa diubah dari berbunyi : =IF(C4<=29;VLOOKUP(B4;$H$5:$J$10;3;0);IF(C4>=30;VLOOKUP(B4;$H$5:$J$10;2;0))) menjadi : VLOOKUP(B4;$H$5:$J$10;2+(c4<=29);0) Dengan bunyi formula yang lebih singkat, tampaknya jadi lebih mudah untuk dilacak jika ada ketidak sesuaian hasil formula dengan hasil kerja manual. hmmm.... ternyata menyederhanakan formula tidaklah sulit bukan ? yuk tetap semangat belajar setiap hari supaya dapat hal baru setiap hari dan bisa berbagi setiap hari ;) Wassalam, Kid. On Mon, Mar 9, 2015 at 5:11 PM, PMA Log Staff 4 [email protected]<mailto:[email protected]> [belajar-excel] <[email protected]<mailto:[email protected]>> wrote: Dear Mr Roy, bisa dicoba rumus ini : =IF(C4<=29;VLOOKUP(B4;$H$5:$J$10;3;0);IF(C4>=30;VLOOKUP(B4;$H$5:$J$10;2;0))) semoga sesuai harapan. Salam, DD ________________________________________ From: [email protected]<mailto:[email protected]> [[email protected]<mailto:[email protected]>] Sent: Monday, March 09, 2015 12:28 PM To: [email protected]<mailto:[email protected]> Subject: [belajar-excel] Update Harga [1 Attachment] Dear Para Tetua Excel Mohon bantuannya untuk kasus excel yang membuat saya sakit kepala mencari jalan keluarnya Terima kasih atas bantuannya Regard Roy M. ________________________________ This e-mail and any files transmitted with it are confidential and intended solely for the use of the individual to whom it is addressed. If you have received this email in error please send it back to the person that sent it to you. Any views or opinions presented are solely those of its author and do not necessarily represent those of NABATI or any of its subsidiary companies. Unauthorized publication, use, dissemination, forwarding, printing or copying of this email and its associated attachments is strictly prohibited. ------------------------------------ ------------------------------------ ============================================================ Pojok Lowongan Kerja yang disediakan milis : http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html *** Posting lowongan kerja : ke link tersebut dan klik New Topic ============================================================ bergabung ke milis (subscribe), kirim mail kosong ke: [email protected]<mailto:[email protected]> posting ke milis, kirimkan ke: [email protected]<mailto:[email protected]> berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages melihat file archive / mendownload lampiran : http://www.mail-archive.com/[email protected]/ atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/ menghubungi moderators & owners: [email protected]<mailto:[email protected]> keluar dari membership milis (UnSubscribe), kirim mail kosong ke : [email protected]<mailto:[email protected]> --------------------------------------------------------------------- ------------------------------------ Yahoo Groups Links ------------------------------------ ------------------------------------ ============================================================ Pojok Lowongan Kerja yang disediakan milis : http://milis-belajar-excel.1048464.n5.nabble.com/Pojok-Lowongan-Kerja-f5725753.html *** Posting lowongan kerja : ke link tersebut dan klik New Topic ============================================================ bergabung ke milis (subscribe), kirim mail kosong ke: [email protected] posting ke milis, kirimkan ke: [email protected] berkunjung ke web milis : http://tech.groups.yahoo.com/group/belajar-excel/messages melihat file archive / mendownload lampiran : http://www.mail-archive.com/[email protected]/ atau (sejak 25-Apr-2011) bisa juga di : http://milis-belajar-excel.1048464.n5.nabble.com/ menghubungi moderators & owners: [email protected] keluar dari membership milis (UnSubscribe), kirim mail kosong ke : [email protected] --------------------------------------------------------------------- ------------------------------------ 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: https://info.yahoo.com/legal/us/yahoo/utos/terms/
