How to concatenate text into a formula?

W

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),")")
 
D

Domenic

Wmm said:
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?
 
W

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top