I have two sets of numbers..... In column B I have measured values. In column
A I have the times for which these valus occur. I need to determine whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

Plz Help?

C an you give an example of the data and the results that you expect?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lost" wrote in message
...
I have two sets of numbers..... In column B I have measured values. In
column
A I have the times for which these valus occur. I need to determine
whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

Plz Help?

2:46:08 0.2
2:46:23 0
2:46:38 0.2
2:46:53 0.1
2:47:08 0.2
2:47:23 0.2
2:47:38 0.3
2:47:53 0.2
2:48:08 0
2:48:23 0.2
2:48:38 0.1
2:48:53 0.2
2:49:08 0.1
2:49:23 0.1


in this example my values are too low for the actual limits so lets say over
..1 for a duration of 30 secs

"Bob Phillips" wrote:

C an you give an example of the data and the results that you expect?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Lost" wrote in message
...
I have two sets of numbers..... In column B I have measured values. In
column
A I have the times for which these valus occur. I need to determine
whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

Plz Help?

Venturing some thoughts at this ..

Assume data in cols A and B, from row1 down,
"Max limit" is entered in F1: 0.1 (say)
"Duration" is entered in F2: 0:00:30 (say, in time format)

Put in C1: =IF(OR(A1="",B1=""),"",IF(B1$F$1,1,""))
Put in D1: =IF(OR(C1="",C2=""),"",IF(SUM(C1:C2)=2,A2-A1,""))
Select C1:D1, copy down

Col C will return the durations between successive times
where the limit was exceeded, if any

Then, with a label placed in E3: "Limit exceeded for duration?"
we could put in F3: =IF(SUM(D:D)F2,"Yes","No")
to provide the monitoring
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
2:46:08 0.2
2:46:23 0
2:46:38 0.2
2:46:53 0.1
2:47:08 0.2
2:47:23 0.2
2:47:38 0.3
2:47:53 0.2
2:48:08 0
2:48:23 0.2
2:48:38 0.1
2:48:53 0.2
2:49:08 0.1
2:49:23 0.1


in this example my values are too low for the actual limits so lets say
over
.1 for a duration of 30 secs

My guidelines are such that if the consecutive counts do not last for the
exceeded time limits the count restarts.......so......

3:17:04 0.1
3:17:19 0.1
3:17:34 0
3:17:49 0
3:18:04 0
3:18:19 0.1
3:18:34 0.1

would not exceed the limits of .1 for more then 45sec as there are zeros
seperating the groups...... would this still adress this?

"Max" wrote:

Venturing some thoughts at this ..

Assume data in cols A and B, from row1 down,
"Max limit" is entered in F1: 0.1 (say)
"Duration" is entered in F2: 0:00:30 (say, in time format)

Put in C1: =IF(OR(A1="",B1=""),"",IF(B1$F$1,1,""))
Put in D1: =IF(OR(C1="",C2=""),"",IF(SUM(C1:C2)=2,A2-A1,""))
Select C1:D1, copy down

Col C will return the durations between successive times
where the limit was exceeded, if any

Then, with a label placed in E3: "Limit exceeded for duration?"
we could put in F3: =IF(SUM(D:D)F2,"Yes","No")
to provide the monitoring
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
2:46:08 0.2
2:46:23 0
2:46:38 0.2
2:46:53 0.1
2:47:08 0.2
2:47:23 0.2
2:47:38 0.3
2:47:53 0.2
2:48:08 0
2:48:23 0.2
2:48:38 0.1
2:48:53 0.2
2:49:08 0.1
2:49:23 0.1


in this example my values are too low for the actual limits so lets say
over
.1 for a duration of 30 secs

Tinker with this slightly revised set-up ..

Sample construct available at:
http://cjoint.com/?doiA5opb4J
Monitoring Consecutive Limit Breaches.xls

Assume source data in A1:B14
Max limit in G1: 0.1
Duration in G2: 0:00:30

In C1: =IF(OR(A1="",B1=""),"",IF(B1$G$1,1,""))
In D1: =IF(OR(C1="",C2=""),"",IF(SUM(C1:C2)=2,A2-A1,""))
In E1: =IF(D1="","",IF(SUM(D1:$D$14)$G$2,"Yes",""))
Select C1:E1, copy down to E14

Then, in G1: =IF(COUNTIF(E1:E14,"Yes")0,"Yes","No")
returns the status whether "Limit exceeded for duration?"

Adapt to suit your actual data range, test it out ..
(Lightly tested here, seems ok)
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
My guidelines are such that if the consecutive counts do not last for the
exceeded time limits the count restarts.......so......

3:17:04 0.1
3:17:19 0.1
3:17:34 0
3:17:49 0
3:18:04 0
3:18:19 0.1
3:18:34 0.1

would not exceed the limits of .1 for more then 45sec as there are zeros
seperating the groups...... would this still adress this?

Can you post a sample set of data in cols A and B in plain text here and
where / how the expected results should be / look like ?
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
I have two sets of numbers..... In column B I have measured values. In
column
A I have the times for which these valus occur. I need to determine
whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

Plz Help?

Lost,

If you have a consistent time period for your data points, simply use

=IF((COUNTIF(B:B, "5")*TimePeriodBasis)15,"Bad","OK")

So, if your data points are taken every 30 seconds:

=IF((COUNTIF(B:B, "5")*0.5)15,"Bad","OK")

For every 2 minutes:
=IF((COUNTIF(B:B, "5")*2)15,"Bad","OK")

HTH,
Bernie
MS Excel MVP


"Lost" wrote in message
...
I have two sets of numbers..... In column B I have measured values. In column
A I have the times for which these valus occur. I need to determine whether
the measured values in B exceeded the max limit for more then a given time
(ie do not exceed a value of five for more then 15mins)

Plz Help?