Forum Discussion

Lucky7423's avatar
Lucky7423
Copper Contributor
May 30, 2020

Embedded CONCAT in AVERAGE function results in #VALUE! error

I'm trying to dynamically put a variable range of cells in an AVERAGE function to cover different periods preceding the most recent.  In this approach I would only have to change the last row number ...
excel
Formulas and Functions
Jos_Woolley's avatar
Jos_Woolley
Iron Contributor
May 31, 2020

Lucky7423 

 

Just to add to IngeborgHawighorst's posts, it should be pointed out that volatile INDIRECT and OFFSET set-ups can almost always be replaced with a non-volatile INDEX one in such cases.

 

For example, the following are all equivalent:

 

=AVERAGE(C1:INDEX(C:C,A1))

=AVERAGE(INDIRECT("C1:C"&A1))

=AVERAGE(OFFSET(C1,0,0,A1))

 

though only the first is non-volatile (well, not quite: technically volatile just once, i.e. when the workbook is first opened, but for all intents and purposes that's non-volatile).

 

Regards

  • Lucky7423's avatar
    Lucky7423
    Copper Contributor
    May 31, 2020

    Jos_Woolley  Thank you for responding. Seeing your three examples together is helpful.

    • SergeiBaklan's avatar
      SergeiBaklan
      Diamond Contributor
      May 31, 2020

      Lucky7423 

      One more point - it looks like you calculate the same range sizes. They could be defined in any helper range, e.g.

      And if in A1 the last row to use, e.g. 2820, formula for 7 days average could be, as suggested earlier,

      =AVERAGE(INDEX(D:D,$A$1-$H1+1):INDEX(D:D,$A$1))

      and drag it down to receive another 4 averages.

       

       

Resources