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.
Mikethe trick is to try to match a number larger than any possible in the column
=AVERAGE(SMALL(INDIRECT(quot;f2:fquot;amp;MATCH(999999999,F:F )),{1,2,3,4,5}))
--
Don Guillett
SalesAid Software
quot;Mikequot; gt; wrote in message
...
gt;I am trying to take an average of a changing range of numbers.
gt; The range always starts at A2 but ends at different rows of column A.
gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; determine A?? (the last row containing data).
gt; Note: I have a field that contains the next available row number, so I
gt; could
gt; use it minus 1 except I don't know how to get the formula to use this
gt; value.
gt;
gt; Thanks for any help.
gt; Mike
gt;
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-Proquot;Mikequot; wrote:
gt; I am trying to take an average of a changing range of numbers.
gt; The range always starts at A2 but ends at different rows of column A.
gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; determine A?? (the last row containing data).
gt; Note: I have a field that contains the next available row number, so I could
gt; use it minus 1 except I don't know how to get the formula to use this value.
gt;
gt; Thanks for any help.
gt; Mike
gt;
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)))
Mikequot;Ron Coderrequot; wrote:
gt; Try something like this:
gt;
gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; location of the last numeric value in Col_A:
gt;
gt; This formula returns the average of the smallest 5 numeric cells:
gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; determine A?? (the last row containing data).
gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt;
gt; gt; Thanks for any help.
gt; gt; Mike
gt; gt;
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)
Mikequot;Ron Coderrequot; wrote:
gt; Try something like this:
gt;
gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; location of the last numeric value in Col_A:
gt;
gt; This formula returns the average of the smallest 5 numeric cells:
gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt;
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; determine A?? (the last row containing data).
gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt;
gt; gt; Thanks for any help.
gt; gt; Mike
gt; gt;
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-Proquot;Mikequot; wrote:
gt; I take it from the two responses I've received that it is not possible to
gt; take the value in a cell and use it as a pointer to a cell?
gt; i.e. B1=400
gt; So, C1=AVERAGE(A2:A,(B1-1)
gt; would come out to be
gt; C1=AVERAGE(A2:A399)
gt;
gt; Mike
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try something like this:
gt; gt;
gt; gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; gt; location of the last numeric value in Col_A:
gt; gt;
gt; gt; This formula returns the average of the smallest 5 numeric cells:
gt; gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt; gt;
gt; gt;
gt; gt; Does that help?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Mikequot; wrote:
gt; gt;
gt; gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; gt; determine A?? (the last row containing data).
gt; gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt; gt;
gt; gt; gt; Thanks for any help.
gt; gt; gt; Mike
gt; gt; gt;
I pasted that in and it worked.
Now, any chance you could explain it a little?
I don't see the purpose of the quot;A:Aquot; preceding the quot;(B1-1)quot;
Mikequot;Ron Coderrequot; wrote:
gt; Try this:
gt;
gt; B1l: (a numeric value)
gt; C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))
gt;
gt; Is that what you're looking for?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I take it from the two responses I've received that it is not possible to
gt; gt; take the value in a cell and use it as a pointer to a cell?
gt; gt; i.e. B1=400
gt; gt; So, C1=AVERAGE(A2:A,(B1-1)
gt; gt; would come out to be
gt; gt; C1=AVERAGE(A2:A399)
gt; gt;
gt; gt; Mike
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; gt; gt; location of the last numeric value in Col_A:
gt; gt; gt;
gt; gt; gt; This formula returns the average of the smallest 5 numeric cells:
gt; gt; gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Mikequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; gt; gt; determine A?? (the last row containing data).
gt; gt; gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for any help.
gt; gt; gt; gt; Mike
gt; gt; gt; gt;
Sorry Ron,
I shouldn't have posted so quickly. After further analysis I see the
structure of the formula.
Thanks again,
Mikequot;Ron Coderrequot; wrote:
gt; Try this:
gt;
gt; B1l: (a numeric value)
gt; C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))
gt;
gt; Is that what you're looking for?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I take it from the two responses I've received that it is not possible to
gt; gt; take the value in a cell and use it as a pointer to a cell?
gt; gt; i.e. B1=400
gt; gt; So, C1=AVERAGE(A2:A,(B1-1)
gt; gt; would come out to be
gt; gt; C1=AVERAGE(A2:A399)
gt; gt;
gt; gt; Mike
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try something like this:
gt; gt; gt;
gt; gt; gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; gt; gt; location of the last numeric value in Col_A:
gt; gt; gt;
gt; gt; gt; This formula returns the average of the smallest 5 numeric cells:
gt; gt; gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; Does that help?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Mikequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; gt; gt; determine A?? (the last row containing data).
gt; gt; gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt; gt; gt;
gt; gt; gt; gt; Thanks for any help.
gt; gt; gt; gt; Mike
gt; gt; gt; gt;
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-Proquot;Mikequot; wrote:
gt; I pasted that in and it worked.
gt; Now, any chance you could explain it a little?
gt; I don't see the purpose of the quot;A:Aquot; preceding the quot;(B1-1)quot;
gt;
gt; Mike
gt;
gt;
gt; quot;Ron Coderrequot; wrote:
gt;
gt; gt; Try this:
gt; gt;
gt; gt; B1l: (a numeric value)
gt; gt; C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))
gt; gt;
gt; gt; Is that what you're looking for?
gt; gt;
gt; gt; ***********
gt; gt; Regards,
gt; gt; Ron
gt; gt;
gt; gt; XL2002, WinXP-Pro
gt; gt;
gt; gt;
gt; gt; quot;Mikequot; wrote:
gt; gt;
gt; gt; gt; I take it from the two responses I've received that it is not possible to
gt; gt; gt; take the value in a cell and use it as a pointer to a cell?
gt; gt; gt; i.e. B1=400
gt; gt; gt; So, C1=AVERAGE(A2:A,(B1-1)
gt; gt; gt; would come out to be
gt; gt; gt; C1=AVERAGE(A2:A399)
gt; gt; gt;
gt; gt; gt; Mike
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt;
gt; gt; gt; gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; gt; gt; gt; location of the last numeric value in Col_A:
gt; gt; gt; gt;
gt; gt; gt; gt; This formula returns the average of the smallest 5 numeric cells:
gt; gt; gt; gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; Does that help?
gt; gt; gt; gt;
gt; gt; gt; gt; ***********
gt; gt; gt; gt; Regards,
gt; gt; gt; gt; Ron
gt; gt; gt; gt;
gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Mikequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; gt; gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; gt; gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; gt; gt; gt; determine A?? (the last row containing data).
gt; gt; gt; gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; gt; gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Thanks for any help.
gt; gt; gt; gt; gt; Mike
gt; gt; gt; gt; gt;
Great explanation.
Thanks!
quot;Ron Coderrequot; wrote:
gt; OK...Here's the explanation:
gt;
gt; Regarding: INDEX(A:A,(B1-1))
gt;
gt; The INDEX function starts with a range and returns a reference to a cell in
gt; that range as indicated by the RowRef and ColRef arugments.
gt;
gt; Example:
gt; In =INDEX(A5:C10,2,3) we start with the range A5:C10 and isolate the cell
gt; that is in the second row and third column of that grid....C6.
gt;
gt; In our case, the range A:A refers to a single column so no ColRef is required.
gt; Since you could choose ANY row in Col_A to be the last cell referenced by
gt; the formula we have to assume that it could be anywhere in Col_A. Hence, the
gt; A:A reference.
gt;
gt; Does that help?
gt;
gt; ***********
gt; Regards,
gt; Ron
gt;
gt; XL2002, WinXP-Pro
gt;
gt;
gt; quot;Mikequot; wrote:
gt;
gt; gt; I pasted that in and it worked.
gt; gt; Now, any chance you could explain it a little?
gt; gt; I don't see the purpose of the quot;A:Aquot; preceding the quot;(B1-1)quot;
gt; gt;
gt; gt; Mike
gt; gt;
gt; gt;
gt; gt; quot;Ron Coderrequot; wrote:
gt; gt;
gt; gt; gt; Try this:
gt; gt; gt;
gt; gt; gt; B1l: (a numeric value)
gt; gt; gt; C1: =AVERAGE(A2:INDEX(A:A,(B1-1)))
gt; gt; gt;
gt; gt; gt; Is that what you're looking for?
gt; gt; gt;
gt; gt; gt; ***********
gt; gt; gt; Regards,
gt; gt; gt; Ron
gt; gt; gt;
gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt;
gt; gt; gt;
gt; gt; gt; quot;Mikequot; wrote:
gt; gt; gt;
gt; gt; gt; gt; I take it from the two responses I've received that it is not possible to
gt; gt; gt; gt; take the value in a cell and use it as a pointer to a cell?
gt; gt; gt; gt; i.e. B1=400
gt; gt; gt; gt; So, C1=AVERAGE(A2:A,(B1-1)
gt; gt; gt; gt; would come out to be
gt; gt; gt; gt; C1=AVERAGE(A2:A399)
gt; gt; gt; gt;
gt; gt; gt; gt; Mike
gt; gt; gt; gt;
gt; gt; gt; gt;
gt; gt; gt; gt; quot;Ron Coderrequot; wrote:
gt; gt; gt; gt;
gt; gt; gt; gt; gt; Try something like this:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; for a list if values (or blanks) beginning in A2 and ending with the
gt; gt; gt; gt; gt; location of the last numeric value in Col_A:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; This formula returns the average of the smallest 5 numeric cells:
gt; gt; gt; gt; gt; B1: =AVERAGE(SMALL(A2:INDEX(A:A,MATCH(10^99,A:A)),{1,2 ,3,4,5}))
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; Does that help?
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; ***********
gt; gt; gt; gt; gt; Regards,
gt; gt; gt; gt; gt; Ron
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; XL2002, WinXP-Pro
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; quot;Mikequot; wrote:
gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; I am trying to take an average of a changing range of numbers.
gt; gt; gt; gt; gt; gt; The range always starts at A2 but ends at different rows of column A.
gt; gt; gt; gt; gt; gt; If I'm using the formula =AVERAGE(SMALL(A2:A??,{1,2,3,4,5})), how do I
gt; gt; gt; gt; gt; gt; determine A?? (the last row containing data).
gt; gt; gt; gt; gt; gt; Note: I have a field that contains the next available row number, so I could
gt; gt; gt; gt; gt; gt; use it minus 1 except I don't know how to get the formula to use this value.
gt; gt; gt; gt; gt; gt;
gt; gt; gt; gt; gt; gt; Thanks for any help.
gt; gt; gt; gt; gt; gt; Mike
gt; gt; gt; gt; gt; gt;
留言列表
