Logical Errors


In my blog post about spreadsheet syntax errors, I explained that a syntax error occurs when your function is incorrect.


If your error is not a syntax error, it is a logical error. There are many types of logical errors, all of which can be prevented. Here is a list of logical errors that can occur in your spreadsheet.


Image by Elisa Riva from Pixabay


Cell Reference

  • You reference the wrong range OR

  • You reference only a portion of the needed data

  • NOTE: It is a syntax error if you reference a range that does not exist. When this happens, you will get a #REF! error


Order of Precedence Not Followed

  • Spreadsheet programs interpret how to calculate a formula by following the order of precedence shown in the following table.

  • If your formula results in an incorrect answer, you have not followed the order of precedence.


Computation Components

  • Performing calculations on data which do not agree


Hardcoded Numbers

  • You use a number instead of a cell reference in a formula


Disbursed Variables / Assumptions

  • You have your variables and assumptions scattered throughout the spreadsheet instead of consolidating them all on one worksheet tab.


Readability

  • You want the user to be able to quickly understand your spreadsheet and the point you are trying to make. If you don't the user will draw the wrong conclusion.

  • Simplify the content to be reviewed

  • People don’t have time to decipher and review spreadsheets so point people to the outcomes they have to analyze


Hidden Sheets / Columns

  • If you hide a sheet, column, or row as a security measure, to conceal sensitive information – DON’T!

  • It’s not secure

  • Hidden items can be changed by any user edits

  • It is better to hardcode the data and send the user a duplicate of the worksheet


Lack of Formula Checks

  • You should always calculate the answer for critical data in different ways in different places in your spreadsheet

  • Use a checksum to ensure your formula is correct


Complex Formulas

  • Avoid complex formulas - they are difficult to understand

  • Complex formulas increase the likelihood of logical - and syntax - errors

  • Instead of complex formulas, use multiple simple formulas with helper columns

  • Resist the urge to create heavily nested lengthy formulas

  • Use named ranges to ensure you use the correct data and so your formula is much easier for the user to understand


Comment Sparingly

  • If you label everything, then nothing is important


Duplication Without Inspection

  • Review your worksheet before you make copies

  • If your workbook has an error, then you only create and worsen a problem when you distribute copies

  • Have someone else review your file


Other Practices Best Avoided to Ensure Simplicity and Understanding

  • Not Using Named Ranges or Built-In Functions

  • Using VBA When Formulas Will Suffice

  • Too Many Columns in One Worksheet

  • Using Copy / Paste Instead of Connecting the Spreadsheet to the Data Source

  • File Names / Worksheet Names Don’t Make Sense

  • File Not Documented in File Properties

  • Not Understanding the Business Issues




6 views