Dynamic Range

[nigelk]

Hi all,

I have a data set in column A, and (for example) 2 in B1 and 10 in C1

How can I create a formula that look at the figures in B1 and C1, and gives me the average of A2:A10

The next time it could be the average of A15:A30 (whatever the figures are in B1 and C1

Thanks,Nigel

 

[taigovinda]

The most obvious way, I think, might be:

=AVERAGE(INDIRECT("A" & B1 & ":A" & C1))

This would probably be a better way in that it's more efficient/not volatile:

=AVERAGE(INDEX(A:A,B1):INDEX(A:A,C1))

 

[nigelk]

Thanks, I've tried that but it's including numbers above the upper limit.

I tried 2 and 5 (A2:A5) and then put other figures in A6:A7, and they got included as well.

Nigel.

 

[T. Valko]

Thanks, I've tried that but it's including numbers above the upper limit.

I tried 2 and 5 (A2:A5) and then put other figures in A6:A7, and they got included as well.

Nigel.

Which one did you try that's not working properly?

They both work for me but I'd use this version:

=AVERAGE(INDEX(A:A,B1):INDEX(A:A,C1))

Note that if either or both cells B1, C1 are empty then the formula will calculate the entire column A.

 

[taigovinda]

Hi Nigel,

On second glance the formula seems to work for me too...

To illustrate Biff's point (which is something that hadn't occurred to me, and I still don't understand how it works to return the whole column...):

Excel Workbook
	A	B	C	D
1		2	5	25
2	10	2	8	307.1429
3	20	2	100	461.1111
4	30		5	461.1111
5	40	2		461.1111
6	50
7	1000
8	1000
9	1000
10	1000
Sheet1

Cell Formulas
Range	Formula
D1	=AVERAGE(INDEX(A:A,B1):INDEX(A:A,C1))

Hope that helps.
Tai

 

[Aladin Akyurek]

...and I still don't understand how it works to return the whole column...):...

INDEX(VerticalRange,0)

means all rows of VerticalRange.

INDEX(HorizontalRange,0)

means all columns of HorizontalRange.

INDEX(Range,0,0)

menas all cells of Range.

 

[T. Valko]

Hi Nigel,

On second glance the formula seems to work for me too...

To illustrate Biff's point (which is something that hadn't occurred to me, and I still don't understand how it works to return the whole column...):

Excel Workbook
	A	B	C	D
1		2	5	25
2	10	2	8	307.1429
3	20	2	100	461.1111
4	30		5	461.1111
5	40	2		461.1111
6	50
7	1000
8	1000
9	1000
10	1000
Sheet1

Cell Formulas
Range	Formula
D1	=AVERAGE(INDEX(A:A,B1):INDEX(A:A,C1))

Hope that helps.
Tai

If the row_number argument evaluates to 0 then the entire referenced range is calculated.

INDEX(A:A,B1)

So, if B1 is an empty cell (empty cells evaluate to 0), then the entire column A is calculated in the formula.

 

[taigovinda]

Thanks to both of you for explaining that one - I guess in the back of my mind I knew that index(A:A,0) returns the whole column but was getting confused about constructing a reference with two index() functions as (Index)":"(Index)...where that evaluates to (A:A)":"(A5) for example.

Tai

 

[T. Valko]

Thanks to both of you for explaining that one - I guess in the back of my mind I knew that index(A:A,0) returns the whole column but was getting confused about constructing a reference with two index() functions as (Index)":"(Index)...where that evaluates to (A:A)":"(A5) for example.

Tai

Yes, and since (A5) falls within (A:A), then the final evaluation is simply A:A.