Google Sheets: Average of every other column

Question

I’ve looked at similar questions and I think I’m close to a working solution, but it’s giving me the wrong answer. I have a spreadsheet in Google Sheets with data in all columns, but every other cell contains a dollar value and I need only the average of those cells. They start (in this version) on cell G3 and continue through most of row 3, then I intend to copy the formula to other rows with the same cells in those rows needing to be averaged as well, so if it’ll adjust as I copy that’ll be best. Here’s what I’ve worked up so far:

=AVERAGEIF(ArrayFormula(mod(column(G3:3),2)),”>0”)

It’s returning 1 as the result, when it should be about 1500. If I change the 2 to another number, the result increases with it, so I think something in mod or column is being done wrong, but I don’t have enough practice to know where I messed up.

score 3 · Answer 1 · answered Feb 22 '21 at 19:49

3

avg of every 2nd column it's done like:

=AVERAGE(FILTER(G3:3, MOD(COLUMN(G3:3)-1, 2)=0))

answered Feb 22 '21 at 19:49

player0

124,011
12
67
124

1

Or =AVERAGE(FILTER(G3:3, isodd(COLUMN(G3:3)))) I guess although not as general if OP wanted every third or fourth column instead. – Tom Sharpe Feb 22 '21 at 20:26
Thank you, this worked! Of note, are the spaces necessary or simply for easier reading? – jondesu Feb 24 '21 at 17:04
spaces in the formula are not necessary. you can skip them if you prefer so – player0 Mar 06 '21 at 16:23

Hal Lin · Answer 2 · 2022-03-05T18:25:57.730

TIL about the FILTER function. Thanks guys.

There is a way with ArrayFormula. I think you almost got it. I would prefer to add one more argument for AVERAGEIF to specify the range to average. I don't know if it defaults to the range in the condition.

And I would do the modulo on the difference between a column and the first column. I guess for your question it isn't needed as all column numbers are either odd or even. But using the difference is a general purpose way to apply the concept to say every nth column.

The modulo 2 of any column number with be 0 or 1. So instead of using an inequality just use 0 or 1. From your formula it looks like your dollar values must be in odd columns so the result of the modulo should be 1. But I think if you are starting at G3 then taking the column difference before applying Mod 2 changes the desired result to 0. But of course to switch to the other column change the 0 to a 1.

=AVERAGEIF(ArrayFormula(mod((COLUMN(G3:3)-COLUMN(G3)),2)),0,G3:3)

Google Sheets: Average of every other column

2 Answers2