Upon completion of Lesson 5, you will be able to:
- Identify the parts of a function.
- Enter formulas with functions.
- Use functions to solve mathematical problems.
- Use functions to solve statistical problems.
- Use functions to solve financial problems.
- Use logical functions to make decisions with worksheet data.
- Use functions to insert times and dates in a worksheet.
- Use text functions to format and work with cell contents.
Project 5-1
- Open the Test Data File.
- Rename the workbook as Test Grades followed by your initials.
- In cell B25, enter a formula with a function to determine the number of students taking the examination.
- In cell B26, enter a formula with a function to determine the average exam grade.
- In cell B27, enter a formula with a function to determine the highest exam grade.
- In cell B28, enter a formula with a function to determine the lowest exam grade.
- In cell B29, enter a formula with a function to determine the standard deviation of the exam grades.
- Format cells B26 and B29 to display one digit to the right of the decimal.
- Close the workbook.
Project 5-2
- Open the National Data File.
- Rename the workbook as National Bank followed by your initials.
- In cell B11, enter the PMT function to calculate the yearly payment for borrowers. The lending rate will be entered in cell B7, the term of the loan will be entered in cell B9, and the loan principal (or present value) will be entered in cell B5. (The formula results are #NUM!, indicating an error due to division by zero, because no data is entered in the argument’s cell references.)
- A potential borrower inquires about the payments on a $5,500 loan for four years. The current lending rate is 8%. Determine the yearly payment on the loan. (The number in cell B11 appears as a negative, because this amount must be paid.)
- Raise your hand and show your teacher.
- In cell B24, enter the FV function to calculate the future value of periodic payments for depositors. The interest rate will be entered in cell B22, the term of the payments will be entered in cell B20, and the yearly payments will be entered in cell B18. (The formula results show $0.00, because no data is entered in the argument’s cell references.)
- A potential depositor is starting a college fund for her child. She inquires about the value of yearly deposits of $2,550 at the end of 15 years. The current interest rate is 4.5%. Determine the future value of the deposits. (Remember to enter the deposit as a negative because the depositor must pay this amount.)
- Close the workbook.
Project 5-3
The Tucson Coyotes have just completed seven preseason professional basketball games. Coach Patterson will soon be entering a press conference in which he is expected to talk about the team’s performance for the upcoming season. Coach Patterson wants to be well informed about player performance before entering the press conference.
- Open the Team Data File.
- Rename the workbook as Team Stats followed by your initials.
- In cell J6, enter a function that adds the values in the range B6:I6.
- Copy the formula in cell J6 to the range J7:J12.
- In cell J19, enter a function that adds the values in the range B19:I19.
- Copy the formula in cell J19 to the range J20:J25.
- In cell B13, enter a function that averages the game points in the range B6:B12.
- In cell B14, enter a function that determines the standard deviation of the game points in the range B6:B12.
- In cell B15, enter a function that counts the number of entries in the range B6:B12.
- Copy the formulas in the range B13:B15 to the range C13:I15.
- In cell B26, enter a function that averages the rebounds in the range B19:B25.
- In cell B27, enter a function that determines the standard deviation of the rebounds in the range B19:B25.
- In cell B28, enter a function that counts the number of entries made in the range B19:B25.
- Copy the formulas in the range B26:B28 to the range C26:I28.
- Use the worksheet data to complete this form.
- Close the workbook.
Project 5-4
- Open the Golf Data File.
- Rename the workbook as Golf Tryouts followed by your initials. A player must average a score of less than 76 to qualify for the team.
- In cell I5, enter a function that displays Made if the average score in cell H5 is less than 76 and Cut if the score is not less than 76. (Hint: The IF function has three arguments. The first argument is the logical test that determines whether the value in cell H5 is less than 76. The second argument is the text that appears if the statement is true. The third argument is the text that appears if the statement is false. Because the items to be displayed are words rather than numbers, they must be entered within quotation marks.)
- Copy the formula from cell I5 to the range I6:I16.
- In cell B21, enter a function that displays today’s date.
- Format cell B21 so that the date appears as the month followed by the day and year, such as March 14, 2010.
- Click cell B22, and then enter your name.
- Close the workbook.
Project 5-5
You have worked for Xanthan Gum Corp. for several years and are now eligible for promotion. Promotions at Xanthan are determined by supervisor ratings and a written examination. To be promoted, employees must score an average of 80 or above in the following four categories:
- Supervisor rating of leadership potential
- Supervisor rating of understanding of duties
- Supervisor rating of willingness to work hard
- Written test score
After receiving your supervisor ratings, you prepare a worksheet to determine the minimum written test score you need to be promoted.
- Open the Xanthan Data File.
- Rename the workbook as Xanthan Promotion followed by your initials.
- In cell B7, enter 70 as the supervisor rating of leadership potential. In cell B8, enter 85 as the supervisor rating of understanding of duties. In cell B9, enter 80 as the supervisor rating of willingness to work hard.
- In cell B12, enter a function that determines the average of the values in the range B7:B10.
- Format cell B12 as a Number with no decimal places.
- In cell B13, enter an IF function that displays PROMOTION if the average score in cell B12 is greater than 80 and NO PROMOTION if the average score is less than 80.
- Format the contents of cell B13 as bold and centered.
- In cell B10, enter each of the following test scores: 75, 80, 85, 90, and 95.
- Close the workbook.