where “data” is the named range C5:G9. Inside the sum function, the first reference is simply the first cell in the range that covers all possible cells: To get the last cell, we use INDEX. Here, we give INDEX the named range “data”, which is the maximum possible range of values, and also the values from J5 (rows) and J6 (columns). INDEX doesn’t return a range, it only returns a single cell at that location, E9 in the example: The original formula is reduced to: which returns 300, the sum of all values in C5:E9. The formula in J8 is almost the same, but uses AVERAGE instead of SUM to calculate an average. When a user changes values in J5 or J6 the range is updated, and new results are returned.
Alternative with OFFSET
You can build a similar formulas with the OFFSET function, shown below: OFFSET is designed to return a range, so the formulas are perhaps simpler to understand. However, OFFSET is a volatile function, and can cause performance problems when used in larger, more complex worksheets.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
