MeadInKent
| Offset and dynamic charts | Searching for values using VLOOKUP() | Index function |
Excel 2010

Excel Functions: Offset and Choose

OFFSET() is a versatile function with many uses. It enables you to create flexible worksheets in which users can view various results based on different ranges of data depending on an input variable - for example by choosing a different month number.

OFFSET() returns a value from a cell which is at a specified number of rows and columns away from a particular cell reference. It is in the format: =OFFSET(BaseRef, Rows, Cols, RowRange, ColRange) . BaseRef is a cell address from which the number of Rows and Columns are counted (a relative reference) and can be located anywhere on your worksheet (although the top left hand corner of a table is conventional). RowRange and ColRange are optional and can specify a height and width of a range of cells (e.g. 4 rows high by 1 column wide would represent all of the values for one of the months in the table below).

CHOOSE() allows you to retrieve a particular value from a list of options. =CHOOSE(Value, Option1, Option2 ....) The value is a number which specifies the position of an item from a list of options (e.g. a value of 3 returns the 3rd option which may be either text, another number, or a cell reference). An individual option can be a cell reference but you cannot specify a range of cells to represent the list of options within the function itself.

- C D E F G H
3 Out Patient visits   Month number 2  
4            
5 Doctor Month 1 Month 2 Month 3 Selection Cumulative
6 Dr Smith 63 61 62 61 124
7 Dr Lam 41 42 48 42 83
8 Mrs Dapper 3 43 40 43 46
9 Prof. Plum 135 123 115 123 258
10            
11 Selection for month of: May   www.MeadInKent.co.uk

formulas used in example
[G3] = 2 2 the user can type a value of 1, 2 or 3 in this cell
[G6:G9]=OFFSET($C6,0,$G$3) 61 get the value in the cell 0 rows and 2 columns away from C6
[H6:H9]=SUM(OFFSET($C6,0,1,1,$G$3)) 124 add up cells in the range starting 0 rows and 1 column away from C6. The range is 1 row deep and 2 across
[E11]=CHOOSE(G3,"Apr","May","Jun") May Select the 2nd item in the list of text month names

The OFFSET and CHOOSE functions in this example both make use of the value typed into cell G3 (currently 2). The formula in the 'Selection' column is straight forward and just reads a single value each time. The 'Cumulative' column (H) is more complex. The OFFSET function returns a range of cells one row tall and 2 columns wide (D6:E6) which alone means nothing and would generate an error. However, when it is supplied as the range argument to a function such as SUM() it becomes very useful.

In some situations INDEX() may be a more efficient function than OFFSET().

Excel help Get this information as a document
accompanied by Excel worksheets
Document is in PDF format Click here for details about obtaining this
file. It has been rewritten for Excel 2010.

file: xloffsetchoose.htm Last updated Feb15 MeadInKent.co.uk 2016CMIDX S6 P5 Y