Google-sheets – Issue with finding the averages of multiple rows at once

formulasgoogle sheetsgoogle-sheets-arrayformulagoogle-sheets-queryregex

I am currently tasked with hundreds of rows of data for a project. I need to take the average of each row, but there are way too many rows for me to do by each individual row. Is there any way to take the average of each of these rows all at once? I really do not want to spend hours doing this tedious calculation.

I attached a picture to give you an idea of what I'm asking for.

Best Answer

paste this in E2 cell:

=ARRAYFORMULA((QUERY(TRANSPOSE(QUERY(TRANSPOSE(A2:D), 
 "select "&REGEXREPLACE(JOIN( , IF(LEN(A2:A),
 "avg(Col"&ROW(A2:A)-ROW(A2)+1&"),", )), ".\z", "")&"")), "select Col2")))

Related Solutions

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

It appears that you are using regexmatch just to test string equality. This isn't necessary, equalities can be entered simply as Data!A:A = A1. You don't even need lower, because string comparison in filter is case-insensitive:

=filter(Data!A:R, Data!A:A = A1, Data!B:B = B, Data!C:C = C1)

(Also, there is no need for single quotes around sheet name when it does not contain spaces.)

Formula Based on Cell Positions from Target Cell in Google Sheets

The command offset does what you want: =offset(D3, -1, -2) is the value 1 row above and two columns to the left of D3. So, your formula for D3 could be

 =offset(D3,-1,0) + offset(D3,0,-2) - offset(D3,0,-1)

Extended down the column, it will do the intended computations regardless of row insertions.

Best Answer

Related Solutions

Google-sheets – Case insensitive character string filter for multiple individual columns (dynamic input for each)

Formula Based on Cell Positions from Target Cell in Google Sheets

Related Topic