Microsoft Office Tutorials and References
In Depth Information
Case Problems
10. Change the value in cell E3 from $1,750 to $1,800 . If this represents the maximum
affordable monthly payment, use the values in the grid to determine the largest
mortgage for payment schedules lasting 15 through 35 years. Can any of the home loan
values displayed in the grid be repaid in 20 years at $1,800 per month?
11. Save and close the workbook, and then submit the fi nished workbook to your
instructor, either in printed or electronic form, as requested.
Create a
workbook that
automatically
grades a
driving exam.
Case Problem 4
Data File needed for this Case Problem: V6.xlsx
V-6 Driving Academy Sebastian Villanueva owns and operates the V-6 Driving
Academy, a driving school located in Pine Hills, Florida. In addition to driving, students
must take multiple-choice tests offered by the Florida Department of Motor Vehicles.
Students must answer at least 80 percent of the questions correctly to pass each test.
Sebastian has to grade these tests himself. Sebastian could save a lot of time if the test
questions were in a workbook and Excel totaled the test results. Sebastian has already
entered a 20-question test into a workbook. You will format this workbook and insert the
necessary functions and formulas to grade a student’s answers. Complete the following:
1. Open the workbook located in the Excel3\Case4 folder included with your Data V6
Files, and then save the workbook as V6 Driving Test .
2. In the Documentation sheet, enter your name in cell B3 and enter the date in cell B4.
3. In the Exam1 worksheet, format the questions and possible answers so that the
worksheet is easy to read. The format is up to you. At the top of the worksheet, enter a
title that describes the exam, and then enter a function that returns the current date.
4. Add a section somewhere on the Exam1 worksheet where Sebastian can enter
each student’s name and answers to each question. Design the workbook so that
Sebastian can always go back and review any student’s completed exam.
5. The answers for the 20 questions are listed below. Use this information to write
functions that will grade each answer, giving 1 point for a correct answer and 0
otherwise. Assume that all answers are in lowercase letters; therefore, the function that
tests the answer to the fi rst question should check for a “c” rather than a “C”.
Question
Answer
Question
Answer
Question
Answer
1
c
8
a
15
b
2
a
9
c
16
b
3
b
10
b
17
b
4
a
11
c
18
b
5
c
12
b
19
b
6
b
13
b
20
c
7 c 14 a
6. At the top of the worksheet, insert a formula to calculate the total number of correct
answers for each student.
7. Insert another formula that divides the total number of correct answers by the total
number of exam questions on the worksheet. Display this value as a percentage.
8. Enter a logical function that displays the message “PASS” on the exam if the
percentage of correct answers is greater than or equal to 80 percent; otherwise, the logical
function displays the message “FAIL”.
9. Test your worksheet with the following student exams. Which students passed and
which failed? What score did each student receive on the exam?
Juan Marquez
Question
Answer
Question
Answer
Question
Answer
1
a
8
a
15
b
2
b
9
c
16
b
Search JabSto ::




Custom Search