|| Feedback | OffSet() | Indirect() | SumProduct() | Large spreadsheets |
INDEX(Range, RowPosition, ColumnPosition) lets you refer to the contents of a particular cell at the intersection of a row and column within a range or table. In the 4 x 4 range (to the right), the formula =INDEX(A1:D4,2,3) would refer to the cell marked 'X'.
It can also be used to return an entire row or column within the range. Either the RowPosition or ColumnPosition arguement can be omitted. For example =INDEX(A1:D4,,3) has no row value and would return the whole of column 3 (i.e. C1:C4).
This is similar to the OFFSET function, but the arguements can be simpler and it is more efficient in large spreadsheets.
In a spreadsheet which gathers data for processing and reporting, the users may choose to add rows to the end of a table. This can corrupt formulas which are attempting to read the whole range and summarise all of the data. For example, a formula =SUM(C5:E14) would start to miss values if a user inserted more rows starting at row 15 or before row 5.
In this example, the problem of additional rows can be avoided by creating a dynamic named range. Instead of simply creating a new range highlighting the area A6:E12, formulas are used to ensure that the range expands to include additional records.
Any rows between the (green) title row (4) and the section break (15) should be captured within the range defined in cell E3.
A new Name is added using the ribbon option Formulas | Defined Names | Name Manager | New... The name ('BSTable1') uses the reference in cell E3 to specify the size of the range.
The formula =INDIRECT(E3) will pass the text contents of cell E3 as a value to the range definition. [See more about Indirect()]
A named range has now been created which will refer to any number of expenditure records for all of the locations. Formulas to analyse this data can be based on the name rather than a specific range of cell addresses.
This second example is very similar except that the height of the named range is dependent on the current month number (in H17) rather than a count of values.
A second named range called StfTable2 is based on =INDIRECT(User_Input!$E$18)
Calculations can be performed on the two new Named ranges using Index(). Both of the formulas use Index() with the defined names providing the ranges and whole columns rather than cell intersections.
SUMPRODUCT() can be used to sum the values which meet a specified condition.
|Having already created a named range called StfTable2, further named ranges can be created based on particular columns within that range.
Because these three names are based on columns within a dynamic range, these named ranges will also grow along with the number of months. This can greatly improve the appearance of charts.
The first chart is based on data in cells A20:E25 and therefore includes zero values for July to September. This is unhelpful and misleading. The second chart is based on the three 'active' months and therefore omits the zero values.
|A fixed range of source data
|A dynamic range of source data
Within the second chart properties, each of the three series of values is based on one of the names.
The process of creating charts based on dynamic ranges (using OFFSET) is covered elsewhere in this website.
|This page is NOT included in
the Excel Functions guide
|Click here for details about
obtaining this file
|© meadinkent.co.uk 2015
|Page updated Jan15
|CMIDX S6 P6 N