How to average non-adjacent cells
I'm trying to average non-adjacent 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!
-
Happy to help. 👍️
-
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@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
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!