IFERROR

Pernahkah kamu menemukan hasil perhitungan rumus di excel seperti ini #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, atau #NULL!. Sungguh menyebalkan bukan? Apa yang akan kamu lakukan? Ada yang membiarkan dan masa bodoh dengan spread sheet yang kelihatan kotor, ada yang menghapus rumus nya supaya kelihatan bersih, dan ada juga yang kreatif mengakali dengan fungsi IF tambahan.

Lihat contoh berikut ini:

Kolom D berisi rumus yang membagi Total Nilai barang di kolom B dengan berat barang dalam Kg di kolom C, hasilnya adalah Harga/Kg. Tidak ada yang salah dengan rumus itu kecuali di baris 6 dan 9, kita tahu bahwa membagi sebuah bilangan dengan nol menghasilkan nilai tak terhingga, excel mengingatkan kita dengan menampilkan #DIV/0!

Orang yang cuek, sebut saja Mr. C akan membiarkan hasilnya seperti itu, dia baru bingung cari solusi kalau bos protes karena melihat laporan yang dibacanya kelihatan tidak elok dipandang mata. Temannya yang mengerti kemauan bos, yaitu Mr. R akan menghapus rumus di D6 dan D9 dan membiarkannya kosong atau mengisinya dengan angka nol. Sayang sekali Mr. R yang rajin ini akhirnya sangat repot dan waktunya habis hanya untuk menghapus rumus dan mengganti dengan angka nol karena ternyata masih banyak baris lain di bawahnya yang menghasilkan #DIV/0!

Kebetulan ada teman lain yang pintar yaitu Mr. P, dia mengamati bahwa Mr. R selalu menghapus rumus di baris yang salah, yaitu baris yang di kolom C berisi angka nol, dan mengganti rumus tersebut dengan angka nol. Solusi yang disarankannya adalah mendeteksi angka di kolom C sebelum melakukan pembagian. Ini dia hasilnya:

Mr. P melakukan sama persis apa yang dilakukan Mr. R, yaitu memeriksa apakah angka di di kolom C nol atau bukan. Bila angka di kolom C adalah nol maka dia langsung mengisi hasilnya dengan angka nol. Tetapi bila angka di kolom C bukan nol maka dia akan menghitung dengan rumus angka di kolom B dibagi angka di kolom C. Hasilnya persis sama. Lalu apa bedanya? Bedanya Mr. R mendeteksi angka nol dan mengganti rumus secara manual, sedangkan Mr. P melakukan keduanya dengan dengan fungsi IF (pelajari lagi fungsi IF di sini).

Perbedaan yang lain adalah lamanya waktu untuk menyelesaikan pekerjaan. Mr. R akan perlu waktu lama sekali jika jumlah baris yang dikerjakan mencapai angka ratusan atau bahkan ribuan ( yang biasa mengerjakan inventory pasti paham maksud saya). Sedangkan Mr. B hanya perlu waktu sangat sedikit tidak peduli jumlah barisnya ribuan, dia cukup membuat rumus di baris pertama lalu copy dan paste ke baris-baris berikutnya.

Lalu apa hubungannya pembahasan ini dengan fungsi IFERROR ?

IFERROR menampilkan nilai yang anda tentukan jika sebuah rumus menghasilkan kesalahan; selebihnya akan menampilkan hasil rumus tersebut bila benar. Fungsi ini digunakan untuk menangkap dan menangani kesalahan rumus.

Penulisannya: IFERROR(rumus, nilai_jika_salah)

Coba lihat bagaimana Mr. B yang bijaksana menyelesaikan persoalan yang sama, ini dia hasilnya:

Hasilnya sama. Perhatikan juga bahwa rumus nya tetap sama yaitu angka di kolom B dibagi dengan angka di kolom C.

Perbedaannya:

Mr. P mendeteksi nilai di kolom C, bila berisi angka nol maka dia tidak melakukan perhitungan tetapi langsung menaruh angka nol di kolom D. Bila kolom C berisi bukan angka nol maka dilakukan perhitungan dan hasilnya ditaruh di kolom D.

Mr. B langsung melakukan perhitungan. Bila hasil perhitungannya benar maka ditaruh di kolom D, bila hasil perhitungannya salah maka di kolom D diisi dengan angka nol.

Kesimpulan: cara Mr. P bisa dilakukan hanya bila kita tahu penyebab kesalahan hasil perhitungan, cara Mr. B tetap bisa dilakukan tanpa perlu tahu apa penyebab kesalahan.

Selamat mencoba dan membuat banyak kesalahan.

Makin sering bikin salah makin pintar, dijamin!

