Calculating Lost_Minutes & Lost_Energy

[maxmin]

Hello All,

I need some help in writing some formulae to calculate lost_minutes and lost_energy during grid outage. My Excel skills are basic and I have tried for over a week but have not manage to write these formulae.

1. Data is as in screenshot1 below.
- spreadsheet has thousands of rows, about 800 rows per day, mostly at 1 min intervals, but not always, so can not rely on that interval.

2. The way to calculate what I need seems to be, step 1, an interim calculation spreadsheet in that workbook:

Here are the formulae pasted in the image above

H4=MINUTE(B17-B7)

H5=MINUTE(B28-B25)

H6=MINUTE(B42-B36)

J4=I4*H4

I5=AVERAGE(C18,C7)

I6=AVERAGE(C29,C24)

I7=AVERAGE(D44,D35)

3. The final summary spreadsheet is what I really need. In the final spreadsheet, there will be one row per date, totalling the data of for many instances for each date when there was grid outage.

To Generalize Formula in H4:
Time1=value in Col B, if value in Col C is < 167 OR > 286 AND value in Col D is 0 AND value in Col E > 80
find all contiguous values meeting above condition, then
Time2=value in Col B, if value in Col D is > 0
H4=Time2-Time1 in minutes

To Generalize Formula in I4
P1=Value in cell previous to first 0 in Col D
then find all such contiguous zeros with condition of Time1
P2=Value in cell in next cell after last contiguous 0 is found in Col D
I4=AVGERAGE(P1,P2)

I hope this explanation is clear, perhaps the manual calculation formulae could be of help.

I would greatly appreciate if there is help forthcoming. Thank you.

I am currently using Excel 365, in a downloaded app.

 

[offthelip]

I think this easiest way to do this is to sum the lost minutes as you go which means you don't need to determine where they start and end
Using column which equations in from F to J:
F4:=IF(C4=0,F3+MINUTE(B4-B3),0)
G4:=IF(AND(F3>0,F4=0),F3,"")
H4:=IF(AND(C4=0,D3>0),D3,H3)
I4:=IF(AND(C3=0,D4>0),AVERAGE(H3,D4),"")
J4:=IFERROR(G4*I4,"")
Copy these equations down the entire column of data
Then to get the total minutes lost in G3 I put :
G3: =SUM(G3:G30) ' Note my array ended at row 30 change this to your needs
and the total energy lost
J3: =SUM(J3:J30)

 

[maxmin]

I think this easiest way to do this is to sum the lost minutes as you go which means you don't need to determine where they start and end
Using column which equations in from F to J:
F4:=IF(C4=0,F3+MINUTE(B4-B3),0)
G4:=IF(AND(F3>0,F4=0),F3,"")
H4:=IF(AND(C4=0,D3>0),D3,H3)
I4:=IF(AND(C3=0,D4>0),AVERAGE(H3,D4),"")
J4:=IFERROR(G4*I4,"")
Copy these equations down the entire column of data
Then to get the total minutes lost in G3 I put :
G3: =SUM(G3:G30) ' Note my array ended at row 30 change this to your needs
and the total energy lost
J3: =SUM(J3:J30)

Thanks a lot.
F4 formula does not work when date changes, and B4 > B3, and the result is #NUM!. Also, condition for C4 < 160 OR C4 > 287. So C4=0 will not cover all the cases. Some changes are required. But the idea is great. Thanks.