Pls help. Excel graph question.

[Supernova_26]

Hello,
Pls don't bash me but I have reached my limits with excel I am looking for some help with some statistical help. I have 3 data columns Date of Measure/ Time of Measure/ Voltage Measurements. I have 31 days of data with samples every 2 min. The time is shown in 24hrs format. Is that a way for me to graph how long each power outage lasted each day. A power outage condition happens when voltage is less then 5 volt.

Example
Date of meas Time of meas. Voltage
12/12 23:22 110
12/12 23:24 0
12/12 23:26 0
12/12 23:28 110

So is that a way for me to graph the date 12/12 and see that I have a power outage that lasted 4 min.
Thank you so much
Toni

 

[thisoldman]

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD="width: 64"]Time[/TD]
[TD="width: 64"]Volts[/TD]
[TD="width: 64"]Is Outage Start[/TD]
[TD="width: 64"]Is Outage
End[/TD]
[TD="width: 64"]Duration[/TD]
[/TR]
[TR]
[TD="align: right"]00:00[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD="align: right"]00:02[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]00:04[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]4[/TD]
[/TR]
[TR]
[TD="align: right"]00:06[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
[TR]
[TD="align: right"]00:08[/TD]
[TD="align: right"]110[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: center"]#N/A[/TD]
[/TR]
</tbody>[/TABLE]

The upper left corner is cell A1.

C2: =IF(B2, 0, 1)
C3 and copied down: =IF(AND(B3<>B2, B3=0), 1, 0)

D2 and copied down: =IF(AND(B2=0, B2<>B3), 1, 0)

E2 and copied down:=IF(C2 = 1, 2 * MATCH(1, D2:D721, 0), NA())

Plot "Duration" as a column chart using "Time" as the horizontal axis.

 

[Supernova_26]

Thank you so much for your help. This worked very well

 

[thisoldman]

You are welcome. Thank you for providing feedback.