Up next in 10
Comparing Percentile, Percentrank and Quartile Functions in Excel
1K views
Nov 7, 2024
In this video, we dive deep into the PERCENTILE.INC and PERCENTILE.EXC functions in Excel, comparing their usage and differences to help you understand when to use each. We also explore the PERCENTRANK.INC and PERCENTRANK.EXC functions. Finally, we provide a brief overview of the QUARTILE (QUARTILE.INC and QUARTILE.EXC) functions, showing how they relate to percentiles and their importance in understanding data distribution.
View Video Transcript
0:00
hi everyone in this video we'll be
0:02
discussing the percentile and
0:04
percentrank functions when you try to
0:06
use one of these functions in Excel you
0:08
might see a warning that says this
0:09
function is available for compatibility
0:11
with Excel 2007 and earlier while these
0:14
functions are still available for
0:16
backward compatibility it's important to
0:18
consider using the newer functions
0:19
moving forward as they may not be
0:21
supported in future versions of excel
0:23
let's start by discussing the percentile
0:25
function which has been replaced by two
0:27
functions percentile.in C and percentile
0:31
EXC the percentile.in C function Works
0:34
similarly to the original percentile
0:36
function there's essentially no
0:38
difference between them both functions
0:40
calculate the CI percentile of a data
0:42
set which represents the value below
0:44
which is specified percentage of the
0:46
data Falls including the end points they
0:48
return the percentage of values that
0:50
fall below or are equal to the specified
0:52
value on the other hand the percentile
0:55
EXC function calculates the Ki
0:57
percentile of a data set excluding the
1:00
points such as 0 and one this function
1:02
Returns the value below which is
1:04
specified percentage of the data Falls
1:06
but it does not consider the maximum and
1:08
minimum values additionally it will
1:10
return an error for the minimum and
1:12
maximum values as there are no other
1:14
values to compare them to so use
1:17
percentile EXC when you want to exclude
1:19
the influence of the lowest and highest
1:21
values to focus on the main body of data
1:23
conversely use percentile.in C when you
1:26
need to include all values in your
1:28
analysis including the extreme value
1:29
values now that we've covered the
1:31
percentile function let's move on to the
1:33
percent rank function which is useful
1:35
for understanding how a specific value
1:37
ranks within a data set the percent rank
1:39
function has two versions percent rank.
1:42
Inc and percent rank. EXC the percent
1:45
Rank and percent rank. Inc functions
1:48
calculates the rank of a value within a
1:50
data set as a percentage including the
1:52
end points it Returns the percentage of
1:54
values that fall below or are equal to
1:56
the specified value on the other hand
1:59
the percent rank .exe function
2:01
calculates the rank of a value in a data
2:03
set as a percentage but it excludes the
2:05
end points this means it only considers
2:08
values that are below the specified
2:09
value excluding the maximum and minimum
2:12
values for example if you wanted to find
2:14
the percentile rank of the same score of
2:16
80 using percent rank. exe this function
2:19
will give you a percentage that reflects
2:21
the rank of 80 but without counting the
2:23
highest and lowest values in the data
2:24
set use percent rank. Inc when you want
2:27
to include all values including the
2:29
lowest and highest in your analysis use
2:32
percent rank. EXC when you want to focus
2:34
on the central data points excluding the
2:36
extremes to get a clear a picture of how
2:38
a specific value ranks within the rest
2:40
of the data set similar to the
2:42
percentile functions Excel provides
2:44
quartile quartile Inc and quartile.exc
2:48
functions the percentile function
2:50
calculates the CI percentile of a data
2:52
set provides a more granular analysis of
2:55
data by allowing you to specify any
2:57
percentage for example 20% 30% 95% Etc
3:02
the quartile function specifically
3:04
calculates the first second and third
3:07
quartiles of a data set which correspond
3:09
to the 25th 50th and 75th percentiles
3:12
respectively it divides the data set
3:14
into four equal parts the quartile and
3:17
quartile Inc functions calculate the
3:19
quartiles of a data set including the
3:21
endpoints while quartile.exc excludes
3:25
the endpoints from the calculation
3:27
thanks for watching
#Computers & Electronics
#Software