How can I calculate the mean of all months until now?

Question

I have a column with a value for each month, like: 1,2,3,...,12. And I have a row with 12 cells (each corresponding to a month). And I have another cell that says the current month. My question is: How can I calculate the mean of the values until the current month?

I was doing something like « =MEAN(IF(S4>S16;D4:O4;IF(S4>S15;D4:N4; IF(S4>S14;D4:M4; ...)))) »

S4 is the current month; S5 = Jan; S16 = Dec; D4 = value of Jan; O4 = value of Dec

I'm sure there is a better way to do this.

Answer 1

Difficult to tell exactly what you want from your question, but the following takes the average of the values of S4 rows (months) starting from D4 (the first month):

AVERAGE(OFFSET(D4,0,0,S4,1))

If you want to copy that around, be careful with your absolute references ($) (e.g. you would want at least D$4).