Cumulative Run Counter when Event Changes
Posted by Richard L. Craven on October 13, 2001 4:13 PM
Dear Mr. Excel,
I would like to add a function to my spreadsheets that is derived from the outcome: 1 or -1. The outcome is a daily result. However, I would like to count the uninterrupted runs in the next column. For example: if there is a run of three consecutive 1's (positive one's) the next column should output 1,2,3. Likewise, six consecutive -1's (negative one's) should result in -1,-2,-3,-4,-5,-6. My problem has been with the change from a positive one to a negative one (and visa versa).
I build a great number of spreadsheets of this design and I've resorted to manual input, I embarrassed to say. I would greatly appreciate any input or hints you can offer on this. I just can't come up with the solution. I've attached a sample file (spls-1.xls) for your consideration. The worksheet columns in question are: "T" and "U".
Very truly yours,
Richard L. Craven
Posted by Aladin Akyurek on October 13, 2001 4:45 PM
Richard,
I'll assume that the daily outcomes are in A from A1 on.
In B1 enter: =IF(LEN(A1),IF(A1>0,COUNTIF($A$1:A1,">0"),-COUNTIF($A$1:A1,"<0")),"")
Copy down as needed.
Is this what you are looking for?
Aladin
============= I would like to add a function to my spreadsheets that is derived from the outcome: 1 or -1. The outcome is a daily result. However, I would like to count the uninterrupted runs in the next column. For example: if there is a run of three consecutive 1's (positive one's) the next column should output 1,2,3. Likewise, six consecutive -1's (negative one's) should result in -1,-2,-3,-4,-5,-6. My problem has been with the change from a positive one to a negative one (and visa versa). I build a great number of spreadsheets of this design and I've resorted to manual input, I embarrassed to say. I would greatly appreciate any input or hints you can offer on this. I just can't come up with the solution. I've attached a sample file (spls-1.xls) for your consideration. The worksheet columns in question are: "T" and "U". Very truly yours, Richard L. Craven
Posted by Richard L. Craven on October 13, 2001 6:25 PM
Aladin,
Thanks very much for your help with my question. Here is the result:
run run-alt
-1 -1
-1 -2
1 1
1 2
1 3
-1 -3
1 4
-1 -4
1 5
-1 -5
1 6
-1 -6
1 7
-1 -7
1 8
-1 -8
-1 -9
-1 -10
-1 -11
The "run" column cells contain the formula
=IF(K3="UP",1,-1)
Column K consists of cells input with text "UP" or "DN".
The first five rows of column "run-alt" are correct.
The following is what I hoped to get:
run run-alt
-1 -1
-1 -2
1 1
1 2
1 3
-1 -1
1 1
-1 -1
1 1
-1 -1
1 1
-1 -1
1 1
-1 -1
1 1
-1 -1
-1 -2
-1 -3
-1 -4
Any thoughts? Thanks again for all your help.
Richard
RLC@ausable.com
: : I would like to add a function to my spreadsheets that is derived from the outcome: 1 or -1. The outcome is a daily result. However, I would like to count the uninterrupted runs in the next column. For example: if there is a run of three consecutive 1's (positive one's) the next column should output 1,2,3. Likewise, six consecutive -1's (negative one's) should result in -1,-2,-3,-4,-5,-6. My problem has been with the change from a positive one to a negative one (and visa versa). : : I build a great number of spreadsheets of this design and I've resorted to manual input, I embarrassed to say. I would greatly appreciate any input or hints you can offer on this. I just can't come up with the solution. I've attached a sample file (spls-1.xls) for your consideration. The worksheet columns in question are: "T" and "U". : : Very truly yours, : : Richard L. Craven : : :
Posted by Aladin Akyurek on October 14, 2001 12:41 AM
The following is what I hoped to get: run run-alt -1 -2 1 1 1 2 1 3 -1 -1 1 1 -1 -1 1 1 -1 -1 1 1 -1 -1 1 1 -1 -1 1 1 -1 -1 -1 -2 -1 -3 -1 -4
Richard,
Now I understand what you want given your example with expected results.
In B1 enter: =IF(LEN(A1),IF(A1>0,1,-1),"")
In B2 enter: =IF(LEN(A2),IF(A2>0,IF(A1>1,B1+1,1),IF(A1<0,B1-1,-1)),"")
Copy down the formula in B2 as far as needed.
Aladin
Posted by Richard L. Craven on October 14, 2001 12:15 PM
Aladin,
The formulas you provided:
In B1 enter: =IF(LEN(A1),IF(A1>0,1,-1),"") In B2 enter: =IF(LEN(A2),IF(A2>0,IF(A1>1,B1+1,1),IF(A1<0,B1-1,-1)),"")
work perfectly with a run of consequetive negative ones (-1). If column A contains -1,-1,-1,-1 column B yields -1,-2,-3,-4. However, a run of positive ones 1,1,1,1,1 yields 1,1,1,1,1. Negative 1's tally, positive 1's do not.
Again, my thanks for you assistance.
Richard
: The following is what I hoped to get
Posted by Aladin Akyurek on October 14, 2001 12:32 PM
In B1 enter: =IF(LEN(A1),IF(A1>0,1,-1),"") : In B2 enter: =IF(LEN(A2),IF(A2>0,IF(A1>1,B1+1,1),IF(A1<0,B1-1,-1)),"") work perfectly with a run of consequetive negative ones (-1). If column A contains -1,-1,-1,-1 column B yields -1,-2,-3,-4. However, a run of positive ones 1,1,1,1,1 yields 1,1,1,1,1. Negative 1's tally, positive 1's do not.
Rich, No wonder. The formula in B2 must be:
=IF(LEN(A2),IF(A2>0,IF(A1>0,B1+1,1),IF(A1 < 0,B1-1,-1)),"")
Sorry about that.
Aladin
Posted by Richard L. Craven on October 14, 2001 4:14 PM
Aladin,
Your modified formula for B2:
=IF(LEN(A2),IF(A2>0,IF(A1>0,B1+1,1),IF(A1 < 0,B1-1,-1)),"")
works beautifully. Thank you sir!
Best Wishes,
Rich Craven
The formulas you provided
