ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
Employee Table
Example Selection
2
3
YearShort NameEmployee DepotRate
4
2021BenDepot 1£81.45
What the Quartiles should be
Gauge Segments
5
2021DaveDepot 1£69.42
6
2021SamDepot 1£66.42
Rate Quartiles £
Rate Gauge Dial Position
Dave's Rate Position on the Guage if yeaer 2021 as selected
7
2021AdamDepot 2£72.96Depot 1 Q1 202167.92041545Red Min Pointer Position0
8
2021AndyDepot 2£62.04Depot 1 Q2 202169.42225385Red Max Pointer Position24
Dave's 2021 Rate is £69.42 so he falls into the top of the Q2 - Q3 percentile range i.e. the yellow segment
9
2021TomDepot 2£39.60Depot 1 Q3 202175.43492743Orange Min Pointer Position25
We need to move the pointer to a proportional value inside the segment
10
Etc….Orange Max Pointer Position49
11
Depot 2 Q1 202150.82073729Yellow Min Pointer Position50Quartile 2 Segment
12
Depot 2 Q2 202139.60242518Yellow Max Pointer Position74
Point Min+(Point Max - Point Min)*(Rate - Range Min)/(Range Max - Range Min)
13
Depot 2 Q3 202167.50161886Green Min Pointer Position75
14
Green Max Pointer Position100
15
This is to show which quartile Dave's rate sits in compared to his colleagues dial gauge below
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Yellow Range
33
Rate Input£69.42
34
35
Q1 Quartile Range Min£64.04
36
Pointer Min50.00
37
Q2 Quartile Range Max£69.85
38
Pointer Max74.00
39
40
Pointer Position Value72.25
41
42
Pointer Position Value Calculated as below
43
Quartile 2 Segment
44
Point Min+(Point Max - Point Min)*(Rate - Range Min)/(Range Max - Range Min)
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100