Average a range of numbers with blanks appearing at random intervals


With blanks appearing in a range of numbers, one may want to:

1. Average the first n numbers; and/or
2. Sum the last n numbers (in a horizontal and vertical range)

Because of blanks, the range for averaging will need to automatically keep expanding till the nth number is reached.

You may refer to my solution in this workbook.

Leave a Comment

Your email address will not be published.


    • Hi,

      To get MAX, replace 2 with 4 in the first input of the SUBTOTAL() function of my formula shown in the worksheet named “Sum last n numbers”. To get MIN, use 5.

      Hope this helps.

  • I want to find the average of the last five values in a row. A new value is added every week. I’ve used OFFSET in the formula for the starting point in the row.

    • Hi,
      So what problem are you facing when you apply my formula. In the workbook that you download from the link shared in the Blog article, I have shown the formula.