# 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)))` Posted in Tom's Tutorials for Excel
Tags: