Create a formula

The following formulas contain operators and constants.

Example formula	What it does
=128+345	Adds 128 and 345
=5^2	Squares 5

1. Click the cell in which you want to enter the formula.
2. Type = (an equal sign).
3. Enter the formula.
4. Press ENTER.

The following formulas contain relative references to and names 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

1. Click the cell in which you want to enter the formula.
2. In the formula bar , type = (equal sign).
3. 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 create a reference to a named range, press F3, select the name in the Paste name box, and click OK.
4. Press ENTER.

The following formulas contain functions.

Example formula	What it does
=SUM(A:A)	Adds all numbers in column A
=AVERAGE(A1:B4)	Averages all numbers in the range

1. Click the cell in which you want to enter the formula.
2. To start the formula with the function, click Insert Function on the formula bar .
3. 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.
4. Enter the arguments. 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 .
5. When you complete the formula, press ENTER.

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.

1. Click the cell in which you want to enter the formula.
2. To start the formula with the function, click Insert Function on the formula bar .
3. 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.
4. Enter the arguments.
   • To enter cell references as an argument, click Collapse Dialog next to the argument you want to temporarily hide the dialog box. Select the cells on the worksheet; then press 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.

• You can enter the same formula into a range of cells by selecting the range first, typing the formula, and then pressing CTRL+ENTER.
• If you are familiar with the arguments 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.