Marissa Jones Only!!!!
jermaineb1979
EnterandEditaSimpleFormula6.docxEnter 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.
