I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

the trick is to try to match a number larger than any possible in the column
=AVERAGE(SMALL(INDIRECT("f2:f"&MATCH(999999999,F:F )),{1,2,3,4,5}))

--
Don Guillett
SalesAid Software

"Mike" wrote in message
...
I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I
could
use it minus 1 except I don't know how to get the formula to use this
value.

Thanks for any help.
Mike

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

That works.
Could I do the same type of thing for a straight average?
B1: =AVERAGE(A2:INDEX(A:A,MATCH(10^99,A:A)))

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

Try this:

B1l: (a numeric value)
C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

I pasted that in and it worked.
Now, any chance you could explain it a little?
I don't see the purpose of the "A:A" preceding the "(B1-1)"

Mike


"Ron Coderre" wrote:

Try this:

B1l: (a numeric value)
C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

Sorry Ron,

I shouldn't have posted so quickly. After further analysis I see the
structure of the formula.

Thanks again,
Mike


"Ron Coderre" wrote:

Try this:

B1l: (a numeric value)
C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

OK...Here's the explanation:

Regarding: INDEX(A:A,(B1-1))

The INDEX function starts with a range and returns a reference to a cell in
that range as indicated by the RowRef and ColRef arugments.

Example:
In =INDEX(A5:C10,2,3) we start with the range A5:C10 and isolate the cell
that is in the second row and third column of that grid....C6.

In our case, the range A:A refers to a single column so no ColRef is required.
Since you could choose ANY row in Col_A to be the last cell referenced by
the formula we have to assume that it could be anywhere in Col_A. Hence, the
A:A reference.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I pasted that in and it worked.
Now, any chance you could explain it a little?
I don't see the purpose of the "A:A" preceding the "(B1-1)"

Mike


"Ron Coderre" wrote:

Try this:

B1l: (a numeric value)
C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike

Great explanation.

Thanks!

"Ron Coderre" wrote:

OK...Here's the explanation:

Regarding: INDEX(A:A,(B1-1))

The INDEX function starts with a range and returns a reference to a cell in
that range as indicated by the RowRef and ColRef arugments.

Example:
In =INDEX(A5:C10,2,3) we start with the range A5:C10 and isolate the cell
that is in the second row and third column of that grid....C6.

In our case, the range A:A refers to a single column so no ColRef is required.
Since you could choose ANY row in Col_A to be the last cell referenced by
the formula we have to assume that it could be anywhere in Col_A. Hence, the
A:A reference.

Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I pasted that in and it worked.
Now, any chance you could explain it a little?
I don't see the purpose of the "A:A" preceding the "(B1-1)"

Mike


"Ron Coderre" wrote:

Try this:

B1l: (a numeric value)
C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))

Is that what you're looking for?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I take it from the two responses I've received that it is not possible to
take the value in a cell and use it as a pointer to a cell?
i.e. B1=400
So, C1=AVERAGE(A2:A,(B1-1)
would come out to be
C1=AVERAGE(A2:A399)

Mike


"Ron Coderre" wrote:

Try something like this:

for a list if values (or blanks) beginning in A2 and ending with the
location of the last numeric value in Col_A:

This formula returns the average of the smallest 5 numeric cells:
B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))


Does that help?

***********
Regards,
Ron

XL2002, WinXP-Pro


"Mike" wrote:

I am trying to take an average of a changing range of numbers.
The range always starts at A2 but ends at different rows of column A.
If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
determine A?? (the last row containing data).
Note: I have a field that contains the next available row number, so I could
use it minus 1 except I don't know how to get the formula to use this value.

Thanks for any help.
Mike