28 thoughts on “IFERROR

  1. Thanks a lot for giving everyone remarkably special chance to read from this blog. It is always very cool and as well , packed with fun for me and my office peers to visit your web site at minimum thrice in one week to study the new tips you will have. And lastly, I am always impressed with the mind-blowing methods you serve. Some 2 facts in this posting are in truth the most efficient we’ve had.

  2. Thank you for all of your labor on this web page. Kate take interest in managing research and it’s really easy to see why. We all learn all concerning the powerful medium you convey invaluable thoughts on the website and as well as attract contribution from some other people on that subject matter so my princess is without question starting to learn a lot of things. Enjoy the remaining portion of the new year. You have been doing a powerful job.

  3. Thank you a lot for giving everyone an extraordinarily marvellous possiblity to read in detail from here. It can be so enjoyable and packed with a good time for me and my office mates to search your web site nearly 3 times in a week to study the new stuff you have got. And indeed, we’re usually amazed with your staggering strategies served by you. Some 2 tips in this article are completely the most efficient we have ever had.

  4. I precisely needed to appreciate you again. I am not sure the things I might have carried out without the entire aspects contributed by you about such a area. It actually was the daunting scenario for me, but discovering the specialised fashion you dealt with it took me to jump over joy. Now i am happy for the information and in addition wish you really know what a powerful job your are undertaking teaching men and women through your webblog. I am certain you’ve never met any of us.

  5. I’m just writing to make you know what a notable encounter my cousin’s girl enjoyed browsing your webblog. She picked up so many things, which include what it is like to possess an awesome teaching heart to let other people with no trouble know precisely chosen very confusing topics. You truly did more than readers’ desires. I appreciate you for giving the invaluable, healthy, edifying and easy tips about that topic to Kate.

  6. I as well as my friends were checking out the great solutions found on the blog and then suddenly got an awful feeling I never thanked the web site owner for them. Those women were as a consequence very interested to see them and have now in reality been taking advantage of those things. I appreciate you for getting really accommodating and for using this kind of good useful guides millions of individuals are really needing to understand about. Our sincere apologies for not saying thanks to you sooner.

  7. My spouse and i ended up being very joyful Edward could round up his research through your ideas he came across out of your web site. It’s not at all simplistic to just be giving out techniques which other people could have been trying to sell. We really grasp we now have the blog owner to give thanks to because of that. The type of illustrations you’ve made, the simple web site navigation, the friendships your site help foster – it’s everything superb, and it is making our son and the family reason why that topic is cool, which is certainly pretty indispensable. Thank you for all the pieces!

  8. I have to point out my passion for your kindness giving support to those individuals that really need guidance on this topic. Your real commitment to passing the solution up and down was especially useful and have always empowered some individuals much like me to get to their desired goals. Your own useful key points indicates a great deal to me and still more to my office colleagues. Thanks a ton; from each one of us.

  9. I not to mention my guys ended up looking through the best recommendations on the website and suddenly I had an awful feeling I had not thanked the site owner for those secrets. Most of the people had been so thrilled to see them and have truly been tapping into those things. Thanks for indeed being simply kind and for pick out such exceptional subjects most people are really wanting to be informed on. My very own honest regret for not expressing gratitude to you sooner.

  10. I actually wanted to compose a note to say thanks to you for all the precious guidelines you are giving out here. My considerable internet investigation has at the end of the day been rewarded with professional facts and strategies to talk about with my friends and family. I ‘d believe that many of us readers are unquestionably lucky to be in a decent community with so many special individuals with great opinions. I feel rather grateful to have seen your entire website and look forward to plenty of more awesome moments reading here. Thanks a lot again for all the details.

  11. I enjoy you because of all your valuable effort on this site. My daughter take interest in carrying out internet research and it’s obvious why. We all know all relating to the lively method you give very useful secrets via the web blog and even attract response from others about this area of interest so our own simple princess is truly discovering a lot. Take pleasure in the remaining portion of the year. You have been conducting a remarkable job.

  12. I’m just commenting to let you understand what a really good encounter my wife’s child went through visiting your web page. She even learned some details, which include what it’s like to have a great teaching nature to get many people easily completely grasp chosen multifaceted subject areas. You actually surpassed readers’ expectations. Thanks for supplying those effective, trusted, revealing and in addition cool thoughts on that topic to Evelyn.

  13. Needed to put you a bit of word to thank you so much yet again about the extraordinary tactics you have shown above. This has been certainly remarkably generous of people like you to present publicly all some people could possibly have advertised for an electronic book in order to make some money on their own, most notably considering the fact that you might well have tried it in case you considered necessary. Those secrets also worked like the easy way to realize that the rest have a similar fervor much like my very own to realize many more related to this condition. I’m certain there are thousands of more pleasurable situations in the future for individuals that scan through your blog.

  14. I in addition to my pals ended up looking through the nice tips and hints from your website while unexpectedly I had a terrible feeling I had not expressed respect to the blog owner for those strategies. My young men happened to be so warmed to read all of them and have truly been using them. I appreciate you for actually being very considerate and for opting for this form of incredibly good subjects most people are really desirous to be aware of. My personal sincere apologies for not expressing appreciation to you sooner.

  15. I wish to show my love for your kindness in support of visitors who should have assistance with this concern. Your very own dedication to passing the solution around appears to be especially functional and have really encouraged folks much like me to achieve their pursuits. Your insightful tips and hints denotes so much a person like me and even more to my peers. Thanks a ton; from all of us.

  16. Thanks so much for giving everyone a very splendid chance to read articles and blog posts from this web site. It’s usually very superb and also full of fun for me personally and my office co-workers to visit your site no less than thrice per week to find out the latest guides you have got. And definitely, I’m also actually astounded considering the tremendous creative ideas you give. Selected 3 tips on this page are easily the very best we have had.

  17. Needed to write you one tiny word to be able to say thank you yet again on the incredible ideas you have shared here. It is strangely open-handed of people like you to give freely what exactly a lot of people could have made available for an electronic book to help with making some dough on their own, and in particular now that you might well have done it in the event you desired. The strategies in addition acted like a good way to be sure that other people have the identical dreams really like my very own to realize a lot more concerning this matter. I think there are millions of more pleasant occasions in the future for many who look over your blog post.

  18. I would like to voice my admiration for your kind-heartedness in support of those individuals that absolutely need help with this particular topic. Your personal commitment to passing the solution up and down has been certainly interesting and has continually encouraged guys and women like me to get to their objectives. Your personal useful facts indicates this much to me and even more to my peers. With thanks; from each one of us.

  19. I actually wanted to write down a quick note in order to say thanks to you for these superb suggestions you are placing here. My considerable internet investigation has at the end been honored with really good strategies to write about with my pals. I would point out that we website visitors actually are very much endowed to be in a magnificent website with many wonderful individuals with beneficial plans. I feel rather lucky to have used your entire site and look forward to plenty of more cool minutes reading here. Thanks once again for all the details.

  20. I precisely wished to say thanks all over again. I do not know the things I might have gone through in the absence of the entire tricks shared by you on my industry. It had been a very fearsome setting in my opinion, but being able to see your professional avenue you managed the issue made me to weep for gladness. I am happier for the help and as well , hope you recognize what an amazing job you have been providing educating other individuals through your website. More than likely you haven’t encountered any of us.

  21. I together with my guys were taking note of the good tips found on your web site then then came up with a terrible feeling I had not expressed respect to the website owner for them. All of the ladies are already absolutely glad to study all of them and have in effect undoubtedly been making the most of those things. We appreciate you turning out to be really kind as well as for settling on certain ideal ideas millions of individuals are really desirous to be aware of. My personal sincere apologies for not saying thanks to you sooner.

  22. I enjoy you because of all of the hard work on this blog. Kim take interest in conducting internet research and it’s simple to grasp why. Most of us notice all concerning the compelling way you render both interesting and useful ideas by means of your blog and as well as cause participation from other people on the content and our girl has always been starting to learn a lot of things. Take advantage of the remaining portion of the year. You’re conducting a great job.

  23. I want to convey my gratitude for your kindness for those people who should have guidance on this one matter. Your personal commitment to getting the message all around appeared to be extraordinarily functional and have in most cases helped most people much like me to get to their ambitions. This warm and helpful tips and hints signifies this much to me and especially to my mates. With thanks; from all of us.

  24. I am just writing to let you be aware of what a incredible experience my friend’s child found visiting yuor web blog. She came to understand such a lot of pieces, most notably what it’s like to have a marvelous giving heart to make folks without difficulty know precisely some specialized issues. You really surpassed her expected results. Many thanks for imparting these precious, trustworthy, informative not to mention fun tips on your topic to Ethel.

  25. I simply wanted to thank you very much once more. I’m not certain the things that I would have handled without these pointers shown by you on that topic. Entirely was a traumatic setting in my view, but discovering a new professional form you solved it made me to cry for fulfillment. I’m thankful for the work as well as pray you really know what an amazing job you have been undertaking training most people all through your web page. I am sure you’ve never come across any of us.

  26. Thank you a lot for providing individuals with remarkably spectacular chance to read critical reviews from this website. It can be very great and packed with a lot of fun for me personally and my office peers to visit your web site a minimum of thrice in a week to see the latest things you have. And of course, I am also actually impressed considering the effective principles you serve. Some 4 tips in this posting are honestly the most effective I’ve had.

  27. I wanted to send you this bit of observation to help thank you very much the moment again on your pleasant thoughts you have shown above. This has been really generous of you in giving without restraint what a few people might have offered as an e-book to get some dough for themselves, precisely given that you might have tried it if you desired. Those suggestions in addition worked to provide a good way to fully grasp other people have a similar dream the same as my own to learn a lot more pertaining to this issue. Certainly there are millions of more fun instances ahead for those who read carefully your blog post.

Leave a Reply

Your email address will not be published.