Here is the final chapter of my ebook "5 Reasons Your Spreadsheet is Misbehaving". A free copy is available by clicking here.
No overly complex formulas.
Choose the right function for the job.
If you make a mistake in your formula, Excel lets you know right away. Most people are familiar with Excel’s error messages that pop up when you don’t enter the formula correctly. These errors, typically the result of incorrect syntax, are very common.
However, there are other common formula mistakes that people make all the time without realizing it. This includes not following the Order of Precedence, writing formulas that are needlessly complex, and choosing the wrong function.
#DIV/0! – You are trying to divide by zero. Dividing by zero is mathematically impossible, so Excel displays an error message.
#N/A – You told Excel to look for data but either the data does not exist, or you told Excel to look for the data in the wrong place. Take Note: Unlike other syntax errors, with the #N/A error, your formula is probably written correctly.
#NAME? – There are several reasons this can occur:
You misspelled something in your formula, typically the function name or a named range if you have used one.
You forgot to use double quotes when using text in the formula.
You forgot to include the range operator – you put A1C1 instead of A1:C1.
#NUM! - Your formula produces a result – positive or negative – that is too large for Excel to show.
#REF! – Your formula points to a cell that does not exist. This typically occurs when you write a formula that refers to a cell in a row or column, then delete that row or column.
#VALUE! – Your text or numerical values are incorrect. You may have entered or pointed to text values in a formula that only accepts numerical values.
What You Need to Know
Most Excel functions have both required and optional arguments.
All optional arguments have a default value.
NEVER assume the default value is the correct choice for your situation.
ALWAYS explicitly specify the optional argument to avoid errors.
Order of Precedence
Excel follows a very specific set of rules when it calculates your formula. Excel does not just start at the beginning and go from left to right, calculating as it goes. Instead, Excel looks for certain symbols to determine the order in which Excel performs its calculations.
This is the Order of Precedence:
You can manipulate the Order of Precedence by using parentheses in your formula.
Let’s look at some examples!
Calculation Controlled by Order of Precedence vs Controlled by Parentheses
You can see how using parentheses gives a completely different answer than the Order of Precedence. So, if you want to ensure you always get the right answer, you must either:
1. Be certain that you understand the Order of Precedence OR
2. Use parentheses to ensure that your formula calculates the way you want.
All too often users write formulas that are too long and complex. Such formulas are hard to understand and even harder to troubleshoot if you get a wrong answer or an error message.
What You Need to Know
Simple. Simple. Simple.
The user should be able to understand your formula at first glance.
Resist the urge to put multiple calculations into one formula.
Break up your formula into small parts into separate columns.
Keep in mind that other people may need to look at your spreadsheet. You want to make it as easy as possible for other users to understand what you did. That is why using small formulas with helper columns is better than having one long, complex formula.
What is a helper column? It is a column that you use in a spreadsheet to store intermediate steps in your calculation. Helper columns, with short formulas, help the user understand the thought process behind your calculations.
The following picture shows a table that uses a needlessly complex formula in one cell. Any user who tries to understand or troubleshoot this file will get frustrated very quickly. It is hard to glance at this formula and understand what each part is doing, why it is included, and what is being referenced.
Sales calculation with one, overly complex formula
(Note: The text should say "The only cell with a formula is F3)
By contrast, take a look at picture below. In this table, the user added several helper columns:
Column D – Total Sales
Column E – Sales Days per Month
Column H – Average Daily Sales
Column I – Sales Days per Year
With the inclusion of the helper column, it is easy to understand the process by which the final answer – annual sales – is calculated. The formula in the last column is much shorter and easier to understand. Each helper column has a short formula. The only long formula, in E5, is easier to grasp because it is a standalone IF function, and not nested in a larger formula.
Choose the Correct Function
You must be careful when typing the function name in Excel. There are many functions that have similar names but perform very different functions. Here are some examples:
AVERAGE - Returns the average of a list of numbers.
AVERAGEA - Returns the average of a list of numbers. Counts cells with the logical values of TRUE as the number 1 and cells with the logical value of FALSE as zero.
AVERAGEIF - Calculates the average of a range of cells that meet a specific criterion.
AVERAGEIFS - Calculates the average of a range of cells that meet multiple criteria.
COUNT - Returns the number of numerical values in a range of cells.
COUNTA - Returns the number of non-blank cells in a range of cells.
COUNTBLANK - Returns the number of blank cells in a range of cells.
COUNTIF - Counts the number of cells in a range that meet a specific criterion.
COUNTIFS - Counts the number of cells in a range that meet multiple criteria.