Re: Finding the most recent month's (or whatever) data
- From: "RagDyeR" <ragdyer@xxxxxxxxxxxxx>
- Date: Sat, 1 Jul 2006 10:15:27 -0700
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
news:e8688e$3to$1@xxxxxxxxxxxxxxxxxxxx
In <e85p1k$aks$1@xxxxxxxxxxxxxxxxx>, 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
.
- Follow-Ups:
- Re: Finding the most recent month's (or whatever) data
- From: Dallman Ross
- Re: Finding the most recent month's (or whatever) data
- References:
- Finding the most recent month's (or whatever) data
- From: Dallman Ross
- Re: Finding the most recent month's (or whatever) data
- From: Dallman Ross
- Finding the most recent month's (or whatever) data
- Prev by Date: Re: How do I recover data that somebody pasted over on the wrong page
- Next by Date: Re: How do I get rid of the " read only" on a file I want to make chan
- Previous by thread: Re: Finding the most recent month's (or whatever) data
- Next by thread: Re: Finding the most recent month's (or whatever) data
- Index(es):
Relevant Pages
|
