Scribd Logo
Upload

Welcome to Scribd!

  • 110 views

    The Best RANK Function Use in Excel

    Uploaded by

    Josh Sabiman

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd

    The Best RANK Function Use in Excel

    Uploaded by

    Josh Sabiman
    110 views9 pages

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    110 views9 pages

    The Best RANK Function Use in Excel

    Uploaded by

    Josh Sabiman

    Copyright:

    © All Rights Reserved
    Download as PDF, TXT or read online from Scribd
    Download as pdf or txt
    of 9

    Call us today (858) 427-0825 Get Started Today

    Services

    Making the Best of the Rank


    Search Engine Optimization

    Function in Excel
    Social Media Marketing
    Paid Advertising
    Website Design And Development

    Case Studies
    About
    Team

    Blog
    Apr 5 UsBy Amanda Thomas
    Contact 1727 2

    Category: Tips, Tools and Tutorials


    Excel is a helpful resource for storing, sorting
    and managing data. However, generating
    reports and using the Sort button still
    requires you to manually sort your
    information (unless you want to delve into
    Macros). When all you need is a brief look at
    a few of the highest or lowest figures in your
    spreadsheet data, without having to re-sort
    your data each time, you might want to
    consider adding a RANK function in your
    spreadsheet.

    Whether you want to see which keywords have been generating the most amount of
    conversions, or which Internet marketing services are showing the most ROI, you can use
    the RANK function along with your Analytics data. Here is a way to set up your
    spreadsheet to show your highest values using a formula without having to touch the
    sort button or create a single macro.
    The Function of the RANK function
    The most basic use of the RANK function is to return the rank of a number within a set of
    numbers. This can be incredibly useful when applied to budgets and determining where
    your highest expenses or, conversely, highest revenue is coming from. The syntax for
    using RANK is:

    =RANK(number,ref,[order])

    Number – the cell you are ranking (e.g. B2).

    Ref – the data set that you are comparing the ranked cell to (e.g. $B$2:$B$21) – don’t
    forget to keep those $ in there to denote an absolute reference.

    [Order] – the order of rank (1st = 1, 2nd = 2, etc.) – you will have to update this number
    when you copy the formula to make a list of rankings.

    The RANK function can be a bit tricky to customize for various applications, but once
    you’ve got it down, you can use your RANK-enhanced formulas to replace your need for
    the sort button. Here is one way to combine several functions to achieve the Best Rank
    (pun intended) formula on your spreadsheet. This is going to be an extensive use of
    functions so be prepared to use the RANK, INDEX, MATCH, SMALL, LARGE, COUNTIF, IF,
    ROW, IFERROR and SUM functions.

    Step 1: Organize Your Data


    In this example, I’ve created a list of 20 “categories,” which are listed below. Next to each
    are the corresponding values, (which I have denoted as their revenue). Next I will be
    creating more columns on the right of this data set called: ROW, RANK, TIE BREAK, BEST
    RANK, LARGEST VALUEST, SMALLEST VALUES, HIGHEST and LOWEST. All of these
    columns can be hidden for aesthetics at a later time.

    Step 2: Create your formulas


    ROW

    – this will allow you to reference the


    row # of the cell for each name.

    =ROW(A2)

    RANK
    – (a nested formula using an conditional formula –[IF]– to prevent the calculation of
    blank cells which will produce an error along with the RANK function to find the largest
    values in our revenue column).

    =IF(B2>0,RANK(B2,$B$2:$B$21,1),0)

    TIE BREAK

    – sometimes, you run into a tie – or in this case, the same values; this formula helps to
    break any ties by using the COUNTIF function to find any duplicate values in addition to
    the RANK function. The RANK function is utilized to give priority to the ties that have the
    lower ROW numbers/higher in your list.

    =IF(COUNTIF($B$2:$B$21,B2)>1,RANK(D2,$D$2:$D$21,1)/100,0)

    BEST RANK

    – this is a simple SUM formula (using an IFERROR formula to make sure we don’t get any
    errors in our ranking values) that adds the original RANK value to our TIE BREAK value
    which will give us a new list of RANK without ties.

    =IFERROR(SUM(E2+F2),0)

    LARGEST VALUES

    – the LARGE function is utilized to determine what the largest value is associated with
    having the highest BEST RANK. It may seem repetitive, but this helps provide back up of
    any errors or ties.

    =(MATCH(LARGE($G$2:$G$21,1),$G$2:$G$21,0

    (the number 1 – in red – will need to be changed accordingly to 2, 3, 4, etc. down the
    column)

    SMALLEST VALUES

    – the obverse of the LARGEST VALUES, this formula uses the SMALL function to
    determine what the smallest values are associated with having the highest BEST RANK.

    =(MATCH(SMALL($G$2:$G$21,1),$G$2:$G$21,0))

    (the number 1 – in red – will need to be changed accordingly to 2, 3, 4, etc. down the
    column)

    HIGHEST

    – using the INDEX and MATCH functions, you can find out what name is associated with
    having the largest BEST RANK values.

    =INDEX($A$2:$B$21,H2,1)

    LOWEST

    – the opposite of the HIGHEST formula, in that it helps you use INDEX and MATCH to find
    the smallest BEST RANK values.

    =INDEX($A$2:$B$21,I2,1)

    You can even merge some of your formulas in some of the columns, like changing the
    HIGHEST formula to:

    =INDEX($A$2:$B$21,(MATCH(LARGE($G$2:$G$21,1),$G$2:$G$21,0)),1)

    (this combines the LARGEST VALUES formula [in red] with the existing HIGHEST formula
    [in blue]…just don’t forget to change the value of the first 1 as you copy the formula down
    the column).

    Step 3: Hide the columns


    you don’t want to view
    Once you’ve finished with these
    formulas, you can hide the columns
    you don’t wish to see and just keep
    your HIGHEST and LOWEST columns.
    Just select your Columns and right
    click and select Hide. Or, if you plan on creating a dashboard for your data, you can make
    references to the HIGHEST and LOWEST values so you can have that data available
    elsewhere, without having to move your spiffy new RANK formulas.

    And, if you don’t have the time to go through this whole process by hand, and could use a
    little help, feel free to download the spreadsheet (click here). If you need to expand the
    list of names and revenue, make sure to Unhide (select column C through K, right click
    and select Unhide) the columns and copy the formulas as far down as you need.
    And, as an added bonus, in the ListWithoutBlanks tab, you can use similar formulas to
    generate a “shortened” list that will automatically omit any blanks.

    Bio Twitter Google+ LinkedIn Latest Posts

    Amanda Thomas
    SEO Manager

    Amanda is the SEO Manager for BRIM and is responsible for assisting the
    project managers with the daily tasking to the SEO team.

    « Know Your Site Before You SEO


    In Other News…Internet Marketing Recap – 4/4/2011 to 4/8/2011 »

    Comments

    Pingback: COUNTIF formula misses out certain values

    Pingback: Functions similar to SMALL or overcome limitations of


    SMALL

    Leave a Reply

    Your email address will not be published. Required fields are marked *

    Name *

    Email *

    Website
    Comment

    Post Comment

    Search Blog Posts

    Search

    Drop Us A Note

    Name

    Email

    Phone

    Website URL

    GET STARTED TODAY >>

    Categories

    Company News & Events


    Content Marketing
    Conversion Rate Optimization
    General Online Marketing
    Graphic Design
    Industry News
    Lead Generation
    Marketing
    Other
    Pay Per Click
    Podcasts
    Search Engine Optimization
    Social Media
    Tips, Tools and Tutorials
    Videos
    Web Design & Development

    Tweets Follow

    BRIM Agency @BRIMagency 14 Jun


    Don't make the same mistake again!

    bit.ly/1Hs8JC9
    pic.twitter.com/4B5c2uyf5L

    Expand

    Tweet to @BRIMagency

    Services

    SEO
    Paid Advertising
    Website Development
    Social Media Marketing

    Company

    About
    Team
    Contact Us
    Blog
    Sitemap

    Address

     10590 W Ocean Air Dr.


    Suite #220
    San Diego, CA 92130

     (858) 427-0825
    [email protected]

        

    © BRIM Agency, Inc. 2014 All rights reserved Privacy Policy | Terms and Conditions

    You might also like