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.

RSS 4 Comments…

 Share your views
  1. Hi – I hope you can help

    I was looking at your solution to the problem as below - a great help


    I am looking to do something similar with the MAX/MIN function – specify a range and the number of values to be returned

    Can you help?



  2. 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.

Leave a Comment

Your email address will not be published.