0:00
hi everyone in this video we'll
0:02
calculate the percentile scores for each
0:04
student in our data set and use
0:06
conditional formatting to highlight
0:07
those who rank above the 75th percentile
0:10
let's get started the percent rank
0:12
function calculates the rank of a
0:13
specific value within a range as a
0:15
percentage of the total values in that
0:17
range this function is particularly
0:19
useful for understanding how a
0:21
particular score or value compares to
0:23
others for example to find the
0:25
percentile rank of the score 128 located
0:28
in celb 2 you would use use the formula
0:30
as shown on the screen if the result is
0:33
0.541 it indicates that 54.1% of the
0:36
scores in the specified range are below
0:39
128 the percent rank function returns
0:41
values between 0 and 1 to express this
0:44
value as a percentage simply multiply
0:47
the result by 100 if you want to round
0:49
the result to two decimal places you can
0:51
enter two as the third parameter this
0:53
will provide the percentile rank as a
0:55
percentage making it easier to interpret
0:58
now use the fill handle to drag the
1:00
formula down to the last data row b26
1:03
make sure to modify the formula to use
1:05
absolute references for the first
1:06
parameter this will ensure that the
1:08
reference remains constant for the data
1:10
set when you drag or copy and paste it
1:12
into the rows below next we will explore
1:15
the percentile function this function
1:17
allows us to calculate specific
1:19
percentiles for a given range of data it
1:21
determines the kth percentile of a data
1:23
set which represents the value below
1:25
which is specified percentage of the
1:27
data false for example to find the 25th
1:30
percentile you would use
1:32
0.25 to find the 50th percentile which
1:35
is also known as the median you would
1:37
0.50 and to find the 75th percentile you
1:42
0.75 the result of the percentile
1:44
function represents the value below
1:46
which the specified percentage of the
1:48
data set Falls for instance if the
1:50
result for the 25th percentile is 116
1:54
this means that 25% of the scores in the
1:56
data set are below 116 similarly if the
1:59
75th percentile result is 135 it
2:03
indicates that 75% of the scores are
2:06
135 now we will use conditional
2:09
formatting to highlight the data that is
2:10
ranked above the 75th percentile the
2:13
conditional formatting will check each
2:14
row using the percentile function to
2:17
confirm that it's working properly I
2:19
first tested the formula in column F
2:21
next select the data go to the Home tab
2:24
and click on conditional formatting then
2:26
choose new rule the new formatting rule
2:29
dialogue box will appear select the rule
2:31
typee use a formula to determine which
2:33
cells to format and enter the formula as
2:35
shown on the screen now click on format
2:38
and apply your desired formatting for
2:40
this example I choose a white bold font
2:42
with a black background once you've set
2:44
your formatting click okay you should
2:46
now see the formatting applied to the
2:48
data all the values that score above the
2:50
75th percentile have been highlighted
2:53
thanks for watching if you found this
2:55
video helpful please like share and
2:57
subscribe for more Excel tips and tricks
2:59
your support means a lot
