Forum Discussion
Find last value in a row (that will change) and calculate weighted average
OFFSET(StartingReference,0,ColumnsToDisplace,Height,Width)
If you are on column G, means that you will need to displace only 1 column from your StartingReference and then 12 columns of width (12 months range).
(Column(G)-6)*12-11=1 = ($N$2:Width)$N$2:$Y$2
(Column(H)-6)*12-11=2 = ($Z$2:Width)
$Z$2:$AK$2
And so on...
I guess you need a way to dinamically develop an array calculation.
Considering you are on G column, you can play with the following AVERAGE with dinamic range using OFFSET formula.
=AVERAGE(OFFSET($M2,0,(COLUMN()-6)*12-11,1,12))
Could you please explain to me how this part of the formula works? (COLUMN()-6)*12-11,1,12)
OFFSET(StartingReference,0,ColumnsToDisplace,Height,Width)
If you are on column G, means that you will need to displace only 1 column from your StartingReference and then 12 columns of width (12 months range).
(Column(G)-6)*12-11=1 = ($N$2:Width)$N$2:$Y$2
(Column(H)-6)*12-11=2 = ($Z$2:Width)
$Z$2:$AK$2
And so on...
- thank you!