Excel: Flashfill Offset Horizontal + Vertical

3
votes

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

2
exceloffset
Hi Mate, I downloaded a copy of your file trying to figure out what is your issue, see that 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 doesn't make sense as You are useing twice J11. Could you explain to me please? - Tsiriniaina Rakotonirina
@TsiriniainaRakotonirina hi! So, J9 is an example of how an offset formula can be entered to get the titles of the headers in my data automatically expand to the right, for as many columns as there are titles (a "for loop" if you will - if you look to programming languages for iteration) - by just entering the formula in J9. J11 is the same, only that it repeats vertically. My desired result is to enter a formula in J10 that takes the average of J11 (so think of it as wrapping the formula in J11 with an average() formula), but then also have the effect of the formula in J9, meaning I want.. - Streching my competence
..the average of what J11 is getting, iterated for as many columns of data that there is. All with one formula, in one cell: J10. As Excel formulas are not a full fledged programming language has so called "for loops" built in, I'm not sure it's possible, but then again, algorithms/advanced logic is not my strong suit. That's why I am asking, so that if it is possible, I might also be able to understand it and utilize the logic not only for this, but maybe even to understand other complex concepts. - Streching my competence
I do also have a feeling that the formula in J21 might be a part of the key to unlocking the result I want, as it does not return the title of the columns, but rather the column names, meaning it might be possible to work it in as indirect references somehow: =MID(ADDRESS(ROW(),COLUMN(OFFSET($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1))),2,SEARCH("$",ADDRESS(ROW(),COLUMN(OFFSET($A$1:$A$1,0,0,1,COUNTA($A$1:$EV$1)-1))),2)-2) (could be cleaned up by using named references of course) - Streching my competence
In summary, if I understood you well: - J9:N9 is getting its value from A1:E1 - J11:NXX is getting its value from A2:EXX (And the rows expands) - J10:N10 is supposed to be the average of J11:NXX Isn't it? - Tsiriniaina Rakotonirina

2 Answers

2
votes

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

enter image description here

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 :)

0
votes

Sorry, mate, I can't figure out what you want to calculate. If it makes sense to add J9+J11 then you could just concatenate the two formulas in J9 and J11 with a plus sign. After much deliberation I decided to assume that your question is not one of formula but of formula-writing - "referencing" for short. Therefore I prepared this answer for you, hoping that it will prove helpful.

Building on your named range Days I suggest you create a dynamic named range Data with this formula. [Data] =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),COUNTA(Sheet1!$1:$1))

The range thus defined is dynamic in both directions. However, bearing in mind that OFFSET is volatile (slows down your worksheet) you may like to keep its use limited to this one formula and perhaps start the range at A2, but I shall tempt you to break the rule. Now you can use the INDEX function to refer to the Data range.

= INDEX(Data, [Row number], [Column number]) defines a single cell. But by setting either column or row to zero you can define an entire column or row. =INDEX(Data,0,1) defines column 1 of the Data range, =INDEX(Data,1,0) defines its first row. =INDEX(OFFSET(Data,1,0),0,1) defines the first column of a range moved down by one row from its original position. I recommend the alternative and start the Data range from A2 and perhaps declare another range for the first row if needed.

=AVERAGE(INDEX(Data,0,1)) would draw the same average you already have in your sheet, provided that Data was defined starting at A2. For fun's sake, =AVERAGE(INDEX(OFFSET(Data,1,0),0,1)) would do the same without the change in the range's definition.

=COLUMN() returns the number of the column this formula resides in. So, you could enter =COLUMN()-6 in column G, copy to the right and get a count starting from 1. (You can do the same vertically with the ROW() function.) Applied to your formula, =AVERAGE(INDEX(Data,0,COLUMN()-6)) would return the average from column 1 if entered in column G, and from columns 2, 3 4, etc as copied to the right.

As I said, I don't understand enough of your request to bring this idea to a conclusion but I think that using the method described above will provide you with a tool to copy formulas into the table your sample has at its right. If you would elaborate on your requirement I might be able to assist more.