Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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),")") |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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),")") |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula needed to concatenate text with result from calculation | Excel Discussion (Misc queries) | |||
Concatenate text cell and formula cell result | Excel Worksheet Functions | |||
CONCATENATE text formula | Excel Worksheet Functions | |||
Using Concatenate function to generate text in Text Box | Charts and Charting in Excel | |||
Can I concatenate text in cells to make a working formula? | Excel Discussion (Misc queries) |