Google Sheets: extract one column of a named range

Question

update

My original question was about taking the average of a single column of a named range. But upon reflection, the question generalizes to "how do I extract a single column from a named range?"

original question

I'm using a named range to extract two columns from a large dataset. Assume MyRange defines three rows and two columns out of a larger dataset:

 ... |G   |H   | ...
+----|----|----|
| ...          |
|2000|15.1|  10|
|2001|15.2|  23|
|2002|15.3|  30|
| ...          |

Using MyRange, how would I take the average of (only) column H?
I've tried various things like AVERAGE(MyRange:H), and AVERAGE(INDEX(MyRange 1)) but haven't figured out the proper syntax.
(In the above example, the result should be 21.)

score 9 · Accepted Answer · answered Apr 22 '20 at 19:42

9

Or there's the tried and trusted way with index:

=average(index(NamedRange1,0,2))

(specifying 0 for the row means use the entire column)

answered Apr 22 '20 at 19:42

Tom Sharpe

30,727
5
24
37

1

Ah! That's what I was looking for. I didn't know about the significance of a 0 in the row field. – fearless_fool Apr 22 '20 at 20:19

score 3 · Answer 2 · answered Apr 22 '20 at 16:00

3

I just stumbled upon a simpler approach using FILTER():

=AVERAGE(FILTER(MyRange, {FALSE, TRUE}))

It works because FILTER(MyRange, {FALSE, TRUE}) extracts (only) the second column, which is in turn passed to AVERAGE().

answered Apr 22 '20 at 16:00

fearless_fool

33,645
23
135
217

1

Interesting solution :) – marikamitsos Apr 22 '20 at 16:01
love this solution for filtering out columns, works great for virtual ranges – Keego May 25 '22 at 01:51

marikamitsos · Answer 3 · 2020-04-22T15:02:52.040

2

You could use a query:

=Query(ArrayFormula(MyRange), "select avg(H) label avg(H) ''")

As an alternative.

Why not just create a new named range from the 3 rows in column He.g. MyRangeH?
Then, use:

=AVERAGE(MyRangeH)

edited Apr 22 '20 at 15:02

answered Apr 22 '20 at 14:56

marikamitsos

10,264
20
26

I'm using named ranges with two columns because I need the first column for other calculations -- they are logically tied together. If I created a separate named range, they could get out of sync. – fearless_fool Apr 22 '20 at 16:01
I see. You could use the query then. But I have to admit. I like your solution more. – marikamitsos Apr 22 '20 at 16:04

score 2 · Answer 4 · answered Apr 22 '20 at 21:22

Following the now updated question:

My original question was about taking the average of a single column of a named range. But upon reflection, the question generalizes to "how do I extract a single column from a named range?"

One could also use:

=AVERAGE(OFFSET(MyRange,0,2))

About OFFSET

Google Sheets: extract one column of a named range

update

original question

4 Answers4