How to average non-adjacent cells

Ldonoghue

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

Paul Newcome

Are you able to provide some screenshots for reference?

Ldonoghue

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!

Paul Newcome

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.

Ldonoghue

Thank you - I will experiment with this!

Paul Newcome

Happy to help. 👍️

jcouncil

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!

Genevieve P.

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

jcouncil

@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 :)