I need a 0 in A1, data starting in A2:B2. Starting date in D1 (calendar one
eg. 12/1/2008), end date in D2.
Formula for a is (array formula, validate with Ctrl+Shift+Enter) :
=SUMPRODUCT((A2:A1001>=D1)*(A2:A1001<=D2)*IF(DAY(A2:A1001)<DAY(A1:A1000),1,0
)*B2:B1001)/SUMPRODUCT((A2:A1001>=D1)*(A2:A1001<=D2)*IF(DAY(A2:A1001)<DAY(A1
:A1000),1,0))
(Array formula for b is :
=SUMPRODUCT((A2:A1001>=D1)*(A2:A1001<=D2)*(IF(DAY(A2:A1001)<DAY(A1:A1000),1,
0)+((IF(DAY(A2:A1001)>15,1,0)*IF(DAY(A1:A1000)<=15,1,0))))*B2:B1001)/SUMPROD
UCT((A2:A1001>=D1)*(A2:A1001<=D2)*(IF(DAY(A2:A1001)<DAY(A1:A1000),1,0)+((IF(
DAY(A2:A1001)>15,1,0)*IF(DAY(A1:A1000)<=15,1,0)))))
(not so easy)
With VBA, there are 3 ways to do it :
1. Use "Evaluate" and the formula.
2. Use autofilter
3. Loop through the data and test each date.
Evaluate is the shortest :
ResultForA =
Evaluate("SUMPRODUCT((A2:A1001>=D1)*(A2:A1001<=D2)*IF(DAY(A2:A1001)<DAY(A1:A
1000),1,0)*B2:B1001)/SUMPRODUCT((A2:A1001>=D1)*(A2:A1001<=D2)*IF(DAY(A2:A100
1)<DAY(A1:A1000),1,0))")
Regards.
Daniel
-----Message d'origine-----
De : excel-macros@googlegroups.com [mailto:excel-macros@googlegroups.com] De
la part de eggman2001
Envoyé : samedi 18 juin 2011 22:40
À : MS EXCEL AND VBA MACROS
Objet : $$Excel-Macros$$ vba beginner question: manipulating array data
I'm just beginning to learn VBA (have experience with Ruby and PHP) and I'm
wondering how you would go about doing the following:
I have paired data in 2 columns - daily historical stock data with the date
in column A and the closing value in column B, and assume I have
10 years worth of data. I'd like to figure out
a) what is the average close price for the earliest day of each month over a
given date range. The earliest day of each month obviously isn't necessarily
the 1st or the 2nd or 3rd of the month since there would be no data for days
that are weekends or national holidays.
b) what is the average close price for the the earliest day of each month
and the earliest day after the 15th of each month (collectively and not
separately) given a date range.
I assume that this is something that is the kind of thing that Excel and VBA
can do fairly easily, as that's why I'm learning it, but haven't quite
gotten that far yet in the book I'm reading.
I appreciate any help with this. Also, if there is some good documentation
for doing these kinds of operations, I'd be much obliged.
--
----------------------------------------------------------------------------
------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip 2. Join our LinkedIN group @
http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and
Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
Like our page on facebook , Just follow below link
http://www.facebook.com/discussexcel
