I have a set of data showing system outages and am developing some metrics on these figures. One metric that I am working on is time between failure. What this means is how much time elapsed between two outages based on their start date/time. What is making this difficult is that three different types of outages exist(planned outage, down, degraded), and I only want to know the elapsed time between two of those (down & degraded). So in other words, I need a formula that ignores any records labeled as "planned outage". Below is an example of the data I am using. The numbers are all fictitious, so the example is very simplistic.
begin end type
7/30/2013 22:45 7/31/2013 0:20 Degradation
7/29/2013 22:45 7/30/2013 0:20 Degradation
7/28/2013 22:45 7/29/2013 0:20 Degradation
7/27/2013 22:45 7/28/2013 0:20 Degradation
7/26/2013 22:45 7/27/2013 0:20 Degradation
7/25/2013 22:45 7/26/2013 0:20 Degradation
7/24/2013 22:45 7/25/2013 0:20 Degradation
7/23/2013 22:45 7/24/2013 0:20 Degradation
7/22/2013 22:45 7/23/2013 0:20 Outage
7/21/2013 22:45 7/22/2013 0:20 Planned Outage
7/20/2013 22:45 7/21/2013 0:20 Degradation
7/19/2013 22:45 7/20/2013 0:20 Planned Outage
7/18/2013 22:45 7/19/2013 0:20 Outage
7/17/2013 22:45 7/18/2013 0:20 Planned Outage
7/16/2013 22:45 7/17/2013 0:20 Degradation
7/15/2013 22:45 7/16/2013 0:20 Outage
7/14/2013 22:45 7/15/2013 0:20 Planned Outage
7/13/2013 22:45 7/14/2013 0:20 Planned Outage
7/12/2013 22:45 7/13/2013 0:20 Outage
I have created a nested if/then formula:
=IF(AND(F2<>"Planned Outage",F3<>"Planned Outage"),D2-D3,IF(AND(F2<>"Planned Outage",F4<>"Planned Outage"),D2-D4,IF(AND(F2<>"Planned Outage",F5<>"Planned Outage"),D2-D5,IF(AND(F2<>"Planned Outage",F6<>"Planned Outage"),D2-D6,IF(AND(F2<>"Planned Outage",F7<>"Planned Outage"),D2-D7,IF(AND(F2<>"Planned Outage",F8<>"Planned Outage"),D2-D8,IF(AND(F2<>"Planned Outage",F9<>"Planned Outage"),D2-D9,"")))))))
however, this formula is very clunky and also limited to 7 conditions, and there are instances where more than 7 "Planned Outages" occurr in a row. In those cases, the result is inaccurate and manual intervention is required. Having to apply this metric to hundreds of records on dozens of different tables makes that process very annoying. Any help would be greatly appreciated.
Bookmarks