Search code examples
excelmoving-averagerefer

Refer to index value present in same row

For a large series of numbers I would like to find out a certain moving average. I want for example in T(750) the average of K2:K(index). This certain index can be found in the same row, in column B. So in Cell B750. How can I do this?

Any help is much appreciated

Solution

  • Use INDEX():

    =AVERAGE($K$2:INDEX($K:$K,B750))

    Or to ensure no errors as @Jeeped suggested

    =AVERAGE($K$2:INDEX($K:$K,MAX(B750,2))

    Otherwise you will get errors if B750 is text or null.