Forum Discussion
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 ...
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 THANK YOU! Problem solved!