1

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)

1
  • 1
    share a copy of your sheet – user0 May 25 '19 at 12:44
0

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

0

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

0


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

0

3
  • 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 – XCATHADOR May 25 '19 at 15:59
  • @XCATHADOR answer and your sheet updated – user0 May 25 '19 at 17:20
  • This was exactly what i was looking for it worked perfect. Genius – XCATHADOR May 25 '19 at 18:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.