Using text for the range in AVERAGE function

[robotman]

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!

 

[Domenic]

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!

 

[robotman]

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!

 

[KL]

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

 

[Domenic]

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!