Excel: Flashfill Offset Horizontal + Vertical

Question

So I'm not a fan of VBA and I recently learned that OFFSET can be used with COUNTA to flashfill a range as far at it is as long as you aim for a longer range than you have data. Now I want to be able to achieve this both for columns and rows at the same time, where the rows are averaged. Could this be done? I am banging my head against the wall to find some logic to do it, but can only manage to combine it in a way that multiplies the rows with the number of the column.. which is not desired, of course.

I have posted a Minimal Reproducible Example in Excel Online: https://onedrive.live.com/view.aspx?resid=63EC0594BD919535!1491&ithint=file%2cxlsx&authkey=!ALmV0VtFb7QZCvI

If you see Cell J9 and J11 you will see what I want to combine. The three rows in J11 and down, I want to average in J10, and spill/flashfill (like J9 and 11 does automatically because of the formula already) them from to the right, for as many columns as there data in the range A1-G4..

So I have raw data of numbers with titles in A1-G4, and by writing =OFFSET($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1) in J9 I get all the titles of the columns filled from left to right, and by writing =OFFSET($A$1,1,0,COUNTA($A:$A)-1) in J11 I get the rows of the first column filled from top to bottom. They can also be combined, by writing OFFSET(Days,1,0,COUNTA($A:$A)-1,COUNTA(Days)), where "Days" is =OFFSET($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1) (in a named range for readability) or OFFSET($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1) without using a named range

As a thought, though I'm not sure how to implement it, maybe this could somehow be used in some form to get the column reference for the horizontal part in combination with =AVERAGE(OFFSET($A$1,1,0,COUNTA($A:$A)-1))

=MID(ADDRESS(ROW(),COLUMN()),2,SEARCH("$",ADDRESS(ROW(),COLUMN()),2)-2)

..found at https://superuser.com/questions/1259506/formula-to-return-just-the-column-letter-in-excel/1259507

Answer 1

Now, based on your explanation, here is the screenshot of my test:

Section A1:Exxx

I have converted that section into a Table, called «TblData», having numerous avantages:

1. It expands automatically without any additional efforts/formula
2. We can identify Data by its Columns attributed automatically by the Table [@1], [@2],[@3], [@4], [@5]

Section J9:N9

As a replica of the table name, I have used the following formula to retrieve it:

=INDEX(TblData[#Headers],1,COLUMN(A1))    '<--- This is for J9
=INDEX(TblData[#Headers],1,COLUMN(E1))    '<--- This is for N9

Section J11:Nxx

As a replica of the Table Content, I have used the following formula to populate the content:

=INDEX(TblData,ROW($A1),MATCH(J$9,TblData[#Headers],0))   '<--- This is on J11
=INDEX(TblData,ROW($A3),MATCH(N$9,TblData[#Headers],0))   '<--- This is on N13

Section J10:N10

Now this is the interesting part of the Average, so here is the formula I used for it:

=AVERAGE(TblData[1])  '<--- This is on J10
=AVERAGE(TblData[5])  '<--- This is on N10

NB: (1) Instead of using the Content below J10:N10, I prefer to reuse the Table as it expands automatically as more rows are added. (2) Unless it is really necessary, I feel it is a double work as well to replicate again A1:Exxx from J9:Nxxx, because you can use the Table for whatever you need, with less maintenance.

Kindly find attached the file as well after I updated those items:

File Link: https://drive.google.com/open?id=1wRbpUxg0XLpfGqdvMF4fNKXDrL7xPPWs

We can correspond more below for further info. Hoping you to strech more your compentence :)