Search code examples
filtergoogle-sheetsarray-formulasgoogle-sheets-formulatextjoin

How to automatically average every column in Google Sheets in their own seperate cells?

I have 2 sheets in one file, I want to take the average of the columns in the 2nd sheet sequentially (columns A, B, C, D, E, F, etc.). It would take me a very long time to type out manually because there are a lot of columns. Is there any way to do this automatically and place them in cells top to bottom left to right like my image shows?

What my sheet looks like:

What my sheet looks like

Solution
    • create a new spreadsheet and call it A
    • delete all rows except the first one
    • add columns so you end up with a range A1:IZ1
    • paste this formula into G1 cell: =IFERROR(AVERAGEA(Data!D5:D100))
    • select G1 cell
    • press CTRL + SHIFT + RIGHT ARROW
    • press CTRL + ENTER
    • go to your Stats sheet and delete all your AVERAGEA formulas eg. row 5, 7, 9, etc.
    • paste this into Stats!A5 cell:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SUBSTITUTE(SPLIT(TEXTJOIN("♦", 1, 
 REPT(FILTER(A!A1:BR1, NOT(MOD(COLUMN(A!A1:BR1), 7)))&"♦♠", 6)), "♦"), "♠", ), 1, 70))

    • paste this into Stats!A7 cell:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SUBSTITUTE(SPLIT(TEXTJOIN("♦", 1, 
 REPT(FILTER(A!B1:BS1, NOT(MOD(COLUMN(A!A1:BR1), 7)))&"♦♠", 6)), "♦"), "♠", ), 1, 70))

    • paste this into Stats!A9 cell:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SUBSTITUTE(SPLIT(TEXTJOIN("♦", 1, 
 REPT(FILTER(A!C1:BT1, NOT(MOD(COLUMN(A!A1:BR1), 7)))&"♦♠", 6)), "♦"), "♠", ), 1, 70))

    • paste this into Stats!A11 cell:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SUBSTITUTE(SPLIT(TEXTJOIN("♦", 1, 
 REPT(FILTER(A!D1:BU1, NOT(MOD(COLUMN(A!A1:BR1), 7)))&"♦♠", 6)), "♦"), "♠", ), 1, 70))

    • paste this into Stats!A13 cell:

    =ARRAYFORMULA(ARRAY_CONSTRAIN(SUBSTITUTE(SPLIT(TEXTJOIN("♦", 1, 
 REPT(FILTER(A!E1:BV1, NOT(MOD(COLUMN(A!A1:BR1), 7)))&"♦♠", 6)), "♦"), "♠", ), 1, 70))

    • etc. (the only thing whats changing is range after FILTER by 1 later)
    • a 70 at the end is the number of the last column of a purple set ending at BR column (BR = 70) green set ends on CF so that's the 84th column etc...
    • 7 represents the jump eg. to get every 7th value of A!A1:1
    • 6 represents a fix for offset because you merged 7 columns (A5:G5, H5:N5, etc.)

    0