Test

31 You: 79
47
54 Are you above average?
55 Mean 67.7
57
58 Are you above the 75th percentile?
60 Percentile Marker for 75% 75%
61 Marker for 75th percentile: 78.75
63
68
68
68
72
76
78
79
84
89
91
95
 Percentiles/Quartiles
Marker that divides the sorted data set and says what % are above and what % are below
Percentiles
Provides information about how the data are spread over an interval from smallest to largest value.
Marks the point in the ascending sorted list (an actual number) where approximately X% of the numbers are
below and approximately 1-X% of the numbers are above the marking point.
For quantitative data without a lot of duplicates
Use PERCENTILE.EXC function, which uses formula: P%*(n+1) to get the position
Note: There is NOT just one way to calculate percentiles. Percentile calculations are estimations, this is why we
use the word "approximate" when we talk about percentiles
Quartiles
Create 3 Value Marking Points to divide data set into 4 parts
Use QUARTILE.EXC function
Percentile Rank
PERCENTRANK.EXC opposite of PERCENTILE.EXC. Whereas PERCENTILE.EXC calculates the marker value when
you give it a P%, PERCENTRANK.EXC calculates P% when you give it a value.
Rank
Counting, 1st, 2nd, 3rd for "Biggest to Smallest" or "Smallest to Biggest"
Use RANK.AVG or RANK.EQ functions

Entrance Exam
Number Name Scores
1 Jo 12
2 Don 54
3 Bob 56
4 Minny 62
5 Gigi 69
6 Lim 72
7 Chris 73
8 Shelia 74
9 Tommy 78
10 Fran 82
11 Homer 83
12 Tina 87
13 Sue 88
14 Sioux 88
15 Chin 91
16 Your Name 92
17 Tyrone 95
18 Karen 100

Entrance Exam
Number Name Scores
1 Jo 12
2 Don 54
 3 Bob 56
4 Minny 62
5 Gigi 69
6 Lim 72
7 Chris 73
8 Shelia 74
9 Tommy 78
10 Fran 82
11 Homer 83
12 Tina 87
13 Sue 88
14 Sioux 88
15 Chin 91
16 Your Name 92
17 Tyrone 95
18 Karen 100

Entrance Exam
Number Name Scores
1 Jo 12
2 Don 54
3 Bob 56
4 Minny 62
5 Gigi 69
6 Lim 72
7 Chris 73
8 Shelia 74
9 Tommy 78
10 Fran 82
11 Homer 83
12 Tina 87
13 Sue 88
14 Sioux 88
15 Chin 91
16 Your Name 92
17 Tyrone 95
18 Karen 100

Entrance Exam
Number Name Scores
1 Jo 12
2 Don 54
3 Bob 56
4 Minny 62
5 Gigi 69
 6 Lim 72
7 Chris 73
8 Shelia 74
9 Tommy 78
10 Fran 82
11 Homer 83
12 Tina 87
13 Sue 88
14 Sioux 88
15 Chin 91
16 Your Name 92
17 Tyrone 95
18 Karen 100

Entrance Exam
Number Name Scores
1 Jo 12
2 Don 54
3 Bob 56
4 Minny 62
5 Gigi 69
6 Lim 72
7 Chris 73
8 Shelia 74
9 Tommy 78
10 Fran 82
11 Homer 83
12 Tina 87
13 Sue 88
14 Sioux 88
15 Chin 91
16 Your Name 92
17 Tyrone 95
18 Karen 100

Entrance
Exam
Scores
32
43
86
63
36
36
52
88
51
89
44
39
10
74
82
20
14
43
10
41
44
86
89
78
30
8
13
4
38
83
70
37
69
86
63
23
20
23
61
60
93
75
29
11
29
2
25
94
73
56
6
33
27
64
5
72
87
2
33
48
12
57
42
41
89
63
26
78
86
60
34
7
39
10
94
47
71
29
16
72
45
1
10
81
51
78
20
32
60
82
64
77
68
43
 8
83
15
79
65
74
2
78
59
79
91
83
62
61
59
45
62
27
65
82
48
70
91
71
92
71
56
60
71
96
66
55
69
78
12
57
71
75
45
69
68
36
67
69
70
69
24
13
88
68
96
9
85
69
18
72
74
91
74
93
83
80
87
78
61
71
51
69
36
73
71
73
74
41
40
86
74
92
52
76
64
85
96
81
75
73
66
79
65
78
77
76
44
52
65
65
74
36
37
62
38
76
52
65
38
43
78
72
63
57
86
4
77
77
59
70
61
81
94
84
69
78
2
69
80
83
68
68
76
73
38
28
97
50
48
 66
66
10
72
100
73
68
71
73
85
75
82
73
67
37
79
9
73
59
18
32
75
79
87
41
69
65
81
44
1
70
45
68
67
73
56
46
76
70
35
65
71
94
3
9
75
51
2
71
51
81
66
77
43
68
77
74
4
80
39
69
73
66
69
61
62
33
36
89
63
70
28
54
48
71
82
64
58
27
74
71
81
62
68
16
72
52
51
60
69
46
79
88
72
63
14
35
72
71
73
76
72
93
10
73
45
58
43
19
45
74
49
38
45
77
66
75
79
50
93
64
82
87
73
15
13
58
79
82
36
75
31
72
39
74
15
65
41
77
87
75
86
77
75
75
70
72
59
16
36
37
65
44
56
79
64
74
70
27
23
20
72
48
65
63
90
58
49
78
72
71
70
62
78
51
81
36
63
74
38
71
41
56
72
92
86
83
87
82
86
67
71
75
57
61
81
47
72
9
37
99
57
76
72
73
72
84
56
87
76
81
48
59
88
82
26
80
67
77
38
47
88
11
36
7
 80
68
82
37
58
71
54
31
50
73
97
49
38
50
40
41
48
80
43
16
76
77
65
75
66
73
26
70
75
78
70
88
67
87
73
76

Rand Formula:
41
 data set and says what % are above and what % are below

how the data are spread over an interval from smallest to largest value.
nding sorted list (an actual number) where approximately X% of the numbers are
-X% of the numbers are above the marking point.
ut a lot of duplicates
on, which uses formula: P%*(n+1) to get the position
way to calculate percentiles. Percentile calculations are estimations, this is why we
" when we talk about percentiles

nts to divide data set into 4 parts

e of PERCENTILE.EXC. Whereas PERCENTILE.EXC calculates the marker value when

ANK.EXC calculates P% when you give it a value.

Biggest to Smallest" or "Smallest to Biggest"

functions

Company Policy with percentile 75%

Count, n Percentiles
Position in List Formula for position = P%*(n+1
How far between 14 & 15? 0
Difference between 14 & 15
How far from 14?
Percentile Marker 0
Percentile Marker with PERCENTILE.EXC 0
0
Company Policy with quartile 3
Quartile Marker with QUARTILE.EXC

Your Score 89
Percentile rank with PERCENTRANK.EXC
0

Company Policy with percentile 50% 0.5

Percentile Marker with PERCENTILE.EXC 0
Median = 50% Percentile Marker = "in the middle" 0
Average Middle Two 0

Quartile
Quartile 1 1
Quartile 2 2
Quartile 3 3

Quintiles (5 parts with 4 markers): P%

Marker 1 0.2
Marker 2 0.4
Marker 3 0.6
Marker 4 0.8

Deciles (10 parts with 9 markers P%

Marker 1 0.1
Marker 2 0.2
Marker 3 0.3
Marker 4 0.4
Marker 5 0.5
Marker 6 0.6
Marker 7 0.7
Marker 8 0.8
Marker 9 0.9

Percentile Rank
5.20%
10.50%
15.70%
21.00%
26.30%
 31.50%
36.80%
42.10%
47.30%
52.60%
57.80%
63.10%
68.40%
68.40%
78.90%
84.20%
89.40%
94.70%

Percentile 75.00% Video about another method f

PERCENTILE.EXC, exclude 1 and 0, where 1 = max and 0 = min Excel 2010 Statistics #27: Excel
PERCENTILE.INC, include 1 and 0, where 1 = max and 0 = min https://www.youtube.com/wa
**Algorithm for PERCENTILE.INC: Position = p*n+(1-p)
PERCENTILE.INC
Inclusive Example: Marker P%
0
0.25
0.5
0.75
1

Marker Quartile
0
1
2
3
4

Function % Result
PERCENTILE.EXC 0.75
PERCENTILE.INC 0.75
QUARTILE.EXC 3
QUARTILE.INC 3
PERCENTRANK.EXC 74
PERCENTRANK.INC 74
Percentiles
Formula for position = P%*(n+1)
Video about another method for calculating percentile:
Excel 2010 Statistics #27: Excel 2010 Functions for Percentile, Quartile and Rank
https://www.youtube.com/watch?v=XllEMSjVGGk

PERCENTILE.EXC PERCENTILE.INC
Marker P% Long Hand Formula:
0 13.75 <<== Position
0.25 88 <<== Marker
0.5
0.75
1
Sales Reps Sales RANK.EQ Percentile Rank RANK.AVE
Sioux $57,635.00
Chris $77,866.00
Sue $72,926.00
Phil $61,073.00
Herb $76,079.00
Tina $76,363.00
Luong $72,097.00
Fred $56,871.00
Chin $74,487.00
Choi $74,487.00

RANK.EQ deals with ties as equal, RANK.AVE deals with ties

by averaging the positional values; ex: (4+5)/2 = 4.5

RANK.EQ or RANK.AVE function:

ref is all the values,
number is the particular value to rank,
0 = big to small (order)