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 ﬁ 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 ﬁ 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