April 5th, 2011

Tips, Tools and Tutorials

Categories

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 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.

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.

– this will allow you to reference the row # of the cell for each name.

*=ROW(A2)*

– (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)*

– 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)*

– 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)*

– 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)

– 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)

– 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)*

– 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).

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.

There are no comments yet.