largest group of non zeros

L

Lost

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?
 
B

Bob Phillips

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)
 
M

Max

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 ?
 
L

Lost

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
 
M

Max

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
 
B

Bernie Deitrick

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
 
L

Lost

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?
 
M

Max

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)
 
M

Max

Oops, think a slight tweak is needed to the formula :
In E1: =IF(D1="","",IF(SUM(D1:$D$14)>$G$2,"Yes",""))
Click to expand...

Change it to:
In E1: =IF(D1="","",IF(SUM(OFFSET(D1,,,3))>$G$2,"Yes",""))
then copy E1 down as before

Corrected sample at:
http://cjoint.com/?doiUXGBq2b
Monitoring Consecutive Limit Breaches_Revised.xls
 
L

Lost

seems to have an error but i'm not sure where....... reports exceed values
when there arn't
 
M

Max

Try this revision to the formula in col E

In E1:

=IF(D1="","",IF(SUM(OFFSET(D1,,,$G$2/AVERAGE(D:D)+1))>$G$2,"Yes",""))

Copy E1 down as before
 
M

Max

A further revision to:
=IF(D1="","",IF(SUM(OFFSET(D1,,,$G$2/AVERAGE(D:D)+1))>$G$2,"Yes",""))
Click to expand...

Use this version instead in E1, copied down:
=IF(D1="","",IF(SUM(OFFSET(D1,,,INT($G$2/AVERAGE(D:D))+1))>$G$2,"Yes",""))
 
Top