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.



Please wait...

Subscribe to ExcelMOOC posts

Want to be notified when our Excel article is published? Enter your email address and name below to know all new Excel features and cool tips