Finding the most recent month's (or whatever) data

  • Thread starter Thread starter Dallman Ross
  • Start date Start date
D

Dallman Ross

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:A65000)),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.
 
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?
Click to expand...

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.> spake said:
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?
Click to expand...

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
 
RagDyeR said:
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))
Click to expand...

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
 
Dallman Ross <dman@localhost.> said:
=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?
Click to expand...

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 !
----------------------------------------------------------------------------
-------------------


RagDyeR said:
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.)
Click to expand...
=SUMPRODUCT((A2:A100>=G1)*(A2:A100 said:
2:A100<=G2))
Click to expand...

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
 
Back
Top