largest group of non zeros
 

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?

largest group of non zeros
 

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?

largest group of non zeros
 

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?

largest group of non zeros
 

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?

largest group of non zeros
 

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

largest group of non zeros
 

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?

largest group of non zeros
 

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

largest group of non zeros
 

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?

largest group of non zeros
 

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

largest group of non zeros
 

now all i have to do is make it work for upto 50000 entries but thanks for
the start

"Max" wrote:

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

largest group of non zeros
 

You're welcome.
Good luck !
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
now all i have to do is make it work for upto 50000 entries but thanks for
the start

largest group of non zeros
 

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

"Lost" wrote:

now all i have to do is make it work for upto 50000 entries but thanks for
the start

"Max" wrote:

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

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

largest group of non zeros
 

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
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Lost" wrote in message
...
seems to have an error but i'm not sure where....... reports exceed values
when there arn't

largest group of non zeros
 

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

Use this version instead in E1, copied down:
=IF(D1="","",IF(SUM(OFFSET(D1,,,INT($G$2/AVERAGE(D:D))+1))$G$2,"Yes",""))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---