Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells

Tom’s Tutorials For Excel: Summing and Averaging Dynamic Lists, Including or Excluding Blank Cells
The below picture shows a side-by-side comparison of summing and averaging the last 5 cells in a dynamic list, depending on if blank cells should or should not be included in the formula results. Today the list only reaches to row 13; next week the list might comprise one hundred rows or longer.
In Image 1, the 5 bottom-most cells are identified in the formulas’ calculations, including cell A10 which is blank. Cell B2 in Image 1 displays an average of $585.75 returned from this formula:
=AVERAGE(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))
Cell C2 of Image 1 displays a sum of 2343 returned from this formula:
=SUM(OFFSET(A2,COUNTA(A1:A100)-5,0,5,1))
In Image 2, the 5 bottom-most occupied cells are identified in the formulas’ calculations, excluding cell A10 which is blank. Array formulas are utilized in this case, which are committed to the cell by pressing Ctrl+Shift+Enter, not just with Enter. Cell B2 displays an average of 538, returned from this array formula:
=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))/5
Cell C2 of Image 2 displays a sum of 2690 returned from this array formula:
=SUM(A100:INDEX(A:A,LARGE(IF(A1:A100<>"",ROW(A1:A100)),5)))