# Count the gaps between two consecutive data

1. ## Count the gaps between two consecutive data

I have attached spreadsheet. Data in column A and result(gaps) in column c

TRUE
FALSE
FALSE
TRUE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE
FALSE
TRUE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
FALSE
TRUE

I want to count the number of falses between two consecutive true

2
0
5
1
9

And I want the result to be arranged in this manner. where 2 is the number of falses between the first two trues, 0 is the number of falses between the next two trues, and so on.

2. ## Re: Count the gaps between two consecutive data

Still with 2019?

3. ## Re: Count the gaps between two consecutive data

If you have 365, this will work:

=LET(d,FILTER(ROW(A1:A23),A1:A23),t,TAKE(d,-(ROWS(d)-1)),f,TAKE(d,ROWS(d)-1),(t-f)-1)

4. ## Re: Count the gaps between two consecutive data

Thanks but Ma'am it's not working in my excel. I tried to copy that formula and edit range to suit my data but it didn't work. Thanks again

5. ## Re: Count the gaps between two consecutive data

Try this in C1 and copied down.
``Please Login or Register  to view this content.``

6. ## Re: Count the gaps between two consecutive data

Thanks but Ma'am it's not working in my excel.
I asked if you still hd 2019 - you did not respond. Do you?

I also said that the solution would work if you had 365.

7. ## Re: Count the gaps between two consecutive data

In older Excel versions, please try
Formula:
`Please Login or Register  to view this content.`
This formula returns the gaps including the number of FALSE's before the first TRUE and the number of FALSE's after the last TRUE.

8. ## Re: Count the gaps between two consecutive data

I am extremely. I could not see your message at that time. If it's possible, please tell me how to do it in older versions. Thanks again. Have a nice day.

See post #7

10. ## Re: Count the gaps between two consecutive data

Originally Posted by Blacktiger1
I am extremely. I could not see your message at that time.
Well, it was there. It seems that you just aren't reading all of the messages in the thread - please do so.

Then, if that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

11. ## Re: Count the gaps between two consecutive data

Thanks mate. It worked. Here is something which is troubling me again. I am attaching spreadsheet.

what I want to do is - Count a certain pair of numbers in column E. Pair I want to count is 5,0...such that Zero appears immediately after 5 in Column E.

E9 5
E10 0

I want to count this pair in the list given in Column E. ( I have uploaded the spreadsheet)

12. ## Re: Count the gaps between two consecutive data

Sure Ma'am. I will do it.

13. ## Re: Count the gaps between two consecutive data

5,0
``Please Login or Register  to view this content.``
6,0
``Please Login or Register  to view this content.``

##### Users Browsing this Thread

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

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1