Calculate impact multiple incidents have on a day

[Glassford]

So I am after some help with the table bellow, I'm trying to calculate how much time is lost per day due to incidents happening. The table on the top is the information we are given which includes start/finish times, duration and how much impact it had to business.

What I am trying to make is a formula to sit in that "Availability" column which will tell me the percentage of hours not impacted for the day. I was able to get the maths working for a single incident but I am not sure how to calculate it over an array. Any help would be incredible thanks.

[TABLE="class: grid, width: 500, align: left"]
<tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Duration[/TD]
[TD]Weighting[/TD]
[TD]Impact[/TD]
[/TR]
[TR]
[TD]1/8/18 0:00
[/TD]
[TD]6/8/18 0:00[/TD]
[TD]120:00[/TD]
[TD]10%[/TD]
[TD]12:00[/TD]
[/TR]
[TR]
[TD]1/8/18 0:00[/TD]
[TD]3/8/18 0:00[/TD]
[TD]48:00[/TD]
[TD]10%[/TD]
[TD]4:48[/TD]
[/TR]
[TR]
[TD]1/8/18 0:00[/TD]
[TD]1/8/18 12:00[/TD]
[TD]12:00[/TD]
[TD]10%[/TD]
[TD]1:12[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Total Impact[/TD]
[TD]Availability[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1/8/18[/TD]
[TD]6:00[/TD]
[TD]75%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2/8/18[/TD]
[TD]4:48[/TD]
[TD]80%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3/8/18[/TD]
[TD]2:24[/TD]
[TD]90%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4/8/18[/TD]
[TD]2:24[/TD]
[TD]90%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5/8/18[/TD]
[TD]2:24[/TD]
[TD]90%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6/8/18[/TD]
[TD]0:00[/TD]
[TD]100%[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

I'm a bit confused sorry... is the Total Impact column calculated from the Availability column? Seems that the Total Impact is the difficult calculation, not the percentage of 6 out of 24 hours?

 

[NiMip]

Anyway, marching on hopefully in the right direction, assuming "Start" is in cell B2, the following formula in the Availability line should do the trick! Note that it is an array formula and hence should be entered using CONTROL+SHIFT+ENTER. You'll see {...} appear around the formula if entered correctly.

=1-SUM(IF((B8>=$B$3:$B$5)*(B8<$C$3:$C$5),$F$3:$F$5/ROUNDUP(($C$3:$C$5-$B$3:$B$5),0)))/24

 

[Glassford]

Yeah this works! Had to delete the /24 at the end though. Thanks a bunch . Never even thought of using roundup, I was trying all other kinds of weird and wonderful formulas, unfortunately they didn't work haha.

 

[NiMip]

Excellent! Ah no worries, wasn't sure if the 120:00 was just formatting or actually the value in hours - I assumed the latter. Yeah the half-day incident caught me out at first too! Glad I could help

 

[Glassford]

So I've come across an issue with the formula. If the start or finish times is anything other than midnight once the duration is more than 1 day the formula doesn't work. I've workout out a couple ways to make it work for a single entries but as soon as I try turn it into an array it completely fails. I used a nested IF function with a bunch of parameters which worked but as soon as the array hit a square with nothing in it the formula would come back as =0.

 

[NiMip]

Oh yeah fair point... because the list of days in the summary are all at 00:00. I've also done some nested IF functions in the formula below, which works with arrays... not sure about your issue with blank squares in it though? Can you paste an example of this issue?

Same deal as last time... "Start" column located in B2 and Control+Shift+Enter

=1-SUM(IF((INT(B8)>=INT($B$3:$B$5))*(B8<=$C$3:$C$5),IF(B8=INT($C$3:$C$5),$C$3:$C$5-B8,IF(B8=INT($B$3:$B$5),1-($B$3:$B$5-B8),1))))*$E$3:$E$5

[TABLE="class: grid, width: 660"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Start[/TD]
[TD]Finish[/TD]
[TD]Duration[/TD]
[TD]Weighting[/TD]
[TD]Impact[/TD]
[/TR]
[TR]
[TD="align: right"]1/08/2018 0:00[/TD]
[TD="align: right"]2/08/2018 12:00[/TD]
[TD="align: right"]36:00:00[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3:36[/TD]
[/TR]
[TR]
[TD="align: right"]1/08/2018 12:00[/TD]
[TD="align: right"]4/08/2018 0:00[/TD]
[TD="align: right"]60:00:00[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]6:00[/TD]
[/TR]
[TR]
[TD="align: right"]7/08/2018 6:00[/TD]
[TD="align: right"]8/08/2018 12:00[/TD]
[TD="align: right"]30:00:00[/TD]
[TD="align: right"]10%[/TD]
[TD="align: right"]3:00[/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]Day[/TD]
[TD]Total Impact[/TD]
[TD]Availability[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]1/08/2018[/TD]
[TD="align: right"]6:00[/TD]
[TD="align: right"]85%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]2/08/2018[/TD]
[TD="align: right"]4:48[/TD]
[TD="align: right"]85%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]3/08/2018[/TD]
[TD="align: right"]2:24[/TD]
[TD="align: right"]90%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]4/08/2018[/TD]
[TD="align: right"]2:24[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]5/08/2018[/TD]
[TD="align: right"]2:24[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]6/08/2018[/TD]
[TD="align: right"]0:00[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]7/08/2018[/TD]
[TD="align: right"]21:36[/TD]
[TD="align: right"]92.5%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]8/08/2018[/TD]
[TD="align: right"]19:12[/TD]
[TD="align: right"]95%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD="align: right"]9/08/2018[/TD]
[TD="align: right"]16:48[/TD]
[TD="align: right"]100%[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[NiMip]

Sorry, ignore the values in the "Total impact" column - forgot to put a proper formula in there.