## Tuesday, 5 July 2016

### Excel Tip: Ranking numbers in Excel

Before I start, a couple of announcements.

First of all, please accept my apologies for the time between posts. A combination of workload and a family bereavement have slowed me down somewhat!

Secondly, if any of you will be in and around London tomorrow (Wednesday) night, the ICAEW are launching their Spreadsheet Competency Framework, with some top speakers from the world of Excel. This document (as suggested by its name) is intended to provide a framework to assess spreadsheet competency, and was developed by the Institute's Excel Community Advisory Committee, of which I am a member. The event is free to attend, and further details can be found at:

http://www.icaew.com/en/technical/information-technology/it-faculty/it-faculty-events

I hope to see you there.

Right...on with the post.

There are many reasons, you might want to rank a list of numbers in Excel. One I do quite often, is produce a league table. By calculating the ranking of a number and then using lookups to populate a table in rank order, we can easily produce a league table, like the example below:

The first Rank column is the one we are trying to populate here.

In Excel 2010 onwards we will use the RANK.EQ function, which replaces the RANK function in Excel 2007 and before. Both functions work exactly the same, and both exist in Excel 2010 to 2016, at least. If you know you may have users using Excel 2007 or earlier, use the RANK function - otherwise use RANK.EQ to future-proof your spreadsheet.

The RANK.EQ (or indeed the RANK) function's syntax is as follows:

=RANK.EQ(number,ref,[order])

where:

number is the number we want to know the rank of
ref is the whole range of numbers
order is an optional argument, which determines the order that the numbers should be ranked in. If this argument is zero, or omitted, the numbers are ranked in descending order (the largest number is ranked 1), whereas if this is 1 (or any non-zero value), the numbers are ranked in ascending order.

Where two numbers in the range are the same, they are both given the highest rank (when using the RANK.EQ function - there is a similar RANK.AVG function that gives them their average rank).

In our example the numbers 30,45,97, etc. are in the range B3:B12, so we would use the RANK.EQ function in cells C3:C12. In cell C3, we would enter:

=RANK.EQ(\$B3,\$B\$3:\$B\$12)

Notice that we have fixed all of the references (using the dollar signs) on the B3:B12 range, to ensure that this range stays fixed when we copy it down, but just the column on the B3 reference so that the row (and the number we rank) changes as we copy down.

If we use this function as it is, we will run into a problem, though. As there are two number 24s in our range, we get the following:

Notice that both 24s are ranked 8 (the highest rank), which means there is no rank 9 - hence our error in the league table when we try to look up 9. We need each rank to be unique for our league table to work.

We will need to use another formula to address this, and we can use COUNTIFS to count how many instances have occurred so far in the list.and if this is greater than 1, to add the difference to the rank. Our formula becomes:

=RANK.EQ(\$B3,\$B\$3:\$B\$12)+COUNTIFS(\$B\$3:\$B3,\$B3)-1

Notice that in the criteria range B3:B3, I have fixed the row on the start of the range and left it flexible on the end, so when our formula is copied down, we are always counting the instances from the top of the column to the current row. By the end of the range, our formula is:

=RANK.EQ(\$B12,\$B\$3:\$B\$12)+COUNTIFS(\$B\$3:\$B12,\$B12)-1

On the first 24, our RANK.EQ function returns 8 as before, and our COUNTIFS counts that there is 1 24 so far, so:

8+1-1 = 8

On the second 24, our RANK.EQ function again returns 8, but our COUNTIFS now counts that there are two 24s so far, so:

8+2-1 = 9

thereby giving us the result we required:

The league table was created by entering the numbers 1 to 10 in the first column and using INDEX and MATCH to return the number at that rank from the first table.

The INDEX/MATCH function on the first row (row 3) was:

=INDEX(\$B\$3:\$B\$12,MATCH(\$E3,\$C\$3:\$C\$12,0))

If you did not understand the COUNTIFS or INDEX/MATCH functions, please visit these earlier posts:

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

## Tuesday, 10 May 2016

### Excel Tip: Adding up columns based on multiple criteria (the SUMIFS function)

Before getting into today's post I want to point you to an excellent free Webinar being offered (for a limited time) by Mynda Treacy, entitled "Creating Excel Dashboards". Mynda is a real expert on Excel Dashboards and her training materials are always excellent. You can register for the webinar here.

I realised the other day that I had never covered one of my most used functions on this blog - SUMIFS. I have covered its predecessor, SUMIF, as SUMIFS has only been available since Excel 2007.

Although SUMIF is still available in later versions of Excel for compatibility purposes, it is essentially redundant, as SUMIFS does the same thing, plus a lot more.

Let us look at an example of some sales data (see left).

Say we want to know how much Mary's sales were, or how much Sarah sold in the East Region, or even how much Ben sold in the North region in the month of January.

SUMIFS can do all of these.

The syntax for SUMIFS is as follows:

=SUMIFS(SumRange,CriteriaRange1,Criteria1,[CriteriaRange2],[Criteria2].....)

You can have as many pairs of CriteriaRange and Criteria as you need. The function works as follows:

SUM SumRange where CriteriaRange1 = Criteria1 and CriteriaRange2 = Criteria2 etc. for however many criteria you have.

For all of the examples above the SumRange will be D2:D21, as this is the range we want to sum, subject to our criteria. We will look at how we construct the rest of the formula for each of our examples above.

How much did Mary sell?
Here we only have one criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Mary"

=SUMIFS(D2:D21,C2:C21,"Mary")

returns £16,853.

How much did Sarah sell in the East Region?
This time we have two criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Sarah"

CriteriaRange2 = B2:B21
Criteria2 = "East"

=SUMIFS(D2:D21,C2:C21,"Sarah",B2:B21,"East")

returns £1,085.

How much did Ben sell in the North Region in the month of January?
This time we actually have four criteria:

CriteriaRange1 = C2:C21
Criteria1 = "Ben"

CriteriaRange2 = B2:B21
Criteria2 = "North"

CriteriaRange3 = A2:A21
Criteria3 = ">="&DATE(2016,1,1)

CriteriaRange4 = A2:A21
Criteria4 = "<="&DATE(2016,1,31)

There are two elements to these last two criteria that need further explanation.

The first is that if our criteria is anything other than equals, we need to include the criteria in inverted commas, for example ">23", or "<=15", to make it a string. If rather than 23, we wished to refer to a cell (say G5) we can use the ampersand (&) to join two strings together, e.g. ">"&G5.

The second is that if we wish to refer to a date directly, we need to refer its sequential number which we can calculate using the DATE function. The three arguments for the DATE function are Year, Month and Day, so to get the date sequence number for 1st January 2016, we can use DATE(2016,1,1). Note that if we entered 1/1/2016 in cell G5, we could just use ">="&G5 for Criteria3, as the cell value when you enter a date, is its date sequence value.

Our function is therefore:

=SUMIFS(D2:D21,C2:C21,"Ben",B2:B21,"North",A2:A21,">="&DATE(2016,1,1),A2:A21,"<="&DATE(2016,1,31))

which returns £4,007.

In most real situations we are likely to have all of the criteria in other cells, as we are usually doing more than one calculation.

With careful planning and smart use of dollar signs, you can structure your formula so that you only need to write it once. For example if we wish to populate the following grid from our data:

If we put the following function in cell J2, we  can copy it to all of the other cells:

=SUMIFS(\$D\$2:\$D\$21,\$C\$2:\$C\$21,\$I2,\$B\$2:\$B\$21,J\$1)

We have used dollars to fix both the rows and columns of the references to the data table, as these should not change, no matter what cell that we are in.

For our criteria however, we want those to change between cells, so we have fixed the column of our name criteria (\$I2), as we are always going to look to column I for the name, but want it to change as we change rows. Similarly, we have fixed the row of our region criteria (J\$1), as we always want to look at row 1, but want it to change with the columns.

Hopefully this gives you an idea how flexible SUMIFS can be.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

## Tuesday, 5 April 2016

As you may be aware, I am a member of the Institute of Chartered Accountants in England and Wales (ICAEW) Excel Community Advisory Committee.

An important piece of work that the committee has been working on is what we are (currently) calling the Spreadsheet Capability Framework.

This document is intended to provide a reference point that spreadsheet users, employers and job applicants can use to discuss and assess competence in spreadsheet use - beyond glib phrases on a CV, such as "proficient with Excel".

Given the authorship of the framework, it is obviously primarily aimed at those in the finance field, so apologies to my non-accountant readers.

The framework has now reached the stage where we are actively seeking opinions outside the committee.

To this end my colleague, David Lyford-Smith has posted a copy of the document to the ICAEW's IT-Counts website, where your comments would be greatly appreciated.

Whereas comments on my blog are always welcome, and will be passed on, I would recommend commenting on the IT Counts post instead to contribute to the wider debate.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".

## Tuesday, 8 March 2016

### Excel Tip: Using Index to calculate a cumulative sum (e.g. a Year to Date total)

Let me start with an apology. My current workload has meant that my posts have been a lot less frequent in recent weeks. This may continue for a while, but I will still be aiming to post at least once a month during this busy spell.

In this post I want to show you how to do something I have shown before using OFFSET, but this time using the more efficient INDEX.

Let's look at the example of a 12 month budget spreadsheet with the monthly sales figures in cells B9 to M9 (month 1 in B9, Month 2 in C9, etc.). And let's say that the current month number is entered in cell B6.

Using OFFSET we could return the current month's sales using:

=OFFSET(\$B9,0,\$B\$6-1)

and the cumulative sales using:

=SUM(OFFSET(\$B9,0,0,1,\$B\$6))

See this earlier post, if you don't understand why.

The problem with OFFSET though, is that it is what is known as a "volatile" function. This means that it always has to be recalculated when any cell in the spreadsheet changes - as it does not specify a range that it is dependent on, therefore does not know whether a change might affect its result.

INDEX however looks at a defined range, making it significantly more efficient, and in its simplest form can be used to replace both of these examples of OFFSET.

For a range the width of a single cell (as in our example), INDEX only needs two arguments to return the current month's sales:

=INDEX(\$B9:\$M9,\$B\$6)

This returns the value of the cell in position B6 in the range B9:M9.

If \$B\$6 is 3, this will return the value in cell D9, being the third cell in the range B9:M9.

To do the cumulative calculation, we can use the same INDEX function to return the end of  a SUM range, while fixing the start:

=SUM(\$B9:INDEX(\$B9:\$M9,\$B\$6))

Again, if B6 contains 3, then this returns the sum of the range B9:D9.

In both cases, these will only recalculate if a cell in the range B9:M9 is edited. In a complex spreadsheet with many calculations, this can make a huge difference to calculation times.

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get two freebies "The 5 Excel features that you NEED to know" and "30 Chants for Better Charts".