Construct an an array from worksheet values

[OceanDesigner]

I am sure this is common, but I can't seem to figure it out. I am calculating the average of a column of data. Ordinarily I would just write =AVERAGE(A1:A5). In this case, the array is specified in other cells after various lookups. Columns A and B have data. Columns D1 and E1 hold the row and column of the first cell in the array. Columns C2 and D2 hold the row and column of the last cell in the array. In the following example, I want AVERAGE(B3:B5). I need help converting the integer cell values (2,3) and (2,5) into an array that I can use to calculate the average.

A B C D E
1 6 2 3
2 2 2 5
3 7
4 8
5 9

Thanks, Jeff

 

[xlbo]

Have a look at the INDIRECT function

 

[AnotherHiggins]

Honestly, I'm having trouble following your post (but I am still pretty tired). First, "Columns C2" makes no sense. I assume you mean "Cell C2..."

Also, you don't say what exactly you want to do with the values in D1, E1, C2 and D2. (Are those really in different columns?)

I think what you're looking for is the INDIRECT function.

Try something along the lines of
[COLOR=blue white]=AVERAGE(INDIRECT("A"&C1&":A"&D1))[/color]

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[OceanDesigner]

Got it. Thanks.

 

[Loomah]

Something like
=AVERAGE(INDIRECT("R"&D1&"C"&E1,0)&":"&INDIRECT("R"&D2&"C"&E2,0))

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[OceanDesigner]

I am using the following:

=AVERAGE(INDIRECT(ADDRESS(D1,E1)&":"&ADDRESS(D2,E2)))

 

[Loomah]

forget that it doesn't work!

;-)

If a man says something and there are no women there to hear him, is he still wrong?
How do I get the best answers?​

 

[Loomah]

Re my last post - I mean my suggestion gives funny results!