The data on the scores obtained by students in five different entrance exams have been collected from 50 colleges and they are p
rovided 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?
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?
1 answer:
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'
<em><u>The answer is GMAT taken by 13 students</u></em>
<em></em>
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.
<u><em>The answer is GMAT with 499.07 average</em></u>
<u><em /></u>
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'
<u><em>The answer is MCAT with 589</em></u>
What is the exam attempted by the student with lowest score.
i. Change the field setting in the pivot to 'Min'
<u><em>The answer is GRE with 267</em></u>
<em></em>
<em></em>
You might be interested in
Answer:
a. Descriptive statistics for the sample.
n= 9
mean X[bar]= 20.36
median Me= 20.30
min: 19.70
max: 21.40
standad deviation S= 0.61
b. 95% Confidence interval [19.88; 20.83]
c. Decision: Support H₀
Step-by-step explanation:
Hello!
I don't have excel so I cannot install PHstat, I've used a statistic software called Infostat for the calculations.
There was a random sample of 9 bottles of shampoo taken to test if the process is operating correctly. If it is, the bottles should weight on average 20 ounces (this would be our study parameter)
The study variable is X: the weight of a bottle of shampoo.
X~N(μ;σ²)
a.
Descriptive statistics for the sample.
n= 9
mean X[bar]= 20.36
median Me= 20.30
min: 19.70
max: 21.40
standad deviation S= 0.61
b. 95% Confidence interval.
Since the variable has a normal distribution and the population variance is unknown, the statistic to use to construct this interval is the Students t:
X[bar] ± * (S/√n)
20.36 ± * (0.61/√9)
[19.88; 20.83]
c. You have to test the hypothesis that the production is operating correctly, if it is so, then:
H₀: μ = 20
H₁: μ ≠ 20
α: 0.05
The statistic value is t= 1.74
p-value: 0.1198
When you use the p-value to decide on a statistical hypothesis, you should always contrast it with the level of significance using the following rule:
If p-value ≤ α, then you reject the null hypothesis.
If p-value > α, then you do not reject the null hypothesis.
Since the p-value is greater than the significance level, you do not reject the null hypothesis. This means that the average weight of the shampoo bottles is 20 ounces, i.e. the process is operating correctly.
I hope it helps!