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
IngeborgHawighorst's avatar
IngeborgHawighorst
MVP
May 30, 2020

Hello Lucky7423  ,

 

You cannot just create a text string and use that like a range reference. You need to convert the text to a range with the Indirect function. So, if cell E2 contains a formula that returns "A1:A4" as a text, then you can use

 

=AVERAGE(INDIRECT(E2))

 

Or you can build the concatenation right into the Indirect function

 

=AVERAGE(INDIRECT(C2&C3&":"&C4&C5))

 

Screenshot to illustrate:

 

 

  • IngeborgHawighorst's avatar
    IngeborgHawighorst
    MVP
    May 30, 2020

    another approach is to use Offset() instead of Indirect() to create a variable range. The syntax for Offset is

     

    Offset(StartRange,rows,columns,height,width)

     

    Offset(A1,0,0,4) will generate a range that starts at A1, goes 0 rows down, 0 columns to the right (i.e. it anchors at A1), is 4 rows high and, since no width is specified, has the same width as the first parameter (A1, so 1 column wide). 

     

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

     

    Change the first parameter to modify the start date and change the height parameter to return more rows. 

     

     

Resources