• Formulas
  • Excel Tips & Tricks
  • Excel Charting
  • Tutorials
  • VBA
  • Book Store
  • More
    • Blog
    • Download Area
    • Excel Video Tutorials
Home » Formula Friday – Excel Rank Function – Who is The Leader Of The Pack?

Formula Friday – Excel Rank Function – Who is The Leader Of The Pack?

June 3, 2016 by Barbara

HAPPY FRIDAY EXCELLERS…. let’s look at Ranking today.

The RANK function will compare numbers to other numbers contained in an Excel list. So if you apply RANK to a list of numbers, Excel will apply a ranking number to them either in an ascending order or a descending order.

There a a few parts or arguments to the function.

=Rank( number, array, [order] )

number – this is the number to find the rank for

array – this is the range or array of number to use for ranking

order– this is an optional argument and it specifies how to rank your numbers

If you set the order to 0 the numbers will be ranked in descending order

If the order is not 0 then the numbers are ranked in ascending order

If the order argument is not supplied then if defaults to 0 (descending order)

So let’s take a look at an example. I recently had to generate a league table for sales representatives, the league is ongoing based on their value sales which will run throughout the summer months. This seemed to be the ideal scenario to use RANK.

Here is my data, with May 2016 already filled in as those sales figures have been confirmed.

 

EXCEL RANK Pin it! Share on Facebook

 

My league table consisted of 10 sales people in the department. They are to be ranked based on their sales volumes, from 1 to 10. 1 being the top ranking and 10 the bottom. They are to be ranked over a 3 month period, May June and July.

I have added a column into the league which gives a total of all three months which I will base my ranking on, this way I only need to use the Total Sales column to use for ranking values. So as we have the first months data let’s see how the Sales people are doing in their ranking up to now. We can start by entering the =RANK function.

EXCEL RANK2 Pin it! Share on Facebook

 

We want to start with the first number we want to rank which is in cell reference E2- this is the number argument of the function

We then need to select the full data range of which to rank the number – $E$2:$E$11 – this is the ref argument of the function (this range of cells I make absolute as we will be dragging the formula down our list of values.)

I want the ranking to be in descending order, so I have set the [order]argument of the formula to 0. ( The highest sales will be ranked 1 and the lowest 10).

EXCEL RANK3 Pin it! Share on Facebook

 

Once entered I can drag the formula to the full list cells. The Sales people are now ranked for May 2016 and Scott Chalmer is in the lead. Well Done Scott!!!.

 

EXCEL RANK4 Pin it! Share on Facebook

Once I enter the sales figures for June and then July the formula will automatically update the the ranking of the Sales people.

If you want to download the sample workbook to work through the example you can do so here.

Icon

Formula Friday - Excel Ranking Function

 file(s)  28.94 KB
Download

More Excel Tips_New1

 

Dont forget to SUBSCRIBE to the How To Excel At Excel Newsletter for more tips the first Wednesday of the month.

th

  • Create A Micro Chart In Excel
  • How To Display a Plus + Sign On Positive Numbers With Custom Formatting In Excel
  • Create A Timestamp In Excel With Formulas
  • Use TRIM for a speedy data clean up

 

MR Xl 40 formulas

excel-formulas-ad-5

Filed Under: Blog, Formula Friday, Formulas Tagged With: Blog, Excel Formulas, Excel Tip Category, formula friday

Tags

Blog Cells Excel Excel Charting Excel Excel Tips Excel Formulas excel tip Excel Tip Category Excel Tips Excel Tips& Tricks excel tutorials Excel Videos Excel Video Tutorials formatting Formula formula friday Formulas macro Macro Mondays Macros Pivot Table Pivot Tables Power BI Power Query Tutorials vb VBA Worksheets

Recommended Excel Resources

Free Excel Dashboard Webinar

Copyright © 2021 · Enterprise Pro Theme on Genesis Framework · WordPress · Log in

Search:

Popular Pages

  • Home

More Info

Want FREE Excel Tips?

Click on my FREE eBook, its my bonus for joining thousands of others who receive my 3 xFREE Excel tips every month in my Excel Newsletter. Join Us!.

Need Answers To Excel Questions Like These?

*How Do I Create A Timestamp In Excel?
*I Want To Change The Width Of The bars On My Excel Chart
*How Can I Find Out The Length Of My Text in Excel? Click the link below to receive more Excel tips' and my Free Ebook

New To Excel? This Is A Great Place To Start!!

Learn Excel Dashboard Course Excel Basics
Scrollbars
Colour of Sheet Tabs
Conditional Formatting
Calculations Introduction

Excel Charting

Self Expanding Charts
One Click Charts
Create Quick Dynamic Charts
Easy Combination Charts

Video Tutorials-If You Like To Learn By Watching

Subscribe to YouTube to get notified of of new Excel videos.


Self Expanding Chart
Delete Obsolete Items From A Pivot Table-Excel 2007
What Are Pivot Tables- Create Your First One
Enable The DeveloperTab In Excel

Some Fun Stuff

Pivot Table Song

© howtoexcelatexcel.com. All rights reserved.
  • Privacy Policy