# Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurred

1. ## Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurred

Hi,

I am collecting data that is supposed to be within a certain range, lets say 2-8, and I am collecting 1 data point per minute for a long period of time. What I am hoping to do is check to make sure that every data point is within the range, and if it is not within the range for five consecutive minutes have that time be added to a list of out of specification times in a single cell.

The sheet setup is fairly simple A1 would be a column header "TIME" and B1 would be a column header "DATA" and then A2:Axx and B2:Bxx would be the corresponding data. Then in some other cell, let's say D2 it would just be keeping track of any consecutive 5 data points that were not in the 2-8 range of acceptable data. The amount out of spec or any other information is not important, I simply need to know all the times it occurs.

I have attached a sample data sheet which has 30 minutes of sample data, but I will be collecting tens of thousands of data points so combing through the data to find the runs of out of spec data will be incredibly time consuming. There can be extra columns used if necessary, but they have to be hidden, and the more direct the better as I have to validate all of the formulas in the sheet.

I have searched and found formulas using a combination of MAX( and FREQUENCY( to report back if the number of consecutive out of specification cells is >= 5, but along with that I need to know the corresponding time of when that 5+ consecutive data points have occurred.

Any help is appreciated. Thanks so much!

2. ## Re: Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurr

Do you need to have the results in real time? So as the 5th, 6th or 7th minute-sample are out of spec, the time-range list is appended.

or

Can you just collect the data over a long period then run a macro to report the results?

3. ## Re: Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurr

I do not need the results in real time, however, I will be doing multiple sets of most likely 24 hour runs and once I create the spread sheet to handle the data, I have to validate it which requires checking by hand that all of the formulas are correct and produce the expected results. I am not sure how that validation process works with macros, so if there was a way with sheet formulas it would be the most ideal, but at this point I would take any solution. I simply am not too familiar with using macros, but I could probably look through one and figure it out.

Beggars can't be choosers. Thanks so much!

4. ## Re: Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurr

Take a look at your modified workbook attached.

5. ## Re: Finding Data Out of Range for 5 Consecutive Readings then Reporting the Time it Occurr

Dennis, Thanks so much for all this. I looked at the sheet, and it works great for the most part. I was considering doing something similar and in the attached sheet I changed the Out of spec(OOS) column to display the time the OOS occurred instead of the duration of the OOS,

However, what I would actually like to do is to take that column of times where the 5 minute OOSs occurred and concatenate them into a single cell that would show up on another "final report" type sheet. For simplicity sake it can remain on one sheet for the example and lets say cell G2 lists all the times where it was OOS so it would display "0:07, 0:08, 0:24".

I know how to concatenate in the basic sense (Column A is first names , Column B is last names, concatenate in Column C to get a First and Last Name), but I do not know if there is a way to say, For all cells in Column F,If the Cell has non " " data, concatenate it in cell G2 without using a macro.

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