Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using text for the range in AVERAGE function
I'm trying to use a text string as the range in the AVERAGE function.
The AVERAGE function doesn't seem to like this. For example: =AVERAGE("A2" & ":" & "A6") (my real sheet uses VLOOKUP to derive the "A2" and "A6" references, but if you can get this simple text range to work, I can expand from there) I found one way that uses INDIRECT and ADDRESS, but this seems cumbersome. = AVERAGE(INDIRECT(ADDRESS(2,1) & ":" & ADDRESS(6,1))) Anyone have a simpler way to use text as the range input for the AVERAGE function? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using text for the range in AVERAGE function
I'm not sure I understand you correctly, but if C2 and D2 contain
VLOOKUP formulas and return the text A2 and A6, respectively, you could use the following formula... =AVERAGE(INDIRECT(C2&":"&D2)) Hope this helps! In article om, wrote: I'm trying to use a text string as the range in the AVERAGE function. The AVERAGE function doesn't seem to like this. For example: =AVERAGE("A2" & ":" & "A6") (my real sheet uses VLOOKUP to derive the "A2" and "A6" references, but if you can get this simple text range to work, I can expand from there) I found one way that uses INDIRECT and ADDRESS, but this seems cumbersome. = AVERAGE(INDIRECT(ADDRESS(2,1) & ":" & ADDRESS(6,1))) Anyone have a simpler way to use text as the range input for the AVERAGE function? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using text for the range in AVERAGE function
Basically I'm building a text string using various functions that ends
up looking like a reference: textstring might look like "A" & "3" & ":" & "A" & "10" (The actual way I build up this string uses several different functions to get the column and rows I need) I then want to use this string in the AVERAGE function. AVERAGE(the textstring) Is there a simple TEXT to ADDRESS RANGE function that anyone knows about? Thanks! Domenic wrote: I'm not sure I understand you correctly, but if C2 and D2 contain VLOOKUP formulas and return the text A2 and A6, respectively, you could use the following formula... =AVERAGE(INDIRECT(C2&":"&D2)) Hope this helps! In article om, wrote: I'm trying to use a text string as the range in the AVERAGE function. The AVERAGE function doesn't seem to like this. For example: =AVERAGE("A2" & ":" & "A6") (my real sheet uses VLOOKUP to derive the "A2" and "A6" references, but if you can get this simple text range to work, I can expand from there) I found one way that uses INDIRECT and ADDRESS, but this seems cumbersome. = AVERAGE(INDIRECT(ADDRESS(2,1) & ":" & ADDRESS(6,1))) Anyone have a simpler way to use text as the range input for the AVERAGE function? Thanks! |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using text for the range in AVERAGE function
I would use the non-volatile INDEX:
=AVERAGE(INDEX(A1:A100,D1):INDEX(A1:A100,D2)) where D1=3 D2=10 if the column is also variable, then =AVERAGE(INDEX(A1:H100,D1,E1):INDEX(A1:H100,D2,E1) ) where D1=3 D2=10 E1=1 Regards, KL wrote in message ups.com... Basically I'm building a text string using various functions that ends up looking like a reference: textstring might look like "A" & "3" & ":" & "A" & "10" (The actual way I build up this string uses several different functions to get the column and rows I need) I then want to use this string in the AVERAGE function. AVERAGE(the textstring) Is there a simple TEXT to ADDRESS RANGE function that anyone knows about? Thanks! Domenic wrote: I'm not sure I understand you correctly, but if C2 and D2 contain VLOOKUP formulas and return the text A2 and A6, respectively, you could use the following formula... =AVERAGE(INDIRECT(C2&":"&D2)) Hope this helps! In article om, wrote: I'm trying to use a text string as the range in the AVERAGE function. The AVERAGE function doesn't seem to like this. For example: =AVERAGE("A2" & ":" & "A6") (my real sheet uses VLOOKUP to derive the "A2" and "A6" references, but if you can get this simple text range to work, I can expand from there) I found one way that uses INDIRECT and ADDRESS, but this seems cumbersome. = AVERAGE(INDIRECT(ADDRESS(2,1) & ":" & ADDRESS(6,1))) Anyone have a simpler way to use text as the range input for the AVERAGE function? Thanks! |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Using text for the range in AVERAGE function
It's unclear which formulas you have generating which characters. For
example, are the ampersand and colon being generated as well? If so, there's no need to. If formulas for C2, D2, E2, and F2 generate A, 3, A, and 10 respectively, you could use... =AVERAGE(INDIRECT(C2&D2&":"&E2&F2)) Hope this helps! In article . com, wrote: Basically I'm building a text string using various functions that ends up looking like a reference: textstring might look like "A" & "3" & ":" & "A" & "10" (The actual way I build up this string uses several different functions to get the column and rows I need) I then want to use this string in the AVERAGE function. AVERAGE(the textstring) Is there a simple TEXT to ADDRESS RANGE function that anyone knows about? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing a range name as an argument to the Index Function | Excel Discussion (Misc queries) | |||
Modified Average Function | Excel Worksheet Functions | |||
numerical integration | Excel Discussion (Misc queries) | |||
Match function...random search? | Excel Worksheet Functions | |||
Macro or Function to make text size to suite text Length? | Excel Discussion (Misc queries) |