Up next in 10
Two Ways to Master Tied (or Group) Rankings in Excel
4K views
Nov 7, 2024
In this comprehensive Excel tutorial, you'll learn how to effectively handle tied rankings. Discover how to extract the top 10 highest-value items, even when multiple entries share the same value. We'll guide you step-by-step through using the LARGE, ROW, INDEX, MATCH, and FILTER functions to achieve accurate rankings. Whether you're working with large datasets or just need a simple solution, this tutorial has you covered. Don’t miss out on these essential Excel tips—watch now and elevate your ranking skills to the next level! 00:00 Intro 00:23 Explaining LARGE Function 00:54 Introducing ROW Function 01:52 The MATCH Function 02:23 The INDEX Function 03:03 The FILTER Function 04:09 Alternate to FILTER Function for Previous Versions of Excel 05:14 Assign Tied (or Group) Ranks 06:24 A Simpler Method For Tied (or Group) Rankings
View Video Transcript
0:00
hello and welcome to this Excel tutorial
0:03
today we're going to explore how to
0:04
handle tide rankings specifically when
0:06
ranking products by price if you've ever
0:09
had multiple items with the same price
0:11
and wanted to rank them accurately in
0:12
Excel this tutorial is for you we'll be
0:15
working with a data set containing
0:16
product names and their corresponding
0:18
prices and I'll show you how to extract
0:20
the top 10 highest pric products even
0:23
when prices tie let's start by
0:25
identifying the top 10 highest prices in
0:27
our data set using the large function
0:29
the large function Returns the K largest
0:31
value from a data range where K is the
0:34
position you specify the first argument
0:36
of the large function specifies the
0:38
range of cells containing the numbers
0:39
you want to evaluate while the second
0:41
argument determines the position of the
0:43
largest value you want to return for
0:45
example if you want to find the largest
0:47
number in a Range you would use one as
0:49
the second argument to find the second
0:51
largest you would use two and so on
0:54
instead of manually entering the value
0:56
for the second argument you can use the
0:57
row function to automate this process
1:00
the row function Returns the row number
1:02
of a specified reference for example the
1:04
row function containing a one returns
1:06
one and the row function containing A2
1:09
returns two and so on by using the row
1:12
function within the large function you
1:14
can dynamically generate the top end
1:16
values this approach eliminates the need
1:18
to manually adjust the second argument
1:20
for each row making your formula more
1:23
efficient and scalable so when we start
1:25
with a row function containing a one it
1:28
returns one the large function then
1:30
fetches the highest price because it's
1:31
looking for the largest value as we drag
1:34
the formula down the cell reference in
1:36
the row function automatically updates
1:37
to A2 which will fetch the second
1:40
highest price and this process continues
1:42
down the list this Dynamic use of row
1:45
allows us to easily extract the top 10
1:47
prices without manually specifying the
1:49
rank for each formula now we displayed
1:51
some highest prices let's move on to
1:54
extracting the corresponding product
1:55
names this is where the index and match
1:58
functions come into play the match
1:59
function searches for a specified value
2:01
in a range and Returns the relative
2:03
position of that value within the range
2:06
it's often used to find the position of
2:07
a value within a list the first argument
2:10
of the match function is the value you
2:12
want to search for the second argument
2:14
is the range of cells where you want to
2:15
search the third argument is optional
2:17
and specifies how Excel matches the
2:19
lookup value zero means an exact match
2:23
the index function is used to return the
2:25
value of a cell at a specified position
2:28
in a Range the first argument is the
2:30
range of cells where the data is stored
2:32
second argument specifies the row number
2:34
in the range to return a value let's use
2:36
the match function as the second
2:38
argument in the index function the match
2:40
function will locate the position of a
2:42
price in a Cell in column F an index
2:44
will use that position to return the
2:46
corresponding product name from the
2:47
range in column
2:51
A now ranking becomes a bit tricky when
2:54
multiple products have the same price
2:56
while this combination works well for
2:58
Unique ranks it only Returns the first
3:00
occurrence of a product name in the case
3:02
of Tide ranks to address this we need an
3:05
additional step using the filter
3:07
function to ensure that all tied
3:09
products are displayed before we apply
3:11
the filter function let's first visually
3:13
distinguish The Identical prices using
3:15
conditional formatting this will help us
3:17
easily identify where the filter
3:19
function needs to be applied delete the
3:21
formulas and cells that show the same
3:23
product name for identical prices and
3:25
then enter the filter formula as shown
3:27
on the screen the filter function
3:29
returns a range of values that meet
3:30
specific criteria the first argument is
3:33
the range of cells to filter the second
3:36
argument is a condition or criteria that
3:38
determines which cells to include you
3:40
may need to enter the filter formula
3:42
multiple times depending on how many
3:44
products have identical prices this is
3:46
because the match function always
3:48
returns the position of the first
3:49
occurrence of a value causing the index
3:51
function to show the same product name
3:53
repeatedly in contrast the filter
3:55
function returns all occurrences of a
3:58
value making it more efficient for
3:59
handling identical
4:09
prices if the filter function is not
4:11
available in your version of excel you
4:13
can achieve the same result using a
4:15
combination of functions like index
4:17
small and if as shown on the screen
4:20
first delete all duplicate product names
4:22
as you did with a filter function then
4:24
drag the formula down to fill the
4:26
remaining cells this formula mimics the
4:28
filter function by extracting and
4:30
displaying only the rows that meet your
4:32
specified condition however unlike
4:34
filter this formula returns a single
4:37
value rather than a range so you will
4:38
need to use the drag handle to fill the
4:40
cells below it when searching for the
4:42
next group of identical prices don't
4:44
forget to update the references to the
4:46
correct price
4:48
cell you'll need to write the same
4:50
formula for each group of identical
4:51
prices just changing the cell reference
4:53
that starts with f for example F7 also
4:57
don't change the last row function it
4:59
should always always be a one for the
5:00
first product name of an identical value
5:02
a two for the second product name and so
5:10
on next clear the conditional formatting
5:13
as we no longer need
5:14
it finally we need to assign ranks based
5:17
on these prices we'll start by entering
5:20
the number one in cell G4 as this
5:22
represents the highest rank in cell G5
5:26
we'll use the formula as shown on the
5:27
screen to adjust the rank based on
5:29
whether the price changes or Remains the
5:31
Same this formula checks if the price in
5:34
F5 matches the price in F4 if it does it
5:38
keeps the same rank as the previous row
5:41
if not it increments the rank by one
5:43
when you drag this formula down it will
5:46
correctly assign ranks ensuring that
5:48
tide prices receive the same Rank and
5:51
the next unique price gets the next rank
5:53
number be careful of the last row that
5:55
ranks 10th there may be additional rows
5:58
with the same price sharing the same
6:00
rank to check for this use the drag
6:03
handle on the last price value and drag
6:05
it down if it reveals another price
6:07
identical to the previous value you'll
6:10
need to generate the rank for it simply
6:12
drag the fill handle in the rank column
6:14
to update the rank and also retrieve the
6:16
corresponding product name now you have
6:19
the final list of the top 10 highest
6:21
pric
6:23
products alternatively there's a very
6:26
easy way to get tide rankings compared
6:28
to the previous m method instead of
6:30
using multiple functions you can simply
6:33
sort the data by price in descending
6:35
order to do this select a price value go
6:38
to the Home tab and choose sort largest
6:40
to smallest from the sord and filter
6:42
drop down next enter one in celc 2 for
6:45
the highest rank then in Cel C3 use the
6:49
formula shown on the screen this formula
6:52
checks if the price in B3 matches the
6:55
price in B2 if it does it keeps the same
6:58
rank as the previous r
7:00
if not it increments the rank by one
7:02
drag this formula down to apply it
7:04
across the entire list this approach is
7:07
much simpler requiring only a single
7:10
formula and it still yields the same
7:12
result for tide rankings thank you for
7:15
watching I hope this tutorial helps you
7:17
manage tide rankings in your Excel
7:19
projects don't forget to like And
7:21
subscribe for more Excel tips and tricks
7:23
if you have any questions or suggestions
7:25
for future tutorials feel free to leave
7:27
them in the comments below see you in
7:29
the next video