Constructing Simple Formulas

The whole purpose of building worksheets is to have Excel perform calculations for you. Excel has many powerful built-in functions that are a sort of shorthand for the various formulas used in mathematical, logical, statistical, financial, trigonometric, logarithmic , and other types of calculations. However, the majority of worksheets created with Excel involve simple calculations that use basic arithmetic. In this section, we show you how to construct a simple formula to find the average of a set of values, and then we introduce Excel features that you use to quickly add sets of numeric values.

Information about  

More formulas, page 137

Doing Arithmetic

In Excel, you begin a formula (Excel s term for an equation ) with an equal sign (=). In the simplest formulas, the equal sign is followed by a set of values separated by +, -, *, or /, such as

=5+3+2

If you enter this formula in any blank cell in a worksheet, Excel displays the result, 10.

Let s experiment with formulas. You ll start by inserting a couple of blank rows to give you space to work. Follow these steps:

1. Select the headers for rows 1 and 2 to select the two rows.

2. Click Rows on the Insert menu.

   Excel inserts two blank rows above the title, moving the table down so that it begins in row 3. Now you re ready to construct a formula in cell A1, using some of the values in the Amount of Invoice column. You tell Excel to use a value simply by clicking the cell that contains it.

3. Click cell A1 , and type = (an equal sign) followed by ( (an open parenthesis).

4. Click cell D4 .

   Excel inserts the reference D4 in cell A1 and in the formula bar. It also displays the reference D4 in blue and outlines the actual D4 cell with a moving dotted blue line.

5. Type + (a plus sign), and click cell D5 .

   Excel changes the moving dotted outline of cell D4 to a solid blue border, and adds the reference D5 to the formula.

6. Continue to build the formula by typing plus signs and clicking cells D6 , D7 , and D8 .

7. Type )/ (a close parenthesis followed by the division operator), and then type 5 .

   The formula now looks like this:

   

   This formula tells Excel to first add the amounts in cells D4, D5, D6, D7, and D8, and then divide the result by 5 to obtain the average of the five amounts.

8. Click the Enter button.

   Excel displays the result of the formula, 5638.718, in cell A1.

   You can use this technique to create any simple formula. Start by typing an equal sign, and then enter a value or click the cell that contains the value, type the appropriate arithmetic operator, enter the next value, and so on. Unless you tell Excel to do otherwise , the program performs multiplication and division before addition and subtraction. If you need parts of the formula to be carried out in a different order, use parentheses as you did in this example to override the default order.

Totaling Columns of Values Using the AutoSum Button

Although the arithmetic method of creating a formula is simple enough, it would become tedious if you had to type and click to total a long series of values. Fortunately, Excel automates the addition process with a very useful button: the AutoSum button.

The AutoSum button will probably become one of your most often-used Excel buttons . It s easy to use, so without further ado, we ll show you what to do. Follow these steps:

1. Select cell D16 .

2. On the Standard toolbar, click the AutoSum button s down arrow, and then click Sum in the drop-down list.

   Excel looks first above and then to the left of the active cell for an adjacent range of values to total. Excel assumes that you want to total the values above D16 and enters the SUM function in cell D16 and in the formula bar. A ScreenTip also appears, illustrating the action of the equation. Your worksheet now looks like the one shown in this graphic:

   

3. Click the Enter button to enter the formula.

   Excel displays $48,406.33 ”the sum of the values in D4:D15. (If necessary, widen the cell to see the value.)

Displaying formulas

By default, Excel displays the results of formulas in cells, not their underlying formulas. To display the underlying formulas in the worksheet, on the Tools menu click Options, display the View tab, select Formulas in the Window options area, and click OK. Excel widens the cells so that you can see the formulas. To redisplay the results, deselect the Formulas check box.

 

Totaling Columns of Values Using the SUM Function

The AutoSum button serves you well whenever you want a total to appear at the bottom of a column or to the right of a row of values. But what if you want the total to appear elsewhere on the worksheet? Knowing how to create SUM functions from scratch gives you more flexibility.

Let s go back and dissect the SUM function that Excel inserted in cell D16 when you clicked Sum in the AutoSum button s drop-down list so that you can examine the function s components . With cell D16 selected, you can see the following entry in the formula bar:

=SUM(D4:D15)

Like all formulas, the SUM function begins with an equal sign (=). Next comes the function name in capital letters , followed by a set of parentheses enclosing the reference to the range containing the amounts you want to total. This reference is the SUM function s argument. An argument answers questions such as What? or How? and gives Excel the additional information it needs to perform the function. In the case of SUM, Excel needs only one piece of information ”the references of the cells you want to total. As you ll see later, Excel might need several pieces of information to carry out other functions, and you enter an argument for each piece.

AutoCalculate

You can use the AutoCalculate area at the right end of the status bar to instantly see the results of certain functions. Right-click the Auto-Calculate area to display a menu of functions, and click the function you want. (The default is the SUM function.) Next select a range of values on your worksheet. The result of the function appears instantly in the AutoCalculate area. For example, if you click the AVERAGE function and then select a range of values, the average of the values appears in the AutoCalculate area.

 

Function names

When you type a function name, such as SUM, in the formula bar, you don t have to type it in capital letters. Excel capitalizes the function name for you when you complete the entry. If Excel does not respond in this way, you have probably entered the function name incorrectly.

 

Creating a SUM function from scratch is not particularly difficult. For practice, follow these steps:

1. Press Ctrl+Home to move to cell A1, and type this:

   =SUM(

   When you begin typing, the cell s old value is overwritten.

2. Select D4:D15 on the worksheet in the usual way.

   Excel inserts the reference D4:D15 after the open parenthesis.

3. Type ) (a close parenthesis), and then press Enter .

   In cell A1, Excel displays the total of the values in the Amount of Invoice column, $48,406.33.

Information about  

AVERAGE function, page 126

Referencing a Formula Cell in Another Formula

After you create a formula in one cell, you can use its result in other formulas simply by referencing its cell. To see how this works, follow these steps:

1. Select cell B1 , and type = (an equal sign).

2. Click cell A1 , which contains the SUM function you entered earlier, type / (the division operator), and then type 12 .

3. Click the Enter button.

   Excel displays the result ”the average of the invoice amounts ”in cell B1.

4. Press the Delete key to erase both the experimental formula and its result from cell B1.