How to concatenate text into a formula?

[Wmm]

Is there a way to use a formula as the ending cell reference to the AVERAGE
function?

If not, how can I build a formula through concatenation and have it execute?
Here is my example. I use MATCH to find the first column with the value
"Forecast", then back up to the previous column (-1) to get the ending column
for the average function.
=CONCATENATE("=AVERAGE(A1:",ADDRESS(2,MATCH("Forecast",A2:C2,0)-1),")")

 

[Domenic]

Is there a way to use a formula as the ending cell reference to the AVERAGE
function?

If not, how can I build a formula through concatenation and have it execute?
Here is my example. I use MATCH to find the first column with the value
"Forecast", then back up to the previous column (-1) to get the ending column
for the average function.
=CONCATENATE("=AVERAGE(A1:",ADDRESS(2,MATCH("Forecast",A2:C2,0)-1),")")

Somewhat unclear... Can you post a small sample of the data, along with
the expected result?

 

[David Biddulph]

It sounds as if you want the INDIRECT function (see Excel help)

 

[Wmm]

Sample Data:
A1 = ""
B1 = ""
C1 = "Forecast"
....
A2 = 1
B2 = 2
C2 = 3
....

I want to average the numbers in row 2 where row 1 does not contain
"Forecast". This are monthly numbers, so once "Forecast" is encountered in
row 1, the previous column will represent the end of the range to be AVERAGEd.

I don't think the INDIRECT function applies here because I'm not looking for
the cell contents. I'm trying to find a way to dynamically change the
AVERAGE range, and I can't find a way to let one of the parameters of the
AVERAGE function be another function.

 

[Domenic]

Try...

=AVERAGE(A2:INDEX(A2:C2,MATCH("Forecast",$A$1:$C$1,0)-1))

 

[Wmm]

That did it! Thank you so much for the help.

Try...

=AVERAGE(A2:INDEX(A2:C2,MATCH("Forecast",$A$1:$C$1,0)-1))

--
Domenic
Microsoft Excel MVP
www.xl-central.com
Your Quick Reference to Excel Solutions