Spreadsheet mistakes\u00a0can cost businesses billions of dollars, reduce reputation and cause job losses.<\/span><\/p>\n

As well as being extremely costly, Excel calculation Errors has been shown to\u00a0influence fiscal economic policies.<\/span><\/p>\n

Reinhart\u2019s and Rogoff\u2019s 2010 \u201cGrowth in a Time of Debt\u201d paper was cited by many politicians as\u00a0justification for budget cutting. Their paper was later discounted due to mistakes in their Excel calculations.<\/span><\/p>\n

\n
• How can we guard ourselves against making costly Excel calculation mistakes?<\/strong><\/span><\/li>\n
• How do we validate that inputs and calculations in our spreadsheets are correct?<\/strong><\/span><\/li>\n
• What types of errors do we encounter in an Excel spreadsheets and how do we find and eliminate them?<\/strong><\/span><\/li>\n<\/ul>\n

  There<\/span> are two basic types of errors or mistakes that are made in Excel spreadsheets:<\/span><\/span><\/p>\n

  There are two basic types of errors or mistakes that are made in Excel spreadsheets:<\/span><\/p>\n

  \n
  1. Syntax errors; caused by invalid inputs according Excel rules.<\/span><\/li>\n
  2. User input errors;<\/span>\n
     \n
     1. Incorrect formulas<\/span><\/li>\n
     2. Incorrect cell-references<\/span><\/li>\n
     3. Wrong functions used<\/span><\/li>\n<\/ol>\n<\/li>\n<\/ol>\n

        1. Syntax errors<\/span> are fairly easy to deal with as long as you are aware of what they mean. Excel syntax errors and how to fix them are covered here.<\/span><\/p>\n

        2.\u00a0User input errors<\/span>: i.e. using the wrong formula or referring to the wrong cells can be more difficult to discover. To check the correctness and validity of a spreadsheet can sometimes be an overwhelming task, especially when the spreadsheet contains a large number of formulas spanning hundreds of rows and\/or columns. Even more so when the task at hand is checking someone else\u2019s work.<\/span><\/p>\n

        In a previous posting, check-and-audit-excel-spreadsheets, three methods were suggested, we will recap here.<\/span><\/p>\n

        Two of the methods are the standard \u201cshow formula\u201d and \u201ctrace precedents and dependents\u201d options provided by Microsoft.<\/span><\/p>\n

        <\/p>\n

        By showing the formulas we can see what the formula looks like, and by colour coding the cell-references one can see what numbers each cell reference represents.<\/span><\/p>\n

        By using the precedents & dependents one can see which cells are referenced by the formula in a cell and also which cell is referencing the current cell.<\/span><\/p>\n

        In a simple example like the one above these options are of course useful\u2026.Thank you Microsoft!\u2026 By the way, we love Excel and we probably couldn\u2019t live without it!<\/strong><\/span><\/p>\n

        Anywho\u2026 anyone who have used Excel for work or leisure knows that using these two options to decipher what is going on in a spreadsheet can be a small \u201cNightmare\u201d. Especially when the spreadsheet span hundreds of columns and\/or rows and when formulas are complex and lengthy.<\/span><\/p>\n

        Also, when formulas are referencing other sheets or workbooks it can be hard keeping our enthusiasm in a positive mode!<\/span><\/p>\n

        Ok, let\u2019s look at option 3.<\/strong> The Xl-Viking display functions xlv and xln are used to display Excel formulas.The xlv function shows the variables and xln function shows the numbers of the formula.<\/span><\/p>\n

        <\/p>\n

        Watch this short video to see how the XLN and XLV functions work.<\/span><\/p>\n

        \n\t\t\t

        \n\t\t\t\t