View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.misc
Herbert Seidenberg Herbert Seidenberg is offline
external usenet poster
 
Posts: 1,180
Default Interval accumulation and resets

Assume your List looks like this:
List Accu SignC
-12 0 0
-5 7 1
24 36 1
35 47 1
-48 0 0
-46 2 0
-40 8 1
-34 14 1
-29 19 1
-46 0 0
40 86 0
50 96 1
18 0 0
-18 -36 1
18 0 0
24 6 1
37 19 1
40 22 1
Add two columns, named Accu and SignC
Into cell 1, 2 of SignC, enter 0, 1 respectively
into cell 3 of SignC enter this formula and copy down
=--(SIGN(List R-List R[-1])=SIGN(List R[-1]-List R[-2]))
Into cell 1 of Accu, enter 0
Into cell 2 of Accu enter and copy down
=IF(SignC,List R-List R[-1]+Accu R[-1],
IF(Accu R[-1]=0,List R-List R[-1]+Accu R[-1],0))
Formulas written in
Tools Options General R1C1
for intuitive readability.