How to concatenate text into a formula?
 

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("Forec ast",A2:C2,0)-1),")")

How to concatenate text into a formula?
 

In article ,
Wmm wrote:

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("Forec ast",A2:C2,0)-1),")")


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

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

How to concatenate text into a formula?
 

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

"Wmm" wrote in message
...
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("Forec ast",A2:C2,0)-1),")")

How to concatenate text into a formula?
 

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" wrote:

In article ,
Wmm wrote:

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("Forec ast",A2:C2,0)-1),")")


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

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

How to concatenate text into a formula?
 

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

In article ,
Wmm wrote:

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" wrote:

In article ,
Wmm wrote:

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("Forec ast",A2:C2,0)-1),")")


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

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

How to concatenate text into a formula?
 

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

"Domenic" wrote:

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

In article ,
Wmm wrote:

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" wrote:

In article ,
Wmm wrote:

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("Forec ast",A2:C2,0)-1),")")


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

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