Summation of last (DD) days

[Guest]

i am tring to create a simple Nasdaq Breadth rati
where the sum of the advancing volume for the past 8 days(including today
is divided by the declining volume for the past 8 days (includiny today
for anyone interested in the market i would buy when ADR=1.5 and sell when ADR=0.8
this would be updated daily from yahoo
DATE AV DATE D
20040331 674931876 20040331 111957091
20040401 1322679760 20040401 44943118

i have the formulae but need to transfer into excel languag
Data
AV = Nasdaq Advancing Volum
DV = Nasdaq Declining Volum
Formulae
SUM_AV = Summation of last (8) days (including current day) of A
SUM_DV = Summation of last (8) days (including current day) of D
ADR = SUM_UV / SUM_D

TIA, cirius3000

 

[Frank Kabel]

Hi
if your dates are stored as real dates (and not as text values) in
column a and C use the following formulas
=SUMIF(A1:A1000,">=" & TODAY()-7,B1:B1000)

and
=SUMIF(C1:C1000,">=" & TODAY()-7,D1:d1000)

 

[Vasant Nanavati]

A bit crude but fairly simple to implement:

Let's say your AV row ends at B19. In cell B20, place the following formula
for the total:

=SUM(OFFSET(B20,-1,0):OFFSET(B20,-8,0))

This will keep a running total of the last 8 rows. Every day, you can insert
a new row above the total and the formula will adjust itself accordingly.

You can do the same thing with the DV column and calculate the ratio.

 

[Vasant Nanavati]

Hi Frank:

This may not work if weekends and holidays are involved, which probably
would be the case with stock trading.

Regards,

Vasant.

 

[Frank Kabel]

Hi
good point. Another way would be to use the following function:
=SUMPRODUCT(--(A1:A1000>=WORKDAY(TODAY(),-7,list_of_holidays)),B1:B1000
)

and
=SUMPRODUCT(--(C1:C1000>=WORKDAY(TODAY(),-7,list_of_holidays)),D11000
)