Question

The data on the scores obtained by students in five different entrance exams have been collected from 50 colleges and they are provided below. Create a PivotTable in Excel to display the number of students who took each exam and the average score for students in each exam.
Exams Scores Exams Scores
SAT 520 MCAT 487
ACT 400 GRE 267
MCAT 580 GMAT 455
GRE 280 SAT 528
GMAT 540 GMAT 536
ACT 356 SAT 469
MCAT 520 GRE 455
GRE 355 MCAT 520
GMAT 480 GRE 489
SAT 574 ACT 455
GRE 396 MCAT 589
GMAT 450 GRE 500
ACT 420 GMAT 500
MCAT 560 SAT 528
GRE 297 GMAT 480
GMAT 520 SAT 475
SAT 489 GMAT 570
GMAT 500 ACT 480
SAT 566 MCAT 567
GRE 451 GRE 546
GMAT 460 GMAT 544
ACT 422 SAT 420
MCAT 550 GMAT 453
GRE 310 SAT 510
ACT 384 GRE 473


a. Which exam did most students attempt?
b. Which exam has the highest average score?
c. Use the PivotTable to determine the exam attempted by the student with the highest score.

What is the exam attempted by the student with the lowest score?

Answer 1

Answer:

Step-by-step explanation:

1. Add data into excel and name both the columns

2. Select top cell and press Ctrl+T to convert data into table

3. Click on any cell on the table and click pivot table in the 'Insert' tab. You will get a pivot table frame.

a. Which exam did most students attempt?

i. Move 'Test name' field to row in the pivot table. Also move test scores field to the value table.

ii. This by default will give you the sum of scores. Click on the field under 'value' table and change it from 'sum of values' to 'count of values'  

The answer is GMAT taken by 13 students

b. Which exam has the highest average score?

i. From the pivot table created in part a, change the field setting to 'Average' instead of count.

The answer is GMAT with 499.07 average

c. Use the pivot table to determine the exam attempted by the student with highest score

i. In the pivot created above, change field setting to 'Max'

The answer is MCAT with 589

What is the exam attempted by the student with lowest score.

i. Change the field setting in the pivot to 'Min'

The answer is GRE with 267