Forum Discussion

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

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

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.