where “first” is a reference to the first cell to include in the average and “last” is a reference to the last cell to include. The result is a range that includes the N cells to average. To get the first cell in the range, we use INDEX like this: The array is the entire Sales column, and row number worked by subtracting (n-1) from total rows. In the example, F4 contains 3, so the row number is 10-(3-1) = 8. With a row number of 8, INDEX returns C12. To get the last cell we use INDEX again like this: There are 10 rows in the table, so INDEX returns C14. The AVERAGE function then returns the average of C12:C14, which is $78.33.
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.
