Excel Tip: Using CHOOSE – or do you need OFFSET, or maybe INDEX?

 

ChooseFrom the Not Just Numbers blog:

How are those New Year Resolutions coming along now we’re all back to work?

Did you set your Excel ones? I’d still love to hear what they are.

Just a couple of quick messages  before we get into today’s post.

First of all, if you’re not a subscriber to the blog and therefore didn’t get my exclusive Christmas video showing you how to use drop-down lists, I have decided to give you one more chance. I will send it out again at the weekend to any new subscribers that have signed up by the end of this week. So get yourself subscribed now in the box at the top right of the blog – if only to laugh at me in a Santa hat!

Secondly, I have made a few changes to the colour scheme of the blog – to freshen it up for the New Year. I would really appreciate any feedback on the changes, both positive and negative – as well as any other suggestions for changes to the layout of the blog.

Well, on with the post…

Tom, an accountant in Australia and regular Not Just Numbers reader, asked the following when I asked to hear what you wanted to learn from the blog:

“I’d like to learn about the CHOOSE function and using this for selecting data from another area of a worksheet (I am not sure if this would be used in conjunction with OFFSET?)”

Well Tom, the CHOOSE and OFFSET functions both perform a similar function – but in different circumstances.

I have covered the OFFSET function before, so I thought I would introduce the CHOOSE function and then explain how to determine which one you need. And then show you why INDEX might be better anyway!

The CHOOSE Function

The CHOOSE function is pretty simple and is used to select from a fixed list as follows:

=CHOOSE(position in list,item1,item 2, etc.)

so =CHOOSE(3, “A”,”B”,”C”,”D”) returns C, being the third item in the list.

The list can contain up to 254 items.

You could use cell references as the list items, e.g.

=CHOOSE(3,A1,B1,C1,D1) will return the contents of cell C1.

Or you could even use ranges as long as you told Excel what you wanted to do with the range, e.g.

=SUM(CHOOSE(3,A1:A3,B1:B3,C1:C3,D1:D3)) returns the sum of cells C1 to C3.

The OFFSET function

I will not go into the full workings of the OFFSET function here as you can read about itin my earlier post.

You could though use the OFFSET function for either of the last two options:

=OFFSET(A1,0,2) returns the contents of C1

(notice an offset of 2 columns returns the third item here as the first item (A1) would be an offset of zero columns)

=SUM(OFFSET(A1,0,2,3)) will return the sum of the range C1 to C3, being offset by zero rows and two columns and having a height of 3.

OFFSET can be far more flexible and and easy to use, particularly as the list gets longer., however will not work if the list contains ranges of differing sizes (in which case you would need to use CHOOSE).

In most cases when the items on the list are held in the spreadsheet, OFFSET will be the better choice – except where you need to select from different size ranges as mentioned above.

The INDEX function

The INDEX function is an alternative to OFFSET which can be a little more complicated to understand, but  makes a better use of resources – which is particularly important if your spreadsheet is getting large and cumbersome.

INDEX has two forms, but for this purpose, we only need to worry about the more common one:

=INDEX(Array,Row Number,Column Number (optional))

The function then returns the value from the array at the intersection of row and column. If the array is only one row high or one column wide then you only need include either the row or column number. Also, a row or column number of zero will return the whole row or column as a range.

So…

=INDEX(A1:C1,3) will return the contents of C1

and…

=SUM(INDEX(A1:C3,0,3)) will return the sum of cells C1 to C3

I hope that helps you Tom, as well as everyone else!

If you enjoyed this post, go to the top of the blog, where you can subscribe for regular updates and get your free report “The 5 Excel features that you NEED to know”.

Comments

Using Offset with the SUM function

brian.barrett | | Permalink

I often use the offset function with the SUM function in situations where you there is the possibility of a row being inserted between the Total cell, and the last row you wish to include in the total.

As an example, if cell A5 had the formula, SUM(A1:A4), then if a row was inserted above row 5, then the formula would move to cell A6, but the formula would remain as SUM(A1:A4),  thereby not including any figures that were entered in A5.

There are various ways around this, but 1 rather neat solution is to have in cell A5 the formula, SUM(A1:OFFSET(A5,-1,0)).  Should a row be inserted above row 5, then the formula would move down to cell A6, but the formula would change to SUM(A1:OFFSET(A6,-1,0)), thereby retaining a sum of the whole expected range.

If row1 was a header row and row 5 a total row, then if you were worried about row insertions at the top of the range 2:4, then you could even use the formula, in cell A5, SUM(OFFSET(A1,1,0):OFFSET(A5,-1,0)) !

One must note, however, that using too many OFFSETs can slow down your worksheet.  This is because the function is recalculated everytime something changes on the spreadsheet.  Most functions are only recalculated when a cell that affects the function, either directly or indirectly, is changed.

Add comment
Log in or register to post comments
This blog

Popular posts from Glen Feechan's Not Just Numbers blog - The blog for those who know it's not just about the numbers. Typical content included is primarily Excel tips and other comment relevant to those responsible for finance in their business. Glen develops spreadsheets for clients all over the world via needaspreadsheet.com and helps accountancy practices to make better use of Excel through his Excellent Accountancy business.

Get The 5 Excel features that you need to know free, by subscribing to Not Just Numbers here.