# Microsoft Excel – Chapter 3

Exam A, Questions 1-10

Exam B, Questions 11-20

Exam C, Questions 21-30

Exam D, Questions 31-40

Exam E, Questions 41-50

Exam F, Questions 51-60

Exam G, Questions 61-65

Exam H, Questions 66-75

Exam I, Questions 76-80

Exam J, Questions 81-85

Exam K, Questions 86-90

# Questions 1-10

Open the Exc3ExamDataA file and use it to answer the following questions.

You are interested in visiting the University of Maryland at College Park in the United States, but you need \$3,000 to fund your trip.  You go to a local bank

and borrow this \$3,000 at a starting interest rate of 5.50%.  These questions will assist you in figuring out what monthly payments will be for the next few years.

1.  There is a spelling mistake on the spreadsheet.  Find and fix it.  (M)

2. Without physically editing C2, make A6 = C2 (M)

3.  Make A7 = A6+.01 (E)

4. Use the fill handle located on the bottom left corner of A7 to fill A8 through A10.  If you haven’t done so already, make A6=C2 and A7=A6+.01 (E)

5. You have just discovered that you made an error when creating this spreadsheet.  Your starting interest was actually 5.00%.  Change C2 to reflect this. (E)

6. Go to Edit and then Find.  Search for "Payment" and have it changed to "Monthly Payment" (M)

7. In B6, make a payment function which uses the interest in A6, a 2 year loan, and \$3,000 as your loan amount. (D)

8. Use the fill handle on B6 to fill in B7 through B10.  If you haven’t done so already, type =PMT(A6/12,2*12,-\$C\$1) into B6 (E)

9. Select cell C6.  Go to the insert menu and click on "Function".  Click "Financial" in the function category box and PMT in the function name list box.  Next to "rate" type A6/12.  Next to "Nper" type 4*12.  Next to "Pv" type -\$C\$1 (D)

10. Make D6 equal to C6 minus B6.  Use the fill handle to fill D7 through D10.  (D)

# Questions 11-20

Open the Exc3ExamDataB file and use it to answer the following questions.

11. In cells B8 and C8, use the SUM function to sum up the columns above them (Hours of Work Per Week and Hours Spent on School Each Week) (M)

12. In cells B9 and C9, use the AVERAGE function to find the average of the columns above them. (M)

13. Sadly, Anastae dropped out of school.  Change her hours spent on school each week to “0” (E)

14. Use pointing to find out how many hours Ebrima spends on school and work each week total. (E)

15. Center the word “Total” in cell D1 (E)

16. Change the contents of cells B1, C1, and D1 to 10 point, red, Times New Roman font (M)

17. In cells B10 and C10, use the MAX function to find out who works the most in each column each week (D)

18. In cells B10 and C10, use the MIN function to find out who works the least in each column each week (D)

19. Highlight cells A8-C11.  Put an outline around them (D)

20. Make cells A8-C11 the color yellow.  (Not the typing, the cells themselves) (M)

# Questions 21-30

Open the Exc3ExamDataC file and use it to answer the following questions.

21. Merge cells A1 and B1, A2 and B2, and A4-D4 (M)

22. Make “Amount Borrowed” and “Starting Interest” 12 point Arial font, bold, and italic (M)

23. Center “Monthly Payment” and make it 16 point Arial font and bold (E)

24. In cell C1, type 4% as your starting interest amount (E)

25. Make A7 equal A6+1%.  Use the fill handle to fill A8-A11 (M)

26. In cell B6, use the paste function dialog box to make a formula calculating your monthly payment over 30 years.  After you make the equation, use the fill handle to fill cells B7-B11. (D)

27. In cell C6, make a payment function using A6 for interest, 15 as the length of the loan, and C2 as the original loan amount and then use the fill handle to fill C7-C11. (D)

28. In cell D5, type “Difference” (E)

29. In cell D6, make a function to determine the difference between B6 and C6 and then use the fill handle to place this function into cells D7-D11 (D)

30. Merge cells A13-D13.  Type:  “Financial Advisor:” and then your name.  (E)

# Questions 31-40

Open the Exc3ExamDataD file and use it to answer the following questions.

31. Make column A have a width of approximately 13.00(96 pixels) (M)

32. Center all typing from cells A2-A8 (E)

33. Make all typing from cells A1-A8 the color blue (E)

34. Make all cells from A1-F1 have a background color of red (E)

35. Change cell B2 to 5, cell D3 to 11, cell B4 to 9, and cell E5 to 4 (M)

36. Use the AVERAGE function to determine the average # of goats owned. (M)

37. Use the fill handle on cell C8 to fill in cells D8-F8 (M)

38. Add farmer 5 below farmer 4.  He has 6 goats, 12 cows, 5 chickens, and 8 pigs.  Fill these statistics in their appropriate cells (D)

39. In cell A9, use the COUNT function to determine how many cells between A1 and F7 coins numbers (D)

40. In cell A10, use the COUNTA function to determine how many cells between A1 and F7 contain anything (D)

# Questions 41-50

Open the Exc3ExamDataE file and use it to answer the following questions.

41. Format cell B1, B2, B3, B4, and B7 to show currency and format cell B5 to show a percentage. (M)

42. Calculate the manufacturers rebate in cell B2 using the given information (D)

43. The interest rate was entered incorrectly.  Change cell B5 to 5% (E)

44. Using a PMT function and the given information, calculate your monthly payments in B7. (D)

45. In column C, enter a price of \$25,000 into C1, \$0 into C2, \$6000 into C3, \$19000 into C4, 6% into C5, and 3 into C6. (E)

46. Calculate the new monthly payments in C7. (D)

47. As it turns out, you can only afford to put \$7000 down and pay \$250 a month at 4.5% interest for 5 years.  Enter these values where they belong between B9 and B15.  Note, you will have to put the normal monthly payment formula into B15 and type \$250 where it belongs when you goal seek. (M)

48. Using the goal seek command, find out how expensive of a car you can purchase and then calculate how much of a loan you will need.  (D)

49. Make the text in A1 through A15 Blue with a Red background.(E)

50. Use the COUNTA function on cells A1-C15.  Place this function into cell A16.(M)

# Questions 51-60

Open the Exc3ExamDataF file and use it to answer the following questions.

51.    Center all students’ names in their respective cells. (E)

52.    Change Antoine’s money raised from \$35.08 to \$86.56 (E)

53.    Using the Group and Outline command, group the participants into three sections:  grade 1, grade 2, and grade 3 (D)

54.    Using the Sum function, find the total amount of money raised by each grade.  Make sure you changed Antoine to have raised \$86.56. (D)

55.    Using the Sum function, find the number of items sold by each grade (D)

56.    Calculate the total amount of money earned by all grades. Make sure you changed Antoine to have raised \$86.56. (M)

57.    Calculate the total number of items sold by all grades (M)

58.    Find the average amount of money earned per grade.  Make sure you changed Antoine to have raised \$86.56. (M)

59.   Make the total number of items sold blue, the number of items sold and total raised by each grade red, and the total money raised and average money raised per grade green.  (E)

60.  Make the title, “First Elementary School Fundraiser” centered and bold. (E)

# Questions 61-65

Open the Exc3ExamDataG file and use it to answer the following questions.

61.    Using the given information, calculate the monthly payment for bank 1 (D)

62.    Using the given information, calculate the total interest for bank 1 (D)

63.    Using the given information, calculate the total cost for bank 1 (D)

64.    Using the scenario manager, calculate the monthly payment, total interest, and total cost for bank 2 (D)

65.    Calculate the difference between the total cost of bank 1 and the total cost of bank 2. (D)

# Questions 66-75

Open the Exc3ExamDataH file and use it to answer the following questions:

66. Format all cells from B3 to E5, making them percentages (E)

67. Find your average for all quizzes in cell B7 (E)

68. Find your low homework score in cell B8 (E)

69. Find your high score for class 3 in cell B9 (E)

70. Find your total points for class 1 in cell B10 (E)

71. In cell C7, create an IF function that displays “Good” if your average is 80% or above and “Bad” if it is below 80% (M)

72. In cell C8, create an IF function that says “Try Harder” if B8 is less than 70% and “Excellent” if it is 70% or above (M)

73. In cell C9, create an IF function that says “Great” if B9 is greater than 90% and “Average” if it is below or equal to 90% (M)

74. In cell C10, create an IF function that says “Horrible” if B10 is less than 320% and “Awesome” if it is greater or equal to 320% (D)

75. In cell A13, find your average for all grades in all classes (B3-E5).  In B13, created an IF function that says “Pass” if A13 is greater or equal to 70% and “Fail” if it is less than 70% (D)

# Questions 76-80

Open the Exc3ExamDataI file and use it to answer the following questions:

76.    In cell B1, type “January”.  Using the AutoFill capability, fill cells C1-M1 with the months of the year (E)

77.    From cells A2-A8, use AutoFill to type in the days of the week (M)

78.    In cell B5, type “Student 1”.  Use AutoFill to fill from C5 to M5 (E)

79.    Use the AutoFill capability to display all years from 1995 to 2007 from A10 to J10 (D)

80.    Center all typing and change all fonts to Times New Roman (E)

# Questions 81-85

Open the Exc3ExamDataJ file and use it to answer the following questions:

81.    In cell B4, use the Freeze Panes technique so that the Student Name column doesn’t disappear as you scroll to the right (M)

82.    Add another scenario entitled “Change”.  In this scenario, change cells D18-D20 and then for the scenario values, type in 60%, 15%, and 25%. (M)

83.    Under performance rating, change “Satisfactory” to “Average” (M)

84.    Change Simon’s Exam 2 score to 120 (E)

85.    Change all student names to Microsoft Sans Serif font (E)

# Questions 86-90

Open the Exc3ExamDataK file and use it to answer the following questions:

86.    In row 8, use the Sum function to find the sum of each row (M)

87.    In row 9, use the Average function to find the average of each row (M)

88.    In row 10, use the Min function to find the minimum value of each row (M)

89.    In row 11, use the Max function to find the maximum value of each row (M)

90.    In row 12, create IF functions that say “Yes” if the average of the row is greater or equal to 50 and “No” otherwise (D)

# Questions 91-100

Open the Exc3ExamDataL file and use it to answer the following questions:

91.    Merge cells A1 and A2.  Merge cells B1 and B2 (E)

92.    Change the beginning interest to 5.19% (E)

93.    Change the amount borrowed to \$14,500 (E)

94.  Create a PMT function in cell B6 using the given criteria.  Use the fill handle to fill cells B7-B12. (D)

95.  Create a PMT function in cell C6 using the given criteria.  Use the fill handle to fill cells C7-C12. (D)

96.  Create a PMT function in cell D6 using the given criteria.  Use the fill handle to fill cells D7-D12. (D)

97.  Highlight and copy all cells from A1 to D12.  Paste this in cell A17 (Note:  This will simply create a full copy of your spreadsheet above, and it will not all be placed into cell A17, that will just be the upper left corner) (M)

98. Highlight all cells from A1 to D12.  Make the font color blue. (M)

99. Make “Amount Borrowed” and “Starting Interest” italic (E)

100. In cell E5 type “Difference”.  In the cells below it, make a function that calculates the difference between the 20 year and the 5 year loan.  Use the fill handle to fill all cells from E6 to E12.  (Note:  You will subtract column D from column B) (D)