A formula is an equation that performs operations on spreadsheet data or other data. Formulas can perform mathematical operations, such as addition and multiplication, or they can join text or compare spreadsheet values. The following formula adds the value in cell A1 on a spreadsheet to the value in cell A2:
=A1+A2
Formulas can use constants (such as a number or text value), can refer to cells on the spreadsheet, or can use values from data on the same Web page.
Elements of a formula
A formula in a spreadsheet always begins with an equal sign (=). Following the equal sign are the elements to be calculated (the operands), which are separated by calculation operators. Calculation operators are symbols that specify the type of calculation
CalculationFormulas are calculated from left to right, beginning with the equal sign, and according to a specific order for each operator in the formula. For instance, multiplication is performed before addition.
=5+2*3*4
In the preceding example, the leftmost multiplication operation (2*3) is performed first for a result of 6; then the next multiplication operation in the left-to-right sequence (6*4) is performed for a result of 24. Finally, the addition operation (5+24) is performed for a result of 29.
You can control the order of calculation by changing the syntax of the formula. For example, if you use parentheses in the following example to change the syntax of the example above, 5 and 2 are added first (5+2), then the result (7) is multiplied by 3 for a result of 21, and then 21 is multiplied by 4 for a result of 84.
=(5+2)*3*4
Calculation is automatic by default, which means that when a cell that a formula refers to changes, the formula recalculates. However, you can set calculation to manual so that you can recalculate only when you choose.
A formula can refer to constant values and to other cells. The cell that contains the formula is known as a dependent cell when its value depends on the values in other cells. For example, cell B2 is a dependent cell if it contains the formula =C2.
Whenever a cell that the formula refers to changes, the dependent cell also changes, by default. For example, if a value in any of the following cells changes, the result of the formula =B2+C2+D2 also changes.
If you use constant values in the formula instead of references to the cells (for example, =30+70+110), the result changes only if you modify the formula yourself.
The Microsoft Office Spreadsheet Component provides many predefined, or built-in, formulas, which are known as functions. Functions can be used to perform simple or complex calculations. For example, the following formula uses the SUM function to add a range of cells:
=SUM(A1:A5)