| | Offset and dynamic charts | Searching for values using VLOOKUP() | Index function | | ||
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 | |||
| [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().
 |
Get this information as a document accompanied by Excel worksheets |
 |
Click here for details about
obtaining this file. It has been rewritten for Excel 2010. |
| file: xloffsetchoose.htm | Last updated Feb15 | © MeadInKent.co.uk 2016 | CMIDX S6 P5 Y |