snap.png
forum_example.xlsx
I need help to calculate the simple average of specified number of consecutive non blank cells. Pls refer the screenshot or the excel file which will make it clear.
The problem: Say, I have values in column F starting for cell F6 downwards. In the example there are non blank cells as: F6, F7, F10, F12, F14, F15 thru F19.
Consider Row number 6 which has Short Avg (of 2 values), Medium Avg (of 3 values) and Long Avg (of 5 values).
Expected: The Short Avg in cell B6 = (F6 + F7)/2
The Medium (3 values) in cell C6 = (F6+F7+F10)/3 and
The Long (5 values) D6 = (F6 + F7 +F10 +F12 + F14)/5 and so forth.
The Problem: The rows are blank randomly so I do not know the span when my 5 values will be available for the Long Avg, say.
So there is no pattern as such. In the example the 14th row gets all the 5 values in just 5 rows F14, F15, F16, F17 anf F18.
Currently I use filter to manually mark the values and use these to manually calculate the average. Of course, the Small/Medium/Long spans are much higher in my real context than 2, 3, 5 as shown in the example above...
Any help will be highly appreciated.. Thanks in advance
Bookmarks