# How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it further?

1. ## How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it further?

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?  Register To Reply

2. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

What are you trying to achieve?  Register To Reply

3. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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  Register To Reply

4. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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  Register To Reply

5. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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.  Register To Reply

6. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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.  Register To Reply

7. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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.  Register To Reply

8. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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?  Register To Reply

9. ## Re: How can I make =IF(COUNTIF(B\$2:B2,0)>=1,0,COUNTIF(A\$2:A2,0)) workwhen copying it furth

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  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 