Excel Practice

Project 2

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.

  1. On the ProjectDay2 worksheet, select cell B11 and click the edit formula button.
  2. Click cell B8
  3. Press + and click cell B9, then press + again and click cell B10
  4. 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.

Copying formulas

  1. 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.

  1. With mouse pointer in cell C5 click the right mouse button.
  2. Click the copy command on the shortcut menu.
  3. Select cells D5 through G5
  4. 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

  1. In the ProjectDay2 worksheet, click cell H13then click the copy button on the standard toolbar.
  2. Click cell B15, then click paste button on the standard toolbar.
  3. Press del to delete the formula and select cell H13 again.
  4. Click in the formula bar and position the insertion point between the open parentheses and the first column letter H
  5. Type $ and press          type $ and press           twice; type $ and press       ; type $
  6. click the enter button on the formula bar
  7. 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.

Naming Ranges

Open the ProjectDay2 workbook and worksheet 2

  1. click cell B3 and drag to cell C4 to select the range B3:c4
  2. Open insert menu, move the mouse pointer to the name command and then choose define from submenu that appears.
  3. Replace the default menu  paper_stationary with paper97\
  4. click ok
  5. select cell F3 through G4 in the worksheet
  6. Open the insert menu, move the mouse pointer to the name command then choose define from the submenu that appears
  7. In the names workbook text box, type paper then click the add button.
  8. in the define name dialog box, click the collapse button, at the right end of the refers to text box.
  9. 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.

Using functions

  1. Select cell D9 and click the edit formula button on the formula bar.
  2. Click the drop down arrow to the right of the function button.
  3. Select sum
  4. In the number 1 text box, type paper97 and then click ok
  5. In cell D10, type =sum(supplies97) and then press Enter
  6. Select cell H10, click the edit formula button, then choose sum from the function drop down list.
  7. Click the collapse button at the right end of Number1 text box.
  8. Select cell F5:G6 to enter the supplies98 range then click the expand button at the right end of Number 1 text box.
  9. Click Ok in the formula palette.

Building formulas with functions

  1. 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)

  1. select cell I12 again, click the right mouse button to open the shortcut menu, then choose copy.
  2. Select cell I3 through I7, open the shortcut menu , then choose paste.
  3. In cell D11, enter the formula = sum(b6:c6) + sum (b7: c7)
  4. select cell H11, click the edit formula button and choose the sum unction.
  5. 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.
  6. In cell H12, enter the formula = average (sum(b6:c7), sum (f6:g7)

Using the conditional statement

  1. Select cell B13
  2. Click the formula edit button
  3. 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.

  1. Click ok
  2. In the value_if_true box, type Yes!, including the exclamation mark and press tab
  3. In the value_if_false box typeno
  4. Click ok

Disclaimer:

The information contains in this web-site is prepared for educational purpose. This site may be used by the students, faculties, independent learners and the learned advocates of all over the world. Researchers all over the world have the access to upload their writes up in this site. In consideration of the people’s participation in the Web Page, the individual, group, organization, business, spectator, or other, does hereby release and forever discharge the Lawyers & Jurists, and its officers, board, and employees, jointly and severally from any and all actions, causes of actions, claims and demands for, upon or by reason of any damage, loss or injury, which hereafter may be sustained by participating their work in the Web Page. This release extends and applies to, and also covers and includes, all unknown, unforeseen, unanticipated and unsuspected injuries, damages, loss and liability and the consequences thereof, as well as those now disclosed and known to exist.  The provisions of any state’s law providing substance that releases shall not extend to claims, demands, injuries, or damages which are known or unsuspected to exist at this time, to the person executing such release, are hereby expressly waived. However the Lawyers & Jurists makes no warranty expressed or implied or assumes any legal liability or responsibility for the accuracy, completeness or usefulness of any information, apparatus, product or process disclosed or represents that its use would not infringe privately owned rights. Reference herein to any specific commercial product process or service by trade name, trade mark, manufacturer or otherwise, does not necessarily constitute or imply its endorsement, recommendation or favouring by the Lawyers & Jurists. The views and opinions of the authors expressed in the Web site do not necessarily state or reflect those of the Lawyers & Jurists. Above all, if there is any complaint drop by any independent user to the admin for any contents of this site, the Lawyers & Jurists would remove this immediately from its site.