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.
Hi – I hope you can help
I was looking at your solution to the problem as below – a great help
https://www.ashishmathur.com/average-a-range-of-numbers-with-blanks-appearing-at-random-intervals/
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?
Thanks
Ken
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.
Average a range of numbers with blanks appearing at random intervals
{ 4 Comments }