Spreadsheet validation: How to eliminate or minimise Excel mistakes.

April 21, 2016 9:06 pm

Spreadsheet mistakes can cost businesses billions of dollars, reduce reputation and cause job losses.

As well as being extremely costly, Excel calculation Errors has been shown to influence fiscal economic policies.

Reinhart’s and Rogoff’s 2010 “Growth in a Time of Debt” paper was cited by many politicians as justification for budget cutting. Their paper was later discounted due to mistakes in their Excel calculations.

  • How can we guard ourselves against making costly Excel calculation mistakes?
  • How do we validate that inputs and calculations in our spreadsheets are correct?
  • What types of errors do we encounter in an Excel spreadsheets and how do we find and eliminate them?

There are two basic types of errors or mistakes that are made in Excel spreadsheets:

There are two basic types of errors or mistakes that are made in Excel spreadsheets:

  1. Syntax errors; caused by invalid inputs according Excel rules.
  2. User input errors;
    1. Incorrect formulas
    2. Incorrect cell-references
    3. Wrong functions used

1. Syntax errors 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.

2. User input errors: 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’s work.

In a previous posting, check-and-audit-excel-spreadsheets, three methods were suggested, we will recap here.

Two of the methods are the standard “show formula” and “trace precedents and dependents” options provided by Microsoft.

06_Show formula and trace dependants

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.

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.

In a simple example like the one above these options are of course useful….Thank you Microsoft!… By the way, we love Excel and we probably couldn’t live without it!

Anywho… 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 “Nightmare”. Especially when the spreadsheet span hundreds of columns and/or rows and when formulas are complex and lengthy.

Also, when formulas are referencing other sheets or workbooks it can be hard keeping our enthusiasm in a positive mode!

Ok, let’s look at option 3. 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.

07_Display and how to input functions

Watch this short video to see how the XLN and XLV functions work.


 

By using these functions to display formulas we get an instant check and verification of the formula in Cell B7.

In simple terms, the xln function tracks down every cell-reference in the formula and displays the content of those cells.

The xlv function displays the variable name that the user have entered in the cell next to the reference cell.

A practical example of how these functions are used is shown below. Image 1 shows a calculation sheet NOT using the functions and Image 2 USING the functions.

By showing the formula numbers and/or variables, checking and validation of spreadsheet calculations become manageable.

Also, by incorporating display functions when the spreadsheet is being developed gives an instant feedback to the correctness of the work.

Image 1: Without Display Functions

Image 1: Without Display Functions

Image 2: With Display Functions

Image 2: With Display Functions

To use the formula display functions the XL-Viking add-in must be installed. The add-in is easy to install and it only takes about 20 seconds. Once installed the display functions are available in Excel just like any other native Excel function.

Currently the add-in contains 5 display function. There are video tutorials here that details all the features of the functions.

The XLN function is completely free to download and use: Download XLN function here.

There is no need to sign-up, provide Email address, give your credit card details and no commitment is required to download and use the XLN display function.

Also, by incorporating display functions when the spreadsheet is being developed gives an instant feedback to the correctness of the work.

Thanks for reading this post. Comments are appreciated.

By Knut Gjelsvik
Independent Contract Stress Engineer


Leave a Reply

Your email address will not be published. Required fields are marked *