Averaging cells that have both blanks and numbers for values that are in non continous columns with nine columns in between them

I need help averaging cells that contain numbers ranging from -1200 to 1200 approximately, or blank values only. I will be averaging them by groupings of 100 increments ie. -1200 to -1100, -1100 to-1000 etc...the cells are contained in 16 columns that are non-continuous or adjacent. There are 9 columns between each column that contains values to be averaged. My attempts either give me a divide by zero error or when I attempt to resolve blanks with if statements and " " I get text cannot be coerced into numbers. I read a solution that involves frequency and index functions that looks like this

``````=SUM(B3:B7,D3:D7,F3:F7)/INDEX(FREQUENCY((B3:B7,D3:D7,F3:F7),0),2)
``````

but have not been able to get it to work for my application it was an Excel solution and my issue is in Google Sheets so? https://docs.google.com/spreadsheets/d/1kwGbFd88cgifQRK_ZqsahQt1aDLzMTnmxHKs7mML5jA/edit?usp=sharing

So (C4:C67, D4:D64, J4:J:67, K4:K67, Q4:Q:67, R4:R67) I would like to have an average value of all numbers say from -200 to -300 and for it to be entered in the destinations (b:120,b:137)

• share a copy of your sheet May 25, 2019 at 12:44

``````=TRANSPOSE(INDEX(QUERY(TRANSPOSE(A3:I5),
"select avg(Col1), avg(Col2), avg(Col3)"), 2, ))``````

``````=SUM(A3:A5, C3:C5, E3:E5, G3:G5, I3:I5)/
COUNTA({A3:A5, C3:C5, E3:E5, G3:G5, I3:I5})``````

``````=ARRAYFORMULA(AVERAGE(IF(({C4:C67,J4:J67,Q4:Q67}>=-150)*
({C4:C67,J4:J67,Q4:Q67}< -125), {C4:C67,J4:J67,Q4:Q67}, )))``````

• I left a link and updated my question. I tried using the formula but could not get it to work. I appreciate your help can you look over the link and the updated info and see if something is different May 25, 2019 at 15:59