Traditionally I have used the OFFSET function for dynamic ranges within Excel. It is solid and gets the job done on a given range. One thing to remember when using OFFSET is to ensure you include a big enough row count for vertical ranges. It works really nicely on a tabular data sets. If you are unfamiliar the following is an example of a dynamic range with a AVERAGE formula:
=AVERAGE(OFFSET(B2,,,COUNTA(B2:B90)))
However, there is another method for trapping dynamic ranges, let’s say we want the AVERAGE for the cells in column B and the likelihood that column B will grow is extreme. The same formula can be written as follows:
=AVERAGE(B2:INDEX(B2:B90,COUNTA(B2:B90)))
While the formula is longer (by 4 characters) it has the added advantage of being a non volatile formula. If there are too many Volatile formula in a spreadsheet it can start to chug a bit.
Breaking the formula down cell B2 is where the formula range starts, the INDEX formula comes to the party where it creates the second cell reference in Column B. It looks at the range between B2 and B90 and is looking for a specific Row number in the first instance. Enter the COUNTA formula which acts as the solution for which Row number. Counta counts all of the cells that have data in a given range. Blanks are not included. So if the data stops in A15 then all of the data from B2:B15 are included in the AVERAGE. It is a master stroke and keeps things non volatile.
Try it with your own data. Here is a workbook that displays both techniques.
