Excel Guideline #12: Don’t Neglect Excel Errors
The Art of making Reliable Spreadsheets. We, Infotron and the Delft University of Technology, write articles about creating good reliable spreadsheets every other week. Never miss one!
Guideline #12: Don’t Neglect Excel Errors
However tempting it may be, it is a bad idea to leave standard Excel errors in your workbook. Because after a while, you no longer know whether you left a mistake consciously or whether there is something wrong with your spreadsheet. Read More.
On perfectxl.com you can Subscribe on our guidelines for free.
Available for data analysis for decision insight, accelerate data processing, data quality, professional Excel development, VBA automation, Survey APIs, Power Query, testing spreadsheets, review & controls audit.
7 年"For example, an addition of a range of which one of the cells contains a text." That depends how you add . SUM() ignores text; A1+B2+C3 etc would throw #VALUE! There are ISNA() and ISERR() and ISERROR() etc for specific testing. IFERROR is extremely commonly used in place of the old IF(ISERROR(long expression),"",long expression). "99% not necessary" is a bit high IMO :-) Use with discretion, typically for presentation purposes to avoid #DIV/0 or #N/A appearing in sheets. As an alternative, consider allowing them to display in sheets but suppress in Print Setup when printing or PDFing.
Director PerfectXL, the company that makes Excel better
7 年Thanks Eric, good point. I can agree the IFERROR has disadvantages. In my opinion, the best option would be specific functions for ifDIV0, IFVALUE etc.. Then we could handle every case without being too easygoing.
Financial Manager bij KCAP Architects&Planners
7 年Agree with most of your guidelines, but I disagree with your conclusion of #12, IFERROR is in 99% not necessary and has (my opinion) even more disadvantages.