Hi,

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

I have a feeling some will say it's not necessary to find the
bottom row number. But I still would like to know how to find it.
Oh, and I will also want to find, e.g., averages for the last year,
3 months, etc., as well.

Thanks,
dman

find row number of last not emty cell in column A

=ROW(INDEX(A:A,LARGE(IF(A1:A65000<"",ROW(A1:A6500 0)),1),))

paste in and then CTRL+SHIFT+ENTER

Easiest way to find the last row number in the most primitive situation is to
count nonblank cells in the range where the data is going to be, column A in
this case. Put this in a cell in any column other than A (otherwise you get
a circular reference error).
=COUNTA(A:A)
By " the most primitive situation " I mean one where there aren't any blank
cells in the middle of a list. COUNTA() counts non-blank cells.

Look at grouping and subtotal functions to assist with your averages issues.

"Dallman Ross" wrote:

Hi,

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

I have a feeling some will say it's not necessary to find the
bottom row number. But I still would like to know how to find it.
Oh, and I will also want to find, e.g., averages for the last year,
3 months, etc., as well.

Thanks,
dman

Try:


=SUMPRODUCT(--(MONTH(A1:A5)=5),(E1:E5 ))/SUMPRODUCT(--(MONTH(A1:A5)=5))

HTH

"Dallman Ross" wrote:

Hi,

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

I have a feeling some will say it's not necessary to find the
bottom row number. But I still would like to know how to find it.
Oh, and I will also want to find, e.g., averages for the last year,
3 months, etc., as well.

Thanks,
dman

I think you may find this assistance article of value to you:
http://office.microsoft.com/en-us/as...097981033.aspx

"Dallman Ross" wrote:

Hi,

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

I have a feeling some will say it's not necessary to find the
bottom row number. But I still would like to know how to find it.
Oh, and I will also want to find, e.g., averages for the last year,
3 months, etc., as well.

Thanks,
dman

In , Dallman Ross <dman@localhost. spake thusly:

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

Okay, I've made a good bit of progress (have also been reading
some articles here).

for example,

=SUMIF($A:$A,"" &TODAY()-60,G:G)/COUNTIF($A:$A,"" &TODAY()-60)

gives me an average of col. G for the last 60 days' data.

Okay, but how do I find the average of data from 31 days ago
through 60 days ago, i.e., the previous month's alone?

dman

How about just designating 2 cells where you enter a starting date and
ending date for your calculations?

With G1 = start
And G2 = end
Try this to average closing if closing was in Column E and dates are in
Column A:
(Assume all dates are *true* XL recognized dates.)

=SUMPRODUCT((A2:A100=G1)*(A2:A100<=G2)*E2:E100)/SUMPRODUCT((A2:A100=G1)*(A
2:A100<=G2))

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"Dallman Ross" <dman@localhost. wrote in message
...
In , Dallman Ross <dman@localhost. spake
thusly:

I'm interested in averaging some column ranges. I'm stuck
because I don't see an easy way to find the row number of the
last row with data in it.

For example, assume some stock data:

A B C D E F
Date Open Hi Lo Close Vol
1 4/23 20.00 20.41 19.82 19.89 1,000,000
2 4/28 19.81 21.01 19.81 20.92 1,111,111
3 4/30 21.11 21.81 21.11 21.80 1,010,101
4 5/13 24.14 24.26 24.01 24.02 2,322,122
5 5/19 22.22 23.33 22.13 22.51 1,732,222

And so on. Not every date is filled. (Only trading
days, but let's not even assume that much.) So
suppose I want to average May's data for the close.
I need to know that the data ends on Row 5. Then
I need to average only entries that show May in Col. A.
can someone help me do this?

Okay, I've made a good bit of progress (have also been reading
some articles here).

for example,

=SUMIF($A:$A,"" &TODAY()-60,G:G)/COUNTIF($A:$A,"" &TODAY()-60)

gives me an average of col. G for the last 60 days' data.

Okay, but how do I find the average of data from 31 days ago
through 60 days ago, i.e., the previous month's alone?

dman

In , RagDyeR
spake thusly:

How about just designating 2 cells where you enter a starting
date and ending date for your calculations?

With G1 = start
And G2 = end
Try this to average closing if closing was in Column E and dates are in
Column A:
(Assume all dates are *true* XL recognized dates.)

=SUMPRODUCT((A2:A100=G1)*(A2:A100<=G2)*E2:E100)/SUMPRODUCT((A2:A100=G1)*(A
2:A100<=G2))

Thanks, interesting. But I'm afraid constantly updating cells to
contain the desired start and end dates is out. The data and dates
are updated dynamically as often as daily. (Also, it disturbs my
sense of aesthetics.)

I'll play some with SUMPRODUCT, though.

dman

In , Dallman Ross <dman@localhost.
spake thusly:

=SUMIF($A:$A,"" &TODAY()-60,G:G)/COUNTIF($A:$A,"" &TODAY()-60)

gives me an average of col. G for the last 60 days' data.

Okay, but how do I find the average of data from 31 days ago
through 60 days ago, i.e., the previous month's alone?

This seems to be the ticket:

=(SUMIF($A:$A,"<" &TODAY()-60,G:G)-SUMIF($A:$A,"<=" &TODAY()-90,G:G))/(COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<=" &TODAY()-90))


My only remaining question is, do I have the "<=" and the "<" set right
to give me a 30-day period starting 60 days ago? It kind of makes my
brain hurt thinking about that too hard. However, as a cross-check
I averaged last-30, prior-30, and the 30 before prior-30, and they
do *not* quite match. E.g., 49.389% for last-90 as opposed to
48.985% for the average of the three 30-day periods. So something
isn't perfect.

dman

You would *NOT* have to *constantly* update the start and end date cells!

Start date (G1) could just as easily be:
=TODAY()-30
as well as
6/1/06
Which would *automatically* increment (update) with each passing day.
--

Regards,

RD
----------------------------------------------------------------------------
-------------------
Please keep all correspondence within the Group, so all may benefit !
----------------------------------------------------------------------------
-------------------


"Dallman Ross" <dman@localhost. wrote in message
...
In , RagDyeR
spake thusly:

How about just designating 2 cells where you enter a starting
date and ending date for your calculations?

With G1 = start
And G2 = end
Try this to average closing if closing was in Column E and dates are in
Column A:
(Assume all dates are *true* XL recognized dates.)


=SUMPRODUCT((A2:A100=G1)*(A2:A100<=G2)*E2:E100)/SUMPRODUCT((A2:A100=G1)*(A
2:A100<=G2))

Thanks, interesting. But I'm afraid constantly updating cells to
contain the desired start and end dates is out. The data and dates
are updated dynamically as often as daily. (Also, it disturbs my
sense of aesthetics.)

I'll play some with SUMPRODUCT, though.

dman

=COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<" &TODAY()-90)

gives an answer of 30

=COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<=" &TODAY()-90)

gives an answer of 29



"Dallman Ross" wrote:

In , Dallman Ross <dman@localhost.
spake thusly:

=SUMIF($A:$A,"" &TODAY()-60,G:G)/COUNTIF($A:$A,"" &TODAY()-60)

gives me an average of col. G for the last 60 days' data.

Okay, but how do I find the average of data from 31 days ago
through 60 days ago, i.e., the previous month's alone?

This seems to be the ticket:

=(SUMIF($A:$A,"<" &TODAY()-60,G:G)-SUMIF($A:$A,"<=" &TODAY()-90,G:G))/(COUNTIF($A:$A,"<" &TODAY()-60)-COUNTIF($A:$A,"<=" &TODAY()-90))


My only remaining question is, do I have the "<=" and the "<" set right
to give me a 30-day period starting 60 days ago? It kind of makes my
brain hurt thinking about that too hard. However, as a cross-check
I averaged last-30, prior-30, and the 30 before prior-30, and they
do *not* quite match. E.g., 49.389% for last-90 as opposed to
48.985% for the average of the three 30-day periods. So something
isn't perfect.

dman