Day 49

Upon completion of Lesson 4, you will be able to:

  • Enter and edit formulas.
  • Distinguish between relative, absolute, and mixed cell references.
  • Use the point-and-click method to enter formulas.
  • Use the Function button to view summary calculations.
  • Preview a calculation.
  • Display formulas instead of results in the worksheet.

Project 4-1

  1. Open the Results Data File.
  2. Rename the workbook as Results of Formulas-(your initials).
  3. Enter formulas in the specified cells that perform the operations listed below.
    • Cell       Operation

      C3         Add the values in cells A3 and B3.

      C4         Subtract the value in cell B4 from the value in cell A4.

      C5         Multiply the value in cell A5 by the value in cell B5.

      C6         Divide the value in cell A6 by the value in cell B6.

      B7          Sum the values in the range B3:B6.

      D3         Add the values in cells A3 and B3, and then multiply by 3.

      D4         Add the values in cells A3 and A4, and then multiply by cell B3.

      D5         Copy the formula in cell D4 to cell D5.

      D6         Subtract the value in cell B6 from the value in cell A6, and then divide by 2.

      D7         Divide the value in cell A6 by 2, and then subtract the value in cell B6.

  4. In cell A1, enter your name.
  5. Close the workbook.

Project 4-2

  1. Open the Zoo Data File.
  2. Rename the workbook as Zoo Fundraiser-(your initials).
  3. In cells D6, D7, D8, and D9, enter formulas that multiply the values in column B by the values in column C.
  4. In cell D10, enter a formula to sum the totals in the range D6:D9.
  5. In cell D11, enter a formula to calculate a 7% sales tax of the subtotal in cell D10.
  6. In cell D12, enter a formula to add the subtotal and sales tax.
  7. Format the range D6:D12 in the Currency number format. The worksheet is ready to accept customer data.
  8. A customer purchases two tiger T-shirts, three dolphin T-shirts, one sweatshirt, and four coffee mugs. Enter these quantities in column C.
  9. Verify the formulas to ensure you have entered them correctly. If any of the formulas are incorrect, edit them and recalculate the worksheet. Repeat this process until you are confident that the worksheet is calculating results as intended.
  10. Close the workbook.

Project 4-3

  1. Open the Investment Data File.
  2. Rename the workbook as Investment Record-(your initials).
  3. In cells D6 through D8, enter formulas to calculate the values of the stocks. The formulas should multiply the number of shares in column B by the price of the shares in column C.
  4. In cells D10 and D11, enter formulas to calculate the values of the mutual funds. As with the stocks, the formulas should multiply the number of shares in column B by the price of the shares in column C.
  5. In cell D12, enter a formula that sums the values in cells D4 through D11. Format cell D12 by adding a Outer Border.
  6. In cell E4, enter the formula =D4/$D$12. This formula determines the percentage of each investment value with respect to the total investment value.
  7. Copy the formula in cell E4 to the ranges E6:E8 and E10:E11. Notice that the absolute reference to cell D12 in the formula remains unchanged as you copy the formula.
  8. In cell E12, enter a formula that sums the percentages in cells E4 through E11. Format cell E12 by adding a Top and Bottom Border.
  9. Enter the following updated share price amounts in the appropriate cells:
    •  Investment                                                         Price

        MicroCrunch Corp.                                           $16.25

        Ocean Electronics, Inc.                                     $21.25

        Photex, Inc.                                                       $13.50

        Prosperity Growth Fund                                   $  6.50

        Lucrative Mutual Fund                                     $18.00

  10. Close the workbook.

Project 4-4

  1. Open the Prairie Data File.
  2. Rename the workbook as Prairie Development-(your initials).
  3. Before considering other factors, the cost of a home is approximately $105 per square foot. In cell D5, enter a formula that multiplies the amount of square footage in cell B5 by the value per square foot in cell C5.
  4. The cost of a home is increased by $3,500 for each bathroom in the house. In cell D6, enter a formula that multiplies the number of bathrooms in cell B6 by the value per bathroom in cell C6.
  5. The cost of a home is increased by $3,250 for each car garage. In cell D7, enter a formula that multiplies the number of car garages in cell B7 by the value per car garage in cell C7.
  6. The cost of a home is increased by $3,000 if the house is located on a cul-de-sac. In cell D8, enter a formula that calculates the increase in value in cell C8 if 1 is entered in cell B8.
  7. The cost of a home is increased by $6,000 if the house has a swimming pool. In cell D9, enter a formula that calculates the increase in value in cell C9 if 1 is entered in cell B9.
  8. In cell D10, use the Sum button to calculate the sum of the numbers in the range D5:D9.
  9. A potential buyer inquires about the price of a home with the following qualities:
    • Square feet:                                                   2000

      Number of bathrooms:                                   3

      Number of car garages:                                 2

      On a cul-de-sac?                                           No

      With a swimming pool?                                  Yes

      In the range B5:B9, enter this data to determine the estimated price of the house.

    •  
  10. Close the workbook.