If a formula refers to values or cells on other worksheets or workbooks, and the name of the other workbook or worksheet contains spaces or non-alphabetical characters, you must enclose its name within single quotation marks ( ' ), like ='Quarterly Data'!D3, or =‘123’!A1.
You can enter, or nest, no more than 64 levels of functions within a function.Įnclose other sheet names in single quotation marks If you use the wrong type of data as an argument, Excel may return unexpected results or display an error. Other functions, such as REPLACE, require a text value for at least one of their arguments. Some functions, such as SUM, require numerical arguments. Also, make sure that you have not entered too many arguments. For example, =SUM(A1:A5), not =SUM(A1 A5), which would return a #NULL! Error. When you refer to a range of cells, use a colon (:) to separate the reference to the first cell in the range and the reference to the last cell in the range. The formula should look like this: =IF(B5<0,"Not valid",B5*1.05). For example, the formula =IF(B5<0),"Not valid",B5*1.05) will not work because there are two closing parentheses and only one open parenthesis, when there should only be one each. When you use a function in a formula, it is important for each parenthesis to be in its correct position for the function to work correctly. Make sure that all parentheses are part of a matching pair (opening and closing). If you type 11/2, Excel displays the date 2-Nov (assuming the cell format is General) instead of dividing 11 by 2. For example, if you type SUM(A1:A10), Excel displays the text string SUM(A1:A10) and does not perform the calculation. If you omit the equal sign, what you type may be displayed as text or as a date. Start every function with the equal sign (=)
Error of your ways how to#
The following table summarizes some of the most common errors that a user can make when entering a formula, and explains how to correct them. Use + and – add and subtract values, and / to divide.
Operators: The ^ (caret) operator raises a number to a power, and the * (asterisk) operator multiplies. A2 returns the value in cell A2.Ĭonstants: numbers or text values entered directly into a formula, such as 2.
References: refer to individual cells or ranges of cells. For example, the PI() function returns the value of pi: 3.142. For example, the following formula adds 3 to 1.Ī formula can also contain any or all of the following: functions, references, operators, and constants.įunctions: included with Excel, functions are engineered formulas that carry out specific calculations. Learn how to enter a simple formulaįormulas are equations that perform calculations on values in your worksheet. For help on specific errors, you can search for questions like yours in the Excel Community Forum, or post one of your own. It is not an exhaustive list of methods for correcting every possible formula error. Note: This topic contains techniques that can help you correct formula errors.