RANK | CSE 148 – Excel Exam 1

RANK explained

Imagine that we're the announcer at a high school track meet, and the runners are currently competing in the 1500m race.

Our job is to take their times, and then rank them from lowest to highest.

To do this, we can use the RANK function on each runner's time!

The RANK function enables us to easily see where a value ranks within a list.

Coding a RANK

Given the above track meet example, let's say that each runner scores the following times:

Let's start by ranking the first runner. To do so, we'll use the following template:

=RANK(value, range, [order])

In our case, the first value will be cell B2, since that's where Michael's time is stored.

=RANK(B2, range, [order])

Next, for the range. This represents the cells contain all the values to be ranked. In this case, that's cells B2:B6.

=RANK(B2, B2:B6, [order])

We can leave [order] blank for now because...

Arguments in [ ] are not required.

When we plug this RANK function into our spreadsheet, we get the following result:

Somethings wrong here... why did Michael get ranked 4th but had the 2nd best time?

The reason is because our RANK function (by default) ranks in descending order (meaning the highest values get the top rankings). Since we're dealing with a race, we want the lowest values to get the top rankings, so we must specify we're ranking in ascending order.

To do so, we need to place a "1" in the [order] argument of our RANK function.

=RANK(B2, B2:B6, 1)

The [order] argument enables us to rank in descending order (value of 0, is default) or ascending order (value of 1).

Notice now that Michael is properly ranked in 2nd place!

Now, what about the rest of the runners? To get their ranks, we must first specify an absolute cell reference for our range.

=RANK(B2, $B$2:$B$6, 1)

After doing so, all we need to do is copy the formula for Michael's ranking down to all the runners like so!

Practice Problem

Given the following results of a hotdog eating content, determine where Peter ranked!

=RANK(B4, B2:B7)

Note: We didn't place anything for the [order] argument because the default for [order] is descending order, which is what we're going for. If we wanted ascending order, we'd place "1" here.

Next, let's learn how to use the COUNT function!

Excel Cram Kit (Exam 1)

Want to unlock content? Get your Excel Cram Kit (Exam 1) now!

ApplyPRACTICE EXCEL EXAM 1 (PREVIEW ONLY)
ToolsExcel Nerd Notes (Exam 1) (PREVIEW ONLY)
LessonYEAR, MONTH, DAY
LessonSUM & AutoSUM
LessonRelative vs. Absolute Cell References
LessonRANK
LessonCOUNT
LessonROUND
LessonWEEKDAY vs. WORKDAY (PREVIEW ONLY)
LessonVLOOKUP (PREVIEW ONLY)
LessonPMT (PREVIEW ONLY)
LessonIF (PREVIEW ONLY)
LessonPercent change (PREVIEW ONLY)
LessonExcel charts (PREVIEW ONLY)
LessonImporting text files (PREVIEW ONLY)

Excel Reviews (Exam 1)

"I bought this Cram Kit the night before my exam, and it helped me tremendously. It provides step-by-step explanations for all the units covered in class."

Matt Lamanna
Oct. 14, 2021

Rating: 5 out of 5.

Leave a Comment