Derived cell references in formulas

[tandoorfalafel]

I have an array of values (A1:An) and depending on what data is entered
in other cells, I only want to use a subset of the array (Ax:Ay) in
several functions that take an array as an argument.

Example:

A1 3
A2 5
A3 9
A4 4
A5 6
A6 3
A7 =AVERAGE(A2:A5) .......... 6
A8 =ADDRESS(A10,1,1,1,"Worksheet") .......... "$A$2"
A9 =ADDRESS(A11,1,1,1,"Worksheet") .......... "$A$5"
A10 2
A11 5

Is there some way to use the cell references to cells A2 and A5 derived
from the functions in A8 and A9 in the function in cell A7? This
function returns an error:

=AVERAGE(A8:A9)

I'm looking for a function, f(x), such that...

=AVERAGE(f(A8):f(A8))

will return 6. Any ideas?

 

[Guest]

You can use the INDIRECT function.

Good Luck
Mark Graesser
(e-mail address removed)


----- tandoorfalafel wrote: -----

I have an array of values (A1:An) and depending on what data is entered
in other cells, I only want to use a subset of the array (Ax:Ay) in
several functions that take an array as an argument.

Example:

A1 3
A2 5
A3 9
A4 4
A5 6
A6 3
A7 =AVERAGE(A2:A5) .......... 6
A8 =ADDRESS(A10,1,1,1,"Worksheet") .......... "$A$2"
A9 =ADDRESS(A11,1,1,1,"Worksheet") .......... "$A$5"
A10 2
A11 5

Is there some way to use the cell references to cells A2 and A5 derived
from the functions in A8 and A9 in the function in cell A7? This
function returns an error:

=AVERAGE(A8:A9)

I'm looking for a function, f(x), such that...

=AVERAGE(f(A8):f(A8))

will return 6. Any ideas?

 

[Aladin Akyurek]

=AVERAGE(INDEX(A1:A6,A10):INDEX(A1:A6,A11))

 

[Harlan Grove]

=AVERAGE(INDEX(A1:A6,A10):INDEX(A1:A6,A11))

 

[Harlan Grove]

=AVERAGE(INDEX(A1:A6,A10):INDEX(A1:A6,A11))

...

Oops! Sorry for that last blank response.

Quibble: this could be done with just two function calls.

=AVERAGE(OFFSET(A1:A6,A10-1,0,A11-A10+1,1))