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