In this project you will learn how to:
· Creating formulas by selecting cells
· Copying formulas
· Using absolute cell references
· Naming Ranges
· Using named Ranges
· select worksheet items
· using functions
· Building formulas with functions
· Using conditional statements
Creating formulas by selecting cells
You can also enter cell addresses into formulas by selecting the cell or range of cells in the worksheet. This simplifies the process of creating a formula.
- On the ProjectDay2 worksheet, select cell B11 and click the edit formula button.
- Click cell B8
- Press + and click cell B9, then press + again and click cell B10
- Click enter in the formula palette
S 5. Select cell B13 and click the autosum button on the standard toolbar.
6. Click cell B2 and drag down to cell B10, then release the mouse button.
7. Click the enter button on the formula bar.
8. Select cell H2 and click the autosum button on the standard toolbar.
9. Click the enter button on the formula bar.
- select cell C5 in the ProjectDay2 worksheet
Cell C5 contains the formula = C2*.5 which you want to copy in the rest of the cells in Insurance row.
- With mouse pointer in cell C5 click the right mouse button.
- Click the copy command on the shortcut menu.
- Select cells D5 through G5
- Move the mouse pointer to the active cell (D5), right click to open the shortcut menu, then choose the paste command.
Using absolute cell references
- In the ProjectDay2 worksheet, click cell H13then click the copy button on the standard toolbar.
- Click cell B15, then click paste button on the standard toolbar.
- Press del to delete the formula and select cell H13 again.
- Click in the formula bar and position the insertion point between the open parentheses and the first column letter H
- Type $ and press type $ and press twice; type $ and press ; type $
- click the enter button on the formula bar
- with cell H13 still selected, click the copy button on the standard toolbar, select cell B15, then click the paste button on the standard toolbar.
Open the ProjectDay2 workbook and worksheet 2
- click cell B3 and drag to cell C4 to select the range B3:c4
- Open insert menu, move the mouse pointer to the name command and then choose define from submenu that appears.
- Replace the default menu paper_stationary with paper97\
- click ok
- select cell F3 through G4 in the worksheet
- Open the insert menu, move the mouse pointer to the name command then choose define from the submenu that appears
- In the names workbook text box, type paper then click the add button.
- in the define name dialog box, click the collapse button, at the right end of the refers to text box.
- select cell B5 through C6 in the worksheet.
Using named ranges
In the worksheet2 click the dropdown arrow to the right of the name box of the formula bar. From the name box drop down list , select paper98. This selects the range paper98.
- Select cell D9 and click the edit formula button on the formula bar.
- Click the drop down arrow to the right of the function button.
- Select sum
- In the number 1 text box, type paper97 and then click ok
- In cell D10, type =sum(supplies97) and then press Enter
- Select cell H10, click the edit formula button, then choose sum from the function drop down list.
- Click the collapse button at the right end of Number1 text box.
- Select cell F5:G6 to enter the supplies98 range then click the expand button at the right end of Number 1 text box.
- Click Ok in the formula palette.
Building formulas with functions
- In cell I2 enter the formula = average(b2+c2,f2+g2)
Note: you can get the same result from the simple formula =average (d2, h2)
- select cell I12 again, click the right mouse button to open the shortcut menu, then choose copy.
- Select cell I3 through I7, open the shortcut menu , then choose paste.
- In cell D11, enter the formula = sum(b6:c6) + sum (b7: c7)
- select cell H11, click the edit formula button and choose the sum unction.
- In the number 1 text box, type the range f6:g6 and press tab, then in the number 2 text box, type the range f7:g7 and click ok.
- In cell H12, enter the formula = average (sum(b6:c7), sum (f6:g7)
Using the conditional statement
- Select cell B13
- Click the formula edit button
- Choose logical in the function category list, and then choose IF in the function list.
For this example the logical test is whether or not your computer costs are greater than $ 4000. If they are then it makes sense to purchase a computer.
- Click ok
- In the value_if_true box, type Yes!, including the exclamation mark and press tab
- In the value_if_false box typeno
- Click ok