How to Assemble Excel Formulas the Right Way
There’s a saying in the computer business: Garbage in, garbage out. And that applies to how Excel formulas are put together. If a formula is constructed the wrong way, it returns an incorrect result or an error.
Two types of errors can occur in formulas. In one type, Excel can calculate the formula, but the result is wrong. In the other type, Excel is not able to calculate the formula. Check out both of these.
A formula can work and still produce an incorrect result. Excel does not report an error because there is no error for it to find. Often, this is the result of not using parentheses properly in the formula. Take a look at some examples:
|=7 + 5 * 20 + 25 / 5||112|
|=(7 + 5) * 20 + 25 / 5||245|
|=7 + 5 *( 20 + 25) / 5||52|
|=(7 + 5 * 20 + 25) / 5||26.4|
All of these are valid formulas, but the placement of parentheses makes a difference in the outcome. You must take into account the order of mathematical operators when writing formulas. Here’s the order of precedence:
Multiplication and division
Addition and subtraction
This is a key point of formulas. It is easy to just accept a returned answer. After all, Excel is so smart. Right? Wrong! Like all computer programs, Excel can do only what it is told. If you tell it to calculate an incorrect but structurally valid formula, it will do so. So watch your p’s and q’s — er, your parentheses and mathematical operators — when building formulas.
The second type of error occurs when a mistake in the formula or in the data the formula uses prevents Excel from calculating the result. Excel makes your life easier by telling you when such an error occurs. To be precise, it does one of the following:
Excel displays a message when you attempt to enter a formula that is not constructed correctly.
Excel returns an error message in the cell when there is something wrong with the result of the calculation.
First, look at what happens when you try to finish entering a formula that had the wrong number of parentheses. The following figure shows this.
Excel finds an uneven number of open and closed parentheses. Therefore, the formula cannot work (it does not make sense mathematically), and Excel tells you so. Watch for these messages; they often offer solutions.
On the other side of the fence are errors in returned values. If you got this far, the formula’s syntax passed muster, but something went awry nonetheless. Possible errors include
Attempting to perform a mathematical operation on text
Attempting to divide a number by 0 (a mathematical no-no)
Trying to reference a nonexistent cell, range, worksheet, or workbook
Entering the wrong type of information into an argument function
This is by no means an exhaustive list of possible error conditions, but you get the idea. So what does Excel do about it? There are a handful of errors that Excel places into the cell with the problem formula.
|Error Type||When It Happens|
|#DIV/0!||When you’re trying to divide by 0.|
|#N/A!||When a formula or a function inside a formula cannot find the
|#NAME?||When text in a formula is not recognized.|
|#NULL!||When a space was used instead of a comma in formulas that
reference multiple ranges. A comma is necessary to separate range
|#NUM!||When a formula has numeric data that is invalid for the
|#REF!||When a reference is invalid.|
|#VALUE!||When the wrong type of operand or function argument is