How to average nonadjacent cells
I'm trying to average nonadjacent ranges. I've tried =AVERAGEIF([Range:Range],[Range:Range],"<>0"), but get an error each time.
Answers

Are you able to provide some screenshots for reference?

It's a really big sheet, so I don't think screenshots will work. But as an example, say my columns are A  Z. I want to calculate an average for A:E and J:M, where the # in any cell does not equal zero. I tried following the cell range references on the formula guide: [A]@row:E@row,separating them with a comma, but this resulted in the information after the comma being read as criteria rather than range. So I tried a semicolon instead ([A@row:E@row;[J@row:M@row,"<>0")  but the result is a syntax error. Thanks!

Try somethign like this:
=AVG(AVG(COLLECT(A@row:E@row, A@row:E@row, @cell <> 0)), AVG(COLLECT(J@row:M@row, J@row:M@row, @cell <> 0)))
Basically you would use an AVG/COLLECT combo for each range and use those outputs as the entries in a regular AVG function.

Thank you  I will experiment with this!


I tried following this suggestion for a similar need, but am getting #incorrect argument error. Any suggestions for modification?
=AVG([Column38]@row, AVG(COLLECT([Nonverbal  Eye Contact]@row:[Nonverbal  Poise]@row), AVG(COLLECT([Verbal  Enthusiasm]@row:[Verbal  Elocution/Speech]@row), AVG(COLLECT([Interaction  Introduction/Agenda/Objective]@row:[Interaction  Product Knowledge]@row)))))
Where column 38 is a one cell non averaged set of data, and the rest of the cells are nonadjacent multi cell ranges.
Thank you!

Hi @jcouncil
The COLLECT portion there is identifying criteria (e.g. "not 0") however in your case it looks like you're just trying to average multiple cells, is that correct?
If so, this is how you would structure that formula:
=AVG([Column38]@row, AVG([Nonverbal  Eye Contact]@row:[Nonverbal  Poise]@row), AVG([Verbal  Enthusiasm]@row:[Verbal  Elocution/Speech]@row), AVG([Interaction  Introduction/Agenda/Objective]@row:[Interaction  Product Knowledge]@row))
Note that this will weight your averages. For example, if [Column38] has a 1, then [Nonverbal  Eye Contact] and [Nonverbal  Poise] both have 10, instead of an average of 7, you'll get an average of 5.5.
This is because it first takes the average of any of your ranges ([Nonverbal  Eye Contact]:[Nonverbal  Poise] = 10), then it takes that number to average it with [Column38] (10 & 1) instead of looking at all three values separately and then taking an average together (10, 10, 1).
What I would actually suggest doing here is to SUM all your data pieces together, then divide it by the number of cells you're looking at:
=SUM([Column38]@row, SUM([Nonverbal  Eye Contact]@row:[Nonverbal  Poise]@row), SUM([Verbal  Enthusiasm]@row:[Verbal  Elocution/Speech]@row), SUM([Interaction  Introduction/Agenda/Objective]@row:[Interaction  Product Knowledge]@row)) / number
Does that make sense?
Cheers,
Genevieve

@Genevieve P. Yes! I started with sum and was trying to figure out how to average afterwards with the average function, but this is so much simpler! THANK YOU :)
Help Article Resources
Categories
Check out the Formula Handbook template!