LinkBack URL
About LinkBacks
The formular =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) only works once and then only gives me zeros, How can I change it so that it works on 4200 rows?
The formular =IF(COUNTIF(B$2:B2,0)>=1,0,COUNTIF(A$2:A2,0)) only works once and then only gives me zeros, How can I change it so that it works on 4200 rows?
Last edited by Dussy; 05-26-2017 at 07:25 AM.
What are you trying to achieve?
Please do add reputation where you see fit, it's nice to be nice and we all enjoy a pat on the back
Please also mark your thread as solved once it has been.
As you can see there is the row for event A and Event B. I would like to tell excel to start a counter if it sees a 0 in row (Event A) and carry on counting until it sees a 0 in row (Event B). If it starts the counter it should ignore any 0 in row (Event A) until it recognizes a 0 in row (event B) and stop counting upon the 0 in row (event B) and then look in row (event A) for another 0. However as you can see a21 has a zero and the counter shows a zero and does not carry on counting until it finds the next 0 in b24. so it basically only works once and not many times over if i want to pull the formula down on 4200 rows
Last edited by Dussy; 05-26-2017 at 08:18 AM.
It would be helpful if you could show in column D what you are hoping to achieve. Is it a series of 1's, then a series of 2's, then 3's etc. governed by zero in column A to start a new number and zero in B to stop the numbering?
Pete
I have a list of 4200 RSI reading (RSI is a technical indicator used in trading) and there is an upper boundary of 90 and a lower boundary of 10, I would like to find the average time it takes for the RSI to do one full oscillation meaning being above 90 going down to 10 and back up to 90.
In the previous screenshot the 0 in column A denotes when the RSI is above 90 and column B When the RSI is Below 10. Which would indicate half an oscillation then I thought I would reverse the solution and then make a counter find out how long it takes per oscillation find the total number of oscillations and then work out the average time period and thus frequency.
I have attached a copy of the spreadsheet maybe you guys have another approach to solving this problem.
In T3 then drag down:
=SUMPRODUCT(--($R$2:R3+$S$2:S3=1))
Are you using lot of helper columns to refer to RSI (column H)?
If yes, I will try to use direct solution to RSI idexes to avoid helpers.
Quang PT
Hey Thanks for your quick response. Yes I am because I tried to find out other variables aswell. I inserted your formular and it works to the extent that it counts one more when ever there is a zero. However when there is a 0 in column R (Event A) it starts counting and when there is a second zero in R it counts one more whereas it shoulndt because it should ignore this 0 until it reaches a in clomn S (event B) as it would other wise not be a full oscilation. As RSI would be above 90 below 90 for 3 days and then above 90 again however not below 10 which would be half and oscilation. How can I get excel to count upon the first 0,lets call it 0a in R then ignore the following zeros until it reaches a 0 in S (0c) then stop counting until it finds the next 0 in R and then to count again until it reaches a 0 in S regardless of any 0 in R between 0a and 0c.
If you look in the spreadsheet column H Shows the RSI Value, How could you count the number of rows it takes (each row 1 day) for the RSI to go from above 90 to below 10 and back up to 90?
In AB9 (or first cell where H > 90) put "A"
in AB10 (or cell following Above)
=IF(AND($H10<10,LOOKUP(2,1/($AB$2:$AB9<>""),$AB$2:$AB9)="A"),"B",IF(AND($H10>90,LOOKUP(2,1/($AB$2:$AB9<>""),$AB$2:$AB9)="B"),"A",""))
and copy down
you will get pattern of "A", "B", "A" i.e >90, then <10 , > 90
in AC2
=IF(AB2<>"",COUNTIF($AB$2:AB2,AB2),"")
will count number of "A"s and "B"s
Hopefully this will help in getting your results
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Register To Reply
Bookmarks