Share via

Counting the Number of times there are more than 5 consecutive cells less than a certain number-Excel 2010

Anonymous
2016-06-02T13:09:13+00:00

Hello all, I am very new to excel and am required to use it for a certain project and I don't know where to begin. I REALLY NEED HELP!

I have a gigantic amount of data, 30 columns by over 46,000 rows. The row labels are the date and time in 1 minute intervals while the columns labels represent the point of origin of data. I have highlighted all the cells that are less that 0.05 in the spreadsheet. However I need to find out how many times in the spreadsheet there were 5 or more consecutive cells that were less than 0.05 down a column.

For example here is some data in one column...

0.049
0.048
0.049
0.048
0.049
0.050
0.050
0.050
0.050
0.049
0.049
0.049
0.051
0.051
0.049
0.050
0.050
0.048
0.049
0.049
0.049
0.048
0.050

I want to find out how many different times there were 5 or more consecutive cells that had a value <0.05. In this case it would be 2 times.

I have no idea how to do this efficiently in excel since I have SO MUCH data to go through. There is not way I can go through it by hand, it would take me months, so if there were some equations that could work for this I would really appreciate the help.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2016-06-02T14:27:33+00:00

Is it an Excel file or a Word document?  If you have a place that you can share the file (like Microsoft OneDrive), I will download it and figure out what needs to be done.

Or, AK11 use this formula

=IF(AND(COUNTIF(C7:C11,"<.05")=5,OR(C12>=0.05,C12="")),1,"")

Copy down to AK12:AK44646.  In AK9, use the formula

=SUM(AK11:AK44646)

Then copy AK9:AK44646  across to columns AL to BR.

Was this answer helpful?

0 comments

26 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2016-06-06T17:42:07+00:00

    You need to use an array formula, (array-enter using Ctrl-Shift-Enter instead of just enter) with this formula in AK11:

    If you just want the count of minutes:

    =IF(AND(COUNTIF(C7:C11,"<.05")=5,OR(C12>=0.05,C12="")),(ROW()-MAX(IF(C$7:C11>0.05,ROW(C$7:C11),7))+1),"")

    If you want times instead, you could use this

    =IF(AND(COUNTIF(C7:C11,"<.05")=5,OR(C12>=0.05,C12="")),(ROW()-MAX(IF(C$7:C11>0.05,ROW(C$7:C11),7))+1)/1440,"")

    Format the cell for time, and change the /1440 if the actual time difference is not one minute (there are 1440 minutes in one day - so use the number of intervals per day) and copy down just like you did before.  

    Note that either of these will take a lot longer to calculate, since there is no upper limit on the amount of time that the instances lasted, so you will need to check thousands of values each time instead of just 5 for some of the cells, at least.

    Alternatively, if you just need the total time spent below the threshold for any instance lasting longer than 5 minutes, this would be much faster:

    =IF(COUNTIF(C7:C11,"<.05")=5,1,0)

    Then just sum to get the total time

    Was this answer helpful?

    0 comments
  2. Anonymous
    2016-06-06T12:44:33+00:00

    Thank you so much. Worked like a charm! Now I have another question for you.

    So say in the first column it came up with 17 instances that the data was less that 0.05 for more than 5 minutes (or cells). Is there any way to identify how long each of those instances were. For example, the first instance in that column was 6 minutes long, the second 11 minutes, so on and so forth?

    Was this answer helpful?

    0 comments
  3. Anonymous
    2016-06-02T14:13:48+00:00

    Thanks for the response, but I am still struggling. Excel is really not my thing. I tried what you suggested but it did not work for me and I think it is because I don't really understand it.

    I'm going to give you the actual cells of where my data begins and ends so maybe you can repeat what you said to me with those values because I am actually very confused.

    So my data begins in cell CD7 (this whole column is merged, I don't know if that will make a difference or not, or if I should try to unmerge them?) and my data ends in cell AJ44646. So my columns go from C to AJ and my rows go from 7 to 44646.

    There is writing in all of the cells above row 7 because it is an office doc.

    I hope this helps you help me a little better. Thanks!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2016-06-02T13:46:03+00:00

    Let's assume that your numbers start in B2. In the first empty column in row 6, (like, say, AF6) enter the formula

    =IF(AND(COUNTIF(B2:B6,"<.05")=5,OR(B7>=0.05,B7="")),1,"")

    and copy down to match your column B.  Then in AF1, enter

    =SUM(AF6:AF50000)

    Then copy AF across for 29 more columns, and use

    =SUM(1:1) 

    to get the total, and you're done.

    The only question is if you need to wrap the data - from the bottom of one column to the top of the next?

    Was this answer helpful?

    0 comments