Marissa Jones Only!!!!

Enter and Edit a Simple Formula

Learning Outcomes

· Enter a formula
· Use cell references to create a formula
You use formulas in Excel to perform calculations such as adding, multiplying, and averaging. Formulas in an Excel worksheet start with the equal sign ( = ), also called the 
formula prefix
, followed by cell addresses, range names, values, and 
calculation operators
. Calculation operators indicate what type of calculation you want to perform on the cells, ranges, or values. They can include 
arithmetic operators
, which perform mathematical calculations (see 
Table 1-2
 in the “Understand Formulas” lesson); 
comparison operators
, which compare values for the purpose of true/false results; 
text concatenation operators
, which join strings of text in different cells; and 
reference operators
, which enable you to use ranges in calculations.  You want to create a formula in the worksheet that calculates gross pay for each employee.

Steps

1. 1

Click cell G5

This is the first cell where you want to insert the formula. To calculate gross pay, you need to add regular pay and overtime pay. For employee Peter Brucker, regular pay appears in cell E5 and overtime pay appears in cell F5.

Quick Tip

You can reference a cell in a formula either by typing the cell reference or clicking the cell in the worksheet; when you click a cell to add a reference, the Mode indicator changes to “Point.”
2. 2

Type =, click cell E5, type +, then click cell F5

Compare your formula bar to 
Figure 1-11
. The blue and red cell references in cell G5 correspond to the colored cell outlines. When entering a formula, it’s a good idea to use cell references instead of values whenever you can. That way, if you later change a value in a cell (if, for example, Peter’s regular pay changes to 690), any formula that includes this information reflects accurate, up-to-date results.

Figure 1-11Simple Formula in a Worksheet

3. 3

Click the Enter button  on the formula bar

The result of the formula =E5+F5, 804, appears in cell G5. This same value appears in cell G15 because cell G15 contains a formula that totals the values in cells G5:G14, and there are no other values at this time.
4. 4

Click cell F5

The formula in this cell calculates overtime pay by multiplying overtime hours (C5) times twice the regular hourly rate (2*D5). You want to edit this formula to reflect a new overtime pay rate.
5. 5

Click to the right of 2 in the formula bar, then type .5 as shown in 
Figure 1-12

Figure 1-12Edited Formula in a Worksheet

The formula that calculates overtime pay has been edited.
6. 6

Click  on the formula bar

Compare your screen to 
Figure 1-13
. Notice that the calculated values in cells G5, F15, and G15 have all changed to reflect your edits to cell F5.

Figure 1-13Edited Formula with Changes

7. 7

Save your work

Understanding Named Ranges

It can be difficult to remember the cell locations of critical information in a worksheet, but using cell names can make this task much easier. You can name a single cell or range of contiguous, or touching, cells. For example, you might name a cell that contains data on average gross pay “AVG_GP” instead of trying to remember the cell address C18. A named range must begin with a letter or an underscore. It cannot contain any spaces or be the same as a built-in name, such as a function or another object (such as a different named range) in the workbook. To name a range, select the cell(s) you want to name, click the Name box in the formula bar, type the name you want to use, then press [Enter]. You can also name a range by clicking the Formulas tab, then clicking the Define Name button in the Defined Names group. Type the new range name in the Name text box in the New Name dialog box, verify the selected range, then click OK. When you use a named range in a formula, the named range appears instead of the cell address. You can also create a named range using the contents of a cell already in the range. Select the range containing the text you want to use as a name, then click the Create from Selection button in the Defined Names group. The Create Names from Selection dialog box opens. Choose the location of the name you want to use, then click OK.