High School Educator Information
Last Name
Kato
Han
Yoon
Lopez
Yamamoto
Lee
Garcia
Thomas
Gao
Cruz
Cruz
Ortiz
Rodriguez
Ma
Young
Jhadav
Jayaraman
Sanchez
Li
He
Mehta
Hamade
Bhatnagar
Takahashi
Young
Park
Matsumoto
Abdoo
Diaz
Rodriguez
Ito
Banerjee
Matsumoto
Kadam
Patil
Sasaki
Robinson
He
Lin
Thomas
Kimura
Jackson
Smith
Williams
Hire Date
12/22/2007
10/20/2015
4/23/2014
7/30/1999
12/29/2002
4/16/2014
4/23/2000
10/2/2002
7/16/2006
8/6/2011
9/11/2006
8/16/2005
8/4/2008
1/21/2010
5/4/2010
8/3/2006
10/14/2003
5/30/2009
5/30/1999
1/17/2002
1/13/2014
8/17/2003
11/30/2011
12/17/2010
4/17/2001
5/25/2011
5/11/2005
9/9/2006
12/18/2011
2/12/2015
10/10/2009
5/25/1999
8/30/1995
7/14/2013
8/22/2007
6/21/2014
1/23/2010
9/26/2005
8/25/2015
4/29/2001
4/16/2002
11/17/2008
2/21/2014
2/28/2007
Salary
$ 61.065,00
$ 59.913,00
$ 64.052,00
$ 45.305,00
$ 52.691,00
$ 38.827,00
$ 57.307,00
$ 48.384,00
$ 37.575,00
$ 60.913,00
$ 58.134,00
$ 57.394,00
$ 43.815,00
$ 36.288,00
$ 50.920,00
$ 55.953,00
$ 64.261,00
$ 34.301,00
$ 55.257,00
$ 44.450,00
$ 35.259,00
$ 58.478,00
$ 35.015,00
$ 53.994,00
$ 40.878,00
$ 31.386,00
$ 41.571,00
$ 52.633,00
$ 37.769,00
$ 57.269,00
$ 36.029,00
$ 46.440,00
$ 64.508,00
$ 40.637,00
$ 57.902,00
$ 53.385,00
$ 53.359,00
$ 34.434,00
$ 33.971,00
$ 31.168,00
$ 43.802,00
$ 55.820,00
$ 55.430,00
$ 52.630,00
Township Salary Rank
Veigo
Jackson
Jackson
Veigo
Veigo
Veigo
Veigo
Acorn
Jackson
Veigo
Acorn
Jackson
Veigo
Jackson
Veigo
Veigo
Acorn
Acorn
Veigo
Acorn
Jackson
Jackson
Jackson
Veigo
Veigo
Jackson
Jackson
Acorn
Veigo
Veigo
Veigo
Acorn
Jackson
Jackson
Veigo
Acorn
Acorn
Veigo
Acorn
Acorn
Jackson
Jackson
Acorn
Acorn
Lumus
Jones
Perry
Officer
Spalding
Lockridge
1/30/2006
7/30/2013
8/20/1998
12/10/2001
1/1/2013
8/12/2002
$
$
$
$
$
$
55.547,00
60.994,00
43.780,00
54.884,00
48.699,00
39.329,00
Jackson
Jackson
Jackson
Acorn
Veigo
Veigo
Summary Information
Count
Total payroll
Average Salary
Hired before 1/1/2005
Acorn Township High School Teachers
Count
Total Payroll
Average Salary
Hired before 1/1/2005
Quartile
Salary (QUARTILE.EXC)
1
2
3
6-8 Test Scores
Student ID Test Score
1075
725
1912
325
4196
648
4483
750
6237
585
6284
325
6285
707
6312
684
6353
407
6747
501
6778
282
7025
596
7284
789
7486
621
7717
600
7788
406
8362
234
8843
650
9573
532
9849
550
1625
417
2309
524
2513
737
2834
275
4470
800
5713
353
6695
523
7224
121
7670
641
7718
544
7825
715
8049
799
9410
739
2137
702
2877
700
3817
400
4562
321
4938
187
5701
357
6035
322
6076
665
6141
301
7601
651
Township School # Days Absent
Acorn
24
2
Acorn
24
10
Acorn
24
6
Acorn
24
0
Acorn
24
2
Acorn
24
9
Acorn
24
1
Acorn
24
2
Acorn
24
10
Acorn
26
6
Acorn
26
5
Acorn
26
2
Acorn
26
9
Acorn
26
0
Acorn
26
1
Acorn
26
3
Acorn
26
6
Acorn
26
0
Acorn
26
3
Acorn
26
0
Jackson
55
3
Jackson
55
3
Jackson
55
3
Jackson
55
10
Jackson
55
2
Jackson
55
6
Jackson
56
3
Jackson
56
7
Jackson
56
8
Jackson
56
5
Jackson
56
0
Jackson
56
0
Jackson
56
8
Veigo
83
4
Veigo
83
5
Veigo
83
0
Veigo
83
1
Veigo
83
3
Veigo
83
9
Veigo
83
5
Veigo
84
5
Veigo
84
9
Veigo
84
7
Max Test Score
800
Sample Size
50
Standard Deviation
Variance
Days Absent
Frequency
0
5
10
7653
7708
8244
8608
9209
9596
8765
411
253
640
479
462
351
555
Veigo
Veigo
Veigo
Veigo
Veigo
Veigo
Veigo
84
84
84
84
84
84
84
10
7
8
5
3
2
2
Average
517
Correlation
Sample High School SAT Scores
SAT Score High School 1
SAT Score High School 2
SAT Score High School 3
1436
2065
1944
2014
1229
2163
2308
2134
2010
1763
1431
1965
1544
1710
2243
1347
1727
1815
1383
1310
2005
1504
2150
1250
1677
2163
1835
1313
1225
2172
1245
2309
2037
2223
2155
1673
1794
1581
2273
1639
2343
1375
1882
1519
1444
1417
1938
2222
1698
1839
1669
2042
1513
1601
1691
1878
1702
2324
1778
2120
1224
1204
1311
1674
2255
2098
1963
1821
1259
1834
1941
2174
2344
1585
2220
2322
2183
1977
2241
1774
1747
1253
1209
1213
1470
2352
2244
1931
2325
1981
1608
2160
1560
2283
1959
1345
1670
2247
1386
2183
1678
1343
1383
1359
2396
1849
2139
1299
1220
1569
2304
1430
1965
2188
2084
2030
2313
2398
1761
2112
2025
2395
1885
1400
1757
2366
2197
2235
2252
1531
1490
2200
1572
1627
1580
1606
2149
1364
2352
2256
1361
2332
2250
1232
1485
1228
1410
2008
2272
1233
Test scores Analysis
Student ID SAT Score
2500
4437
3028
4276
4366
1345
1673
1795
1704
3740
2475
4634
4084
3306
4167
2540
4945
4193
4986
2543
2238
1809
1317
4971
2758
2030
3721
4999
3105
1461
3635
1615
2047
4829
4855
3009
1234
3633
3377
4529
4602
1286
1114
Absences
1436
2014
2308
1763
1544
1347
1383
1504
1677
1313
1245
2223
1794
1639
1882
1417
1698
2042
1691
2324
1224
1674
1963
1834
2344
2322
2241
1253
1470
1931
1608
2283
1670
2183
1383
1849
1220
1430
2084
2398
2025
1400
2197
Bins
6
0
0
3
7
3
7
1
1
8
7
1
7
4
9
7
0
1
5
8
3
1
4
2
2
1
3
9
7
3
8
6
0
5
9
4
6
8
3
1
4
3
2
0
5
10
1017
4718
2053
3546
4891
3668
2302
3511
3842
1840
1760
1030
2556
2812
4226
1246
3095
4156
4909
4435
4467
1850
4401
2128
2983
2080
1251
1003
3477
2784
2028
3651
2369
4672
1312
1169
3165
1016
4048
2483
3801
2635
3044
3919
4209
4309
4562
1531
1490
2200
1572
1627
1580
1606
2065
1229
2134
1431
1710
1727
1310
2150
2163
1225
2309
2155
1581
2343
1519
1938
1839
1513
1878
1778
1204
2255
1821
1941
1585
2183
1774
1209
2352
2325
2160
1959
2247
1678
1359
2139
1569
1965
2030
1761
3
2
0
0
10
5
3
4
2
0
1
8
10
1
7
7
6
7
8
8
9
10
7
10
3
10
9
10
10
7
6
8
8
10
3
5
9
2
2
5
5
3
8
8
3
6
8
3745
4329
1129
4334
2224
3130
3546
4543
2130
3552
2038
4788
3286
3047
3781
3600
3491
3689
1318
4657
1526
1439
3502
4031
2421
1353
2706
1530
3556
3366
1564
1461
4463
3638
4087
3965
3576
2762
1397
2232
1997
2334
2991
3463
3655
3888
4754
2395
1757
2235
2149
1364
2352
2256
1361
2332
2250
1944
2163
2010
1965
2243
1815
2005
1250
1835
2172
2037
1673
2273
1375
1444
2222
1669
1601
1702
2120
1311
2098
1259
2174
2220
1977
1747
1213
2244
1981
1560
1345
1386
1343
2396
1299
2304
6
1
9
0
4
10
10
0
7
10
8
10
8
10
6
4
7
7
10
2
10
7
4
4
10
8
9
9
1
10
9
9
10
2
6
3
6
10
0
2
8
4
9
6
8
5
6
2283
4803
1236
2579
2987
2713
3764
2321
3590
2469
1985
2389
2663
2188
2313
2112
1885
2366
2252
1232
1485
1228
1410
2008
2272
1233
9
4
10
8
0
4
2
7
0
5
9
1
7
COM S 113
(Introduction to Spreadsheets and Databases)
Additional Topics II
(Chapter 8)
Statistical Functions
1
What will we learn? Why?
“Conditional” math and statistical functions
◦ You often only want to sum, average, etc. values that meet certain
conditions
Calculate relative standing with statistical functions
◦ Compare individual value to rest of dataset (e.g. Your ranking in
your class based on exam scores)
Measure central tendency
◦ Sometimes you want to describe your data with statistics like
average and variance
• Create a histogram
◦ Visual display of distribution of data
2
Case Study for Chapter 8
You are the superintendent of K-12 schools in Erie, PA. You are to evaluate
teachers’ performance ranking and salary quartiles. You also have to assess
students’ standardized testing performance.
3
Conditional Math
and Statistical
Functions
4
Conditional Math & Statistical Functions
We have already seen math and statistical functions such as SUM and AVERAGE
Excel has other versions of these functions that perform the same function IF a
certain condition is met
◦ Example: Sum all salaries IF the township equals “Jackson”
Here are some of the conditional functions:
◦ SUMIF
◦ AVERAGEIF
◦ COUNTIF
When more than one condition must be met, use:
◦ SUMIFS
◦ AVERAGEIFS
◦ COUNTIFS
5
Conditional Math & Statistical Functions
The conditional functions require these arguments:
◦ the criteria to meet
◦ e.g. “=Jackson” “>40,000”
◦ the range of cells to compare to the criteria
◦ some functions such as SUMIF also need an
argument to specify the range of values to sum
Examples:
◦ SUMIF(range, criteria, sum_range)
◦ SUMIFS(sum_range, range1,criteria1, range2,criteria2,…)
6
Use ConditionalMath and StatisticalFunctions
How it’s different from the Database
Functions? (Dsum, Daverage, etc.)
To use an Advanced filter, you
must first create a “criteria
range”
◦ A “criteria range” is a separate
area in the sheet that lists the
criteria to filter by
◦ You can easily change the criteria
and then re-apply the filter
Criteria Range
Filtered Data
8
Example of DSUM Database Function
Database functions
=DSUM(database,field,criteria)
“Database functions” perform common
calculations using an “advanced filter” to
select the data to use
Some of the most common database
functions:
DSUM
DAVERAGE
DMIN
DMAX
DCOUNT
9
Calculate Relative Standing
With Statistical Functions
You can determine the “relative standing” of values compared (relative
to) other values
Examples:
◦ Rank the values from 1 to …
◦ Get the percentile of a value (the 90th percentile)
10
2 Types of Relative Standing Functions
1) Get the Relative Standing of a value
◦
RANK functions calculate ranking for individual values within a list
◦
◦
Example: the salary $61,065 ranks 4th among salaries
PERCENTRANK functions calculate rank as a percentile of the range of values
◦
Example: the salary 68,750 is in the 90th percentile rank
11
RANK.EQ vs. RANK.AVG
RANK.EQ ranks values and gives equal values the same rank
◦ Example:
A list has the values 50,30,70,50
70 ranks 1st, the 50s rank 2nd, 30 ranks 4th
RANK.AVG assigns an average rank to equal values
◦ Example:
In the list above, the 50s would rank 2.5
12
Calculate Relative Standing with Statistical Functions
Same
Salary
RANK.AVG
RANK.EQ
PERCENTRANK INC vs.
PERCENTRANK EXC functions
Some functions calculate values between 2 numbers
◦ e.g. PERCENTRANK calculates between 0% and 100%
INC means calculate values between the 2 numbers including the 2
numbers
EXC means calculate values between the 2 numbers excluding the 2
numbers
Examples:
◦ PERCENTRANK.INC includes 0% and 100%
◦ PERCENTRANK.EXC excludes 0% and 100%
14
Example of Percentrank
PERCENTRANK.INC
PERCENTRANK.EXC
15
Example of Rank and Percentrank
16
2 Types of Relative Standing Functions
(continued)
2) Get the Value for a particular relative standing
PERCENTILE functions identify the value at the kth percentile
◦
◦
◦
Example: the 50th percentile salary is 46,363
QUARTILE identify the value at a specific quartile
◦
◦
Quartiles 1, 2, and 3 are the 25th, 50th, and 75th percentile
Example: the 1st quartile salary is 43,750
17
Example of Percentile and
Quartile
18
19
Measuring
Central Tendency
22
Measuring Central Tendency
Population—dataset containing all the data to be evaluated
Sample—smaller, more manageable segment of the population
Variance—measure of a dataset’s dispersion
Standard deviation—measure of how far the data sample is distributed
around the mean
23
Measuring Central Tendency
24
Measure Central Tendency
Variance functions:
◦ VAR.P—calculates the variance based on the population
=STDEV.P(number1,number2)
◦ VAR.S—calculates the variance based on a sample
=STDEV.S(number1,number2)
Measure Central Tendency
STDEV.S
VAR.S
More Complex Statistical
Functions
Analysis ToolPak ─ an add-in that contains tools for performing complex
statistical analyses
◦ The Analysis ToolPak must be enabled, then the tools can be selected as
usual
◦ Tools include Correlation, T-Tests, etc.
27
The correlation coefficient (a value
between -1 and +1) tells you how strongly
two variables are related to each other.
- A correlation coefficient of +1 indicates a perfect positive correlation. As variable X
increases, variable Y increases. As variable X decreases, variable Y decreases.
28
Measure Central Tendency
CORREL—determine the strength of a relationship between two
variables
=CORREL(array1,array2)
CORREL
COPYRIGHT © 2017 PEARSON EDUCATION, INC.
FREQUENCY—
determines the frequency
distribution of a dataset,
Determine how often a set of number appears in
specific range
◦ =FREQUENCY(Data_array,Bins_array)
FREQUENCY
30
Creating a Histogram
Histogram—visual display of tabulated frequencies
◦ Requires bins to tabulate the data and returns a frequency distribution table
31
Creating a Histogram
32
Purchase answer to see full
attachment