Retrieve value 'n' rows away based on value in a reference cell.

[silviosilver]

Hello,

I would like to calculate the average of a range in a column, where the size of the average is to be determined by the value in a reference cell.

For example, if A1 = 100, then the average calculated in B1 would sum B2:B101 and divide by 100; if A1 = 50, sum B2:B51 and divide by 50.

Please help.

 

[Peter_SSs]

Try this. You may have to adjust the 1000 in my formula to suit your data.

=AVERAGE(B2:INDEX(B2:B1000,A1))

 

[silviosilver]

Thanks Peter.

 

[Peter_SSs]

Thanks Peter.

No problem - glad to help.