Simple Question

[rki1966]

I am using an average function which averages the first year. I have the following fucntion in cell FO1
Average(f1:q1) and I want to copy the cell to the right so it will now average the next 12 cells. Average(r1:ac1):

Any suggestions

 

[pgc01]

Hi rki1966

Try in FO1:

=AVERAGE(OFFSET($F$1,0,12*(COLUMN()-COLUMN($FO$1)),1,12))

Hope this helps
PGC

 

[rki1966]

that worked great, can you do it with {product(1+F1:q1)-1}?

 

[pgc01]

Hi

I'm glad it worked.

For the product, try in FO1:

=PRODUCT(1+OFFSET($F$1,0,12*(-1+COLUMNS($FO$1:FO1)),1,12))-1
confirmed with CTRL+SHIFT+ENTER.

Hope it works
PGC

 

[rki1966]

The formula works great but when I try to copy the formula down, it does not change the ranges. I tried to use the f4. It looks like I can either copy down or to the right.

Any suggestions

 

[pgc01]

Hi rki1966

Try unfreezing the row:

=PRODUCT(1+OFFSET($F1,0,12*(-1+COLUMNS($FO1:FO1)),1,12))-1
confirmed with CTRL+SHIFT+ENTER.

Please try it
PGC