Excel: Debugging formulas

Formulas can sometimes yield error values ​​or unintended results. Below are some tools that will help you investigate the causes of these errors and determine solutions.

Formulas are equations that perform calculations on values ​​in the worksheet. A formula starts with an equal sign (=). In the example below, 3 and 1 are added together.

= 3 + 1

A formula can also contain one or more of the following items: functions, references, operators, and constants.

Parts of a formula

Functions: Included in Excel, functions are designed formulas in which specific calculations are performed. For example, the PI () function returns the value of pi: 3.142 …

References: refer to individual cells or cell ranges. A2 returns the value in cell A2.

Constants: numbers or text values ​​entered directly into a formula, such as 2.

Operators: The operator ^ (caret character) is used to raise a number to a power and the operator * (asterisk) is used to multiply. Use + and – to add and subtract values. Use / to share.

Note: Some functions require that arguments are called. Arguments are the values ​​that certain functions use to perform their calculations. If required, arguments are enclosed in the parentheses () of the function. No arguments are required for the PI function, which is why it is empty. Some functions require one or more arguments, and you can leave space for additional arguments. You must use a comma to separate arguments, or a semicolon (;) depending on your location settings.

The SUM function requires only one argument, but the function has 255 arguments.

= SUM (A1: A10) is an example of one argument.

= SUM (A1: A10; C1: C10) is an example of multiple arguments.

Common mistakes

The following table lists some of the common mistakes a user can make while typing a formula. And explains how to correct those errors:

Please note the followingMore information
Start each function with an equal sign (=)If you omit the equal sign, the data you type may appear as text or as a date. For example, if you SUM (A1: A10) Excel becomes the string SUM (A1: A10) is displayed and the calculation is not performed. If you 11/2 Excel will change the date as Nov 2 (as the cell format General instead of dividing the result 11 by 2.
Make sure the opening and closing brackets matchCheck whether each opening parenthesis has a corresponding closing parenthesis. When using a function in a formula, each parenthesis must be in the right place, otherwise the function will not work properly. The formula = IF (B5 <0), “Not valid”, B5 * 1.05) does not work as there are two parentheses close and only one parenthesis open while there must be one of each. The correct formula is: = IF (B5 <0, “Not valid”, B5 * 1.05).
Use a colon to indicate a rangeIf you reference a range of cells, you must put a colon (:) as a separator between the reference to first and the reference to the last cell in the range. For example: = SUM (A1: A5), and not = SUM (A1 A5), causing the error # EMPTY! would be returned.
Type all required argumentsSome functions have mandatory arguments. Also check that you have not provided too many arguments.
Enter the correct type of argumentsFor some functions, such as SUM, only numerical arguments are required, while other functions, such as TO REPLACE, a text value is needed for at least one of the arguments. If you use the wrong data type, some functions in Excel may produce unexpected results or an error.
Do not nest more than 64 functionsYou may enter (nest) up to 64 levels of functions within a function.
Enclose names of other worksheets with single quotesIf a formula contains references to values ​​or cells on other worksheets or in other workbooks, and the name of the other worksheet or workbook contains spaces or non-alphabetic characters, you must enclose this name in single quotes (“), for example = “Quarterly Figures”! D3, or = “123”! A1.
Use an exclamation mark (!) After the worksheet name when referring to it in a formulaFor example, to display the value of cell D3 in a worksheet named Quarterly data in the same workbook, use the following formula: = “Quarterly data”! D3.
Include the path to external workbooksMake sure that all external references contain the name of a workbook and the path to the workbook.

A workbook reference contains the name of the workbook and must be enclosed in parentheses (Worksheet name.xlsx). The reference must also include the name of the worksheet in the workbook.

If the workbook you want to reference is not open in Excel, you can still insert a reference in a formula. Enter the full path to the file, as in the following example: = ROWS (“C: My Documents [K2 activiteiten.xlsx]Sales ’! A1: A8). This formula gives the number of rows in the range of cells A1 through A8 in the other workbook (8).

Note: If the full path contains spaces, as in the previous example, you must enclose the path in single quotes (at the beginning of the path and after the name of the workbook, before the exclamation mark).

Type numbers without formattingDo not format numbers that you specify in formulas. If you want to enter 1000 euros as value, enter 1000 in the formula. If you enter a comma as part of a number, Excel interprets the comma as a separator. To display numbers with thousands separators or currency characters, format the cells after entering the values.

For example, if you want to add 3100 to the value in cell A3 and you enter the formula = SUM (3, 100, A3) Excel adds the numbers 3 and 100 and adds that total to the value in A3, instead of adding 3100 to A3, so if = SUM (3100, A3). For example, if you enter the formula = ABS (-2,134), Excel displays an error message because the ABS function accepts only one argument: = ABS (-2134).

For more information, see Microsoft’s page on Excel.


Related Articles

Back to top button