# Enter a formula

In Excel 2010, entering and editing formulas and using functions in formulas is easier, thanks to improvements to the formula bar and Function AutoComplete.Read an article or try Office 2010! |

Formulas are equations that perform calculations on values in your worksheet.

A formula starts with an equal sign (=). For example, the following formula multiplies 2 by 3 and then adds 5 to the result:

`=5+2*3`

## Enter a simple formula: =128+345

The following formulas contain operators (operator: A sign or symbol that specifies the type of calculation to perform within an expression. There are mathematical, comparison, logical, and reference operators.) and constants (constant: A value that is not calculated and, therefore, does not change. For example, the number 210, and the text "Quarterly Earnings" are constants. An expression, or a value resulting from an expression, is not a constant.).

Example formula |
What it does |
---|---|

=128+345 | Adds 128 and 345 |

=5^2 | Squares 5 |

- Click the cell in which you want to enter the formula.
- Type
**=**(an equal sign). - Enter the formula.
- Press ENTER.

## Enter a formula that contains references or names: =A1+23

The following formulas contain relative references (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.) to and names (name: A word or string of characters that represents a cell, range of cells, formula, or constant value. Use easy-to-understand names, such as Products, to refer to hard to understand ranges, such as Sales!C20:C30.) of 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.

Example formula |
What it does |
---|---|

=C2 | Uses the value in the cell C2 |

=Sheet2!B2 | Uses the value in cell B2 on Sheet2 |

=Asset-Liability | Subtracts a cell named Liability from a cell named Asset |

- Click the cell in which you want to enter the formula.
- In the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) , type
**=**(equal sign). - Do one of the following:
- To create a reference, select a cell, a range of cells, a location in another worksheet, or a location in another workbook. You can drag the border of the cell selection to move the selection, or drag the corner of the border to expand the selection.

- To enter a reference to a named range, press F3, select the name in the
**Paste name**box, and click**OK**.

- Press ENTER.

## Enter a formula that contains a function: =AVERAGE(A1:B4)

The following formulas contain functions (function: A prewritten formula that takes a value or values, performs an operation, and returns a value or values. Use functions to simplify and shorten formulas on a worksheet, especially those that perform lengthy or complex calculations.).

Example formula |
What it does |
---|---|

=SUM(A:A) | Adds all numbers in column A |

=AVERAGE(A1:B4) | Averages all numbers in the range |

- Click the cell in which you want to enter the formula.
- To start the formula with the function, click
**Insert Function**on the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) . - Select the function you want to use. You can enter a question that describes what you want to do in the
**Search for a function**box (for example, "add numbers" returns the SUM function), or browse from the categories in the**Or Select a category**box. - Enter the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.). To enter cell references as an argument, click
**Collapse Dialog**to temporarily hide the dialog box. Select the cells on the worksheet, then press**Expand Dialog**. - When you complete the formula, press ENTER.

Enter a formula with nested functions: =IF

Nested functions use a function as one of the arguments of another function. The following formula sums a set of numbers (G2:G5) only if the average of another set of numbers (F2:F5) is greater than 50. Otherwise it returns 0.

- Click the cell in which you want to enter the formula.
- To start the formula with the function, click
**Insert Function**on the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.) . - Select the function you want to use. You can enter a question that describes what you want to do in the
**Search for a function**box (for example, "add numbers" returns the SUM function), or browse from the categories in the**Or Select a category**box. - Enter the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.).
- To enter cell references as an argument, click
**Collapse Dialog****Expand Dialog**. - To enter another function as an argument, enter the function in the argument box you want. For example, you can add SUM(G2:G5) in the
**Value_if_true**edit box. - To switch the parts of the formula displayed in the
**Function Arguments**dialog box, click a function name in the formula bar. For example, if you click IF, the arguments for the IF function appear.

## (AVERAGE(F2:F5)>50, SUM(G2:G5),0)

**Tips**

- You can quickly enter the same formula into a range of cells. Select the range you want to calculate, type the formula, and then press CTRL+ENTER. For example, if you type =SUM(A1:B1) in range C1:C5, and then press CTRL+ENTER, Excel enters the formula in each cell of the range, using A1 as a relative reference (relative reference: In a formula, the address of a cell based on the relative position of the cell that contains the formula and the cell referred to. If you copy the formula, the reference automatically adjusts. A relative reference takes the form A1.).
- If you are familiar with the arguments (argument: The values that a function uses to perform operations or calculations. The type of argument a function uses is specific to the function. Common arguments that are used within functions include numbers, text, cell references, and names.) of a function, you can use the function tooltip that appears after you type the function name and an opening parenthesis. Click the function name to view the Help topic on the function, or click an argument name to select the corresponding argument in your formula. To hide the function tooltips, on the
**Tools**menu, click**Options**, and then clear the**Function tooltips**check box on the**General**tab.