Excel 97 : negative value detection 1

[beec]

I have a range of data in my Excel 97 spreadsheet existing of a column with dates and time and a second column with pressure values. This data has to be analysed on when it passes zero.
What I have to do is to count every time the pressure value comes below zero and how long it takes to rise above zero again.
Can someone give me some help with this ?

tnx.
Bert.

 

[xlbo]

How do you want this shown ??
presumably you would want a summary table with date / time when pressure < 0 and length of time to get > 0 all on a single row in another sheet. Then the next date / time where pressure < 0 on the row below etc tec ??

Rgds
Geoff
&quot;Some cause happiness wherever they go; others whenever they go.&quot;
-Oscar Wilde

 

[Zathras]

I created a spreadsheet this way which I think is what you are looking for:

A1: 'Time
B1: 'Pressure
C1: 'Go Negative
D1: 'When
E1: 'How Long
A2: 4/16/2003  8:23:50 AM
B2: 2
C2: =IF(AND(B2<0,B1>=0),1,&quot;&quot;)
D2: =IF(C2=1,A2,D1)
E2: =IF(AND(B2>=0,B1<0),A2-D2,&quot;&quot;)
A3: 4/16/2003  8:28:09 AM
B3: 3
A4: 4/16/03 8:32
B4: 2
A5: 4/16/03 8:36
B5: -1
A6: 4/16/03 8:41
B6: -2
A7: 4/16/03 8:45
B7: -3
A8: 4/16/03 8:49
B8: -1
A9: 4/16/03 8:54
B9: 0
A10: 4/16/03 8:58
B1: 2
A11: 4/16/03 9:02
B11: 5
A12:4/16/03 9:07
B12: 5
A13: 4/16/03 9:11
B13: -1
A14: 4/16/03 9:15
B14: 6
A15: 4/16/03 9:19
B15: 3
A16: 4/16/03 9:24
B16: -2
A17: 4/16/03 9:28
B17: -2
A18: 4/16/03 9:32
B18: 6
A19: 4/16/03 9:37
B19: 6

Copy the formulas from C2:E2 to C3 thru whatever
Hide column D (optional)
Format Column E as mm:ss
Cells C5, C13 and C16 should show 1, the rest of col C blank
Cell E9 should show 17:17
Cell E14 should show 04:19
Cell E18 should show 08:38

Put SUM( ) formulas for columns C and E wherever you need counts and total time. (3 and 30:14)

 

[beec]

Zathras solution works, THX !
What I want to do now is to generate a summary table wich tells me when the pressure went below 0 and for how long like Geoff suggests. But how??

Bert.

 

[Zathras]

One simple way would be to set an auto-filter on the &quot;How Long&quot; column (col E in my example) and select NonBlanks. Then copy columns D and E and paste into a new sheet.

 

[beec]

OK Zathras, that works also fine, but I would like to automate this with a macro or something because I have about 35 worksheets with pressure data in it which have to be analysed as described before.
I can create al the worksheets with the formulas in it from your first solution and than insert the pressure data, but how can I automaticly create my summary table which gives an overview per sheet (= room) from the negative pressures and duration time ?
Maybe I'm asking much but I'm not familiar with Excel's possibilities.

THX for your reply,
Bert.

 

[Zathras]

Use the macro recorder. It's a great way to get started with VBA.

Open a new workbook to contain the summary information. Then one by one you can open each of the 35 sheets and run the macro.

Here is the (slightly modified) output of the macro recording I made doing one sheet:

Option Explicit

Sub Macro1()

'
' Macro1 Macro
' Macro recorded 4/17/03 by Zathras
'

'

    Columns("E:E").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:=&quot;<>&quot;
    Columns(&quot;D:E&quot;).Select
    Selection.Copy
    Windows(&quot;Book2&quot;).Activate
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range(&quot;A1&quot;).Select
    Selection.End(xlDown).Select
    Selection.Offset(1, 0).Select
End Sub

I modified the last statement to select the next blank row down (the macro recorder gave a fixed address which wouldn't work for the multiple sheet scenario.
You may want to copy more that columns D:E (e.g. to capture room number). If so, just change the code (or re-record the macro).