- Introduction
- Understanding Formulas
- Creating a Simple Formula
- Creating a Formula Using Formula AutoComplete
- Editing a Formula
- Understanding Cell Referencing
- Using Absolute Cell References
- Using Mixed Cell References
- Using 3-D Cell References
- Naming Cells and Ranges
- Entering Named Cells and Ranges
- Managing Names
- Simplifying a Formula with Ranges
- Displaying Calculations with the Status Bar
- Calculating Totals with AutoSum
- Calculating Totals with Quick Analysis
- Performing One Time Calculations
- Converting Formulas and Values
- Correcting Calculation Errors
- Correcting Formulas
- Auditing a Worksheet
- Locating Circular References
- Performing Calculations Using Functions
- Creating Functions
- Creating Functions Using the Library
- Calculating Multiple Results
- Using Nested Functions
- Using Constants and Functions in Names
Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Understanding Formulas
Introduction
A formula calculates values to return a result. On an Excel worksheet, you can create a formula using constant values (such as 147 or $10.00), operators (shown in the table), references, and functions. An Excel formula always begins with the equal sign (=).
A constant is a number or text value that is not calculated, such as the number 147, the text “Total Profits”, and the date 7/22/2013. On the other hand, an expression is a value that is not a constant. Constants remain the same until you or the system change them. An operator performs a calculation, such as + (plus sign) or - (minus sign). A cell reference is a cell address that returns the value in a cell. For example, A1 (column A and row 1) returns the value in cell A1 (see table below).
Cell Reference Examples
|
Reference |
Meaning |
A1 |
Cell in column A and row 1 |
A1:A10 |
Range of cells in column A and rows 1 through 10 |
A1:F1 |
Range of cells in row 1 and columns A through F |
1:1 |
All cells in row 1 |
1:5 |
All cells in rows 1 through 5 |
A:A |
All cells in column A |
A:F |
All cells in columns A through F |
|
Profits!A1:A10 |
Range of cells in column A and rows 1 through 10 in worksheet named Profits |
A function performs predefined calculations using specific values, called arguments. For example, the function SUM(B1:B10) returns the sum of cells B1 through B10. An argument can be numbers, text, logical values such as TRUE or FALSE, arrays, error values such as #NA, or cell references. Arguments can also be constants, formulas, or other functions, known as nested functions. A function starts with the equal sign (=), followed by the function name, an opening parenthesis, the arguments for the function separated by commas, and a closing parenthesis. For example, the function, AVERAGE(A1:A10, B1:B10), returns a number with the average for the contents of cells A1 through A10 and B1 through B10. As you type a function, a ToolTip appears with the structure and arguments needed to complete the function. You can also use the Insert Function dialog box to help you add a function to a formula.
Perform Calculations
By default, every time you make a change to a value, formula, or name, Excel performs a calculation. To change the way Excel performs calculations, click the Formulas tab, click the Calculation Options button, and then click the option you want: Automatic, Automatic Except Data Tables, or Manual. To manually recalculate all open workbooks, click the Calculate Now button (or press F9). To recalculate the active worksheet, click the Calculate Sheet button (or press Shift+F9).
Precedence Order
Formulas perform calculations from left to right, according to a specific order for each operator. Formulas containing more than one operator follow precedence order: exponentiation, multiplication and division, and then addition and subtraction. So, in the formula 2 + 5 * 7, Excel performs multiplication first and addition next for a result of 37. Excel calculates operations within parentheses first. The result of the formula (2 + 5) * 7 is 49.
Types of Operators
Operator |
Meaning |
Example |
|
|
Arithmetic |
|
|
|
|
= (plus sign) |
Addition |
2+7 |
|
- (minus sign) |
Subtraction |
7-2 |
|
* (asterisk) |
Multiplication |
2*7 |
|
/ (forward slash) |
Division |
7/2 |
|
% (percent) |
Percent |
70% |
|
^ (caret) |
Exponentiation |
2^7 |
Comparison |
|
|
|
|
= (equal sign) |
Equal to |
A2=B7 |
|
> (greater than sign) |
Greater than |
A2>B7 |
|
< (less than sign) |
Less than |
A2<B7 |
|
>= (greater than or equal to sign) |
Greater than or equal to |
A2>=B7 |
|
<= (less than or equal to sign) |
Less than or equal to |
A2<=B7 |
|
<> (not equal to sign) |
Not equal to |
A2<>B7 |
Text concatenation |
|
|
|
|
& (ampersand) |
Connects, or concatenates, two values to produce one continuous text value |
“Total”&”Profit” |
Reference |
|
|
|
|
: (colon) |
Range operator, which produces one reference to all the cells between two references |
A1:A10 |
|
, (comma) |
Union operator, which combines multiple references into one reference |
SUM(A1:A10,B1:B10) |
|
(space) |
Intersection operator, which produces on reference to cells common to the two references |
A1:A10 B1:B10 |