Dynamically average first 4 non blank cells in a column

Hi,

I have 8 columns of retailer data - in columns C-J
Data starts at Row 2 for 6 out of the 8 retailers but at row 25 for retailer 2 (column D) and row 5 for retailer 7 (Column I)
In Row 2 of columns P-W I would like to set a baseline for each retailer and show the average of the first 4 weeks of data, taking the starting point as their first row that isn't blank
For 6 columns it would be an average of rows 2-5, for column D it would be rows 25-28 and Column I it would be rows 5-8

There should only be up to 52 rows of data for each table but the number of columns could vary (the baseline will be on another sheet, but I have put it in P-W for examples sake).

I'd like it dynamic because although this is just a small table, it could be repeated very often with different columns starting at different points so manually adjusting it would take a lot of time and be fiddly / prone to error.

Apologies if this has been answered before - I have looked around the forum and can't see anything that fits, but I might be missing something.

Thanks in advance.

Not elegant and entered as an array with shift control enter
=AVERAGE(OFFSET(C$2,0,0,SMALL(IF(C$2:C$52<>"",ROW($C$1:$C$51)),4),1))

you find the row of the 4th non blank value and then find the average of this all all the preceding rows (blanks are not calculated as part of the average function

Well, you may think it is inelegant, but it works so it's great in my book. Thanks very much!

Cell P2 formula , Drag right

Formula:

Please Login or Register  to view this content.