Derived cell references in formulas

T

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?
 
M

Mark Graesser

You can use the INDIRECT function.

Good Luck
Mark Graesser
[email protected]


----- 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?
 
H

Harlan Grove

=AVERAGE(INDEX(A1:A6,A10):INDEX(A1:A6,A11))
Click to expand...
...

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))
 
Top