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!