Average of Unknown Range

M

Mike

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
 
D

Don Guillett

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}))
 
R

Ron Coderre

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
 
M

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
 
M

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
 
R

Ron Coderre

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
 
M

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
 
M

Mike

Sorry Ron,

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

Thanks again,
Mike
 
R

Ron Coderre

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
 
M

Mike

Great explanation.

Thanks!

Ron Coderre said:
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
Click to expand...
 
Top