Any way to average every other column while shifting cells every new column?

[Harry0]

I looked in the forums and online but could not find something like this in excel formula.
To have one sell that averages and the other to sum every other cell in the row, while shifting to the right every time I insert a new column.

I have the sum of every other column in the row, But it does not shift every time I add a new months. The final formula is more complicated since I calcualte around 13 weeks from a list of over 4000 places.
odds =SUMPRODUCT(A1:H1,MOD(COLUMN(A1:H1),2))
evens =SUMPRODUCT(A1:H1,MOD(COLUMN(A1:H1)+1,2))

And so I need one like that which also shifts as well which I do not have.

attached is an example. which I hope I explained it well enough.
Thanks

 

[r2c2]

@Harry0

You can use OFFSET to dynamically generate the range containing previous 3 or 4 months of data and feed this to SUMPRODUCT to calculate the sum or count or average.

For example, below formula gives you sum. (assuming your data is in row 4)

=SUMPRODUCT(OFFSET($J4,,-6,1,6),MOD(COLUMN(OFFSET($J4,,-6,1,6))-1,2))

Change -6 and 6 to -8 and 8 to get previous 4 months value.

You can then use below formula to calculate average.

=SUMPRODUCT(OFFSET($J4,,-6,1,6),MOD(COLUMN(OFFSET($J4,,-6,1,6))-1,2))/SUMPRODUCT((LEN(OFFSET($J4,,-6,1,6))>0)*(MOD(COLUMN(OFFSET($J4,,-6,1,6))-1,2)))

It is a bit long because we need to refer to the offset(...) portion everywhere.

One way to make it shorter is by creating a named formula.

But a better option would be to use AVERAGEIFS along with 1 extra row of helper data on top of months. like below:

=AVERAGEIFS($B4:J4,$B$3:J$3,"*"&Q$3,$B$2:J$2,">"&(MAX($B$2:J$2)-Q$2))

In this, row 3 has month names along with X or Y.
Row 2 has numbers 1,1,2,2,3,3...
and Q3 has X in it and Q2 has 3 in it (since you want to average last 3 months)

See attached workbook.

 

[Harry0]

That is pretty skillful work.
But when i use the formula average it does not come up the same value.
I assume its complicated and did not expect the formula to be so long which would take up more power.
What would be the solution without the offset in the average for every other?
Thank you