Creating Excel Formulas

Creating Excel Formulas

 

 
Understanding Excel Formulas:

To get the most out of Excel, you need to understand formulas so that you can perform calculations on your worksheet data. You need to know the components of a formula, you need to understand arithmetic and comparison formulas, and you need to understand the importance of precedence when building a formula.
 

 
Formulas: A formula is a set of symbols and values that perform some kind of calculation and produce a result. All Excel formulas have the same general structure: an equal sign followed by one or more operands and operators. The equal sign tells Excel to interpret everything that follows in the cell as a formula. For example, if you type =5+8 into a cell, Excel interprets the 5+8 text as a formula, and displays the result in the cell (13).

Operands: Every Excel formula includes one or more operands, which are the data that Excel uses in the calculation. The simplest type of operand is a constant value, which is usually a number. However, most Excel formulas include references to worksheet data, which can be a cell address, a range address, or a range name. Finally, you can also use any of Excel’s built-in functions as an operand.

Operators: In an Excel formula that contains two or more operands, each operand is separated by an operator, which is a symbol that combines the operands in some way, usually mathematically. Example operators include the plus sign and the multiplication sign.

Arithmetic Formulas: An arithmetic formula combines numeric operands, numeric constants, functions that return numeric results, and fields or items that contain numeric values, with mathematical operators to perform a calculation. Because Excel worksheets primarily deal with numeric data, arithmetic formulas are by far the most common formulas used in worksheet calculations.

Comparison Formulas: A comparison formula combines numeric operands, numeric constants, functions that return numeric results, and fields or items that contain numeric values, with special operators to compare one operand with another. A comparison formula always returns a logical result.

Operator Precedence: Most of your formulas include multiple operands and operators. In many cases, the order in which Excel performs the calculations is crucial. Consider the formula =3 + 5 ^ 2. If you calculate from left to right, the answer you get is 64 (3 + 5 equals 8, and 8 ^ 2 equals 64). However, if you perform the exponentiation first and then the addition, the result is 28 (5 ^ 2 equals 25, and 3 + 25 equals 28).

Build a Formula: You can add a formula to a worksheet cell using a technique similar to adding data to a cell. To ensure that Excel treats the text as a formula, be sure to begin with an equal sign and then type your operands and operators. When you add a formula to a cell, Excel displays the formula result in the cell, not the formula itself. To see the formula, click the cell and examine the Formula bar.