# How to average non-adjacent cells

Options
edited 06/20/22

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

• ✭✭✭✭✭✭
Options

Are you able to provide some screenshots for reference?

• Options

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!

• ✭✭✭✭✭✭
Options

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.

• Options

Thank you - I will experiment with this!

• ✭✭✭✭✭✭
Options

Happy to help. 👍️

• ✭✭✭✭
edited 06/01/23
Options

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!

• Employee
Options

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