?
Solved

I need help calculating EXCEL SLA for Server Downtime

Posted on 2010-03-29
4
Medium Priority
?
3,434 Views
Last Modified: 2012-05-09
I need someone to assist me with the following excel document. This document has two tabs. The first tab is the raw data for the excel sheet. The second tab is information that requires interpretation to get numbers. For instance, the Incidents By BU Past 30 Days has incidents in the past 30 days by BU. The number needs to be the amount of incidents that are 30 days or older and that belong to that BU. The second time is the total down time. Total down time is the amount of minutes the server was down. The percentage Up time should be the number of minutes the server was down divided by the time the servers was up (minutes in 30 days). I have pre-populated the excel sheet with data to make the formulas work. To complete this task, you will need to create forumulas for the B, C, and D columns for the second worksheet and these columns should adjust themselves accordingly based on the data that is entered on the 1st sheet.  Any ideas or a better approach to this type of collection?
Server-Outage-Tracking-Draft.zip
0
Comment
Question by:securitythreat
  • 2
4 Comments
 
LVL 5

Accepted Solution

by:
ericathome earned 1000 total points
ID: 29065795
See if the attached helps. You may want to adjust hours for uptime percentages depending on how many hours a day servers are used.

Let me know if you need any changes.

Thanks, Eric
Server-Outage-Tracking-Draft.xlsx
0
 
LVL 50

Assisted Solution

by:Ingeborg Hawighorst (Microsoft MVP / EE MVE)
Ingeborg Hawighorst (Microsoft MVP / EE MVE) earned 1000 total points
ID: 29075867
Hello securitythreat.

Please note that ericathome's file does not calculate what you want. I have added a few more rows of data to create a range of dates that goes back more than 30 days. Ericathome's version does not take the last 30 days into account at all, but simply takes the whole data from the outage sheet.

I have created three dynamic range names that will grow with the data

BusinessUnit      =Outages!$F$31:INDEX(Outages!$F:$F,MATCH(99^99,Outages!$A:$A,1))
DownTime      =Outages!$D$31:INDEX(Outages!$D:$D,MATCH(99^99,Outages!$A:$A,1))
ODate      =Outages!$A$31:INDEX(Outages!$A:$A,MATCH(99^99,Outages!$A:$A,1))

To calculate the Incidents per BU in the last 30 days
=COUNTIFS(BusinessUnit,A2,ODate,">="&TODAY()-30)

Total down time in the last 30 days
=SUMIFS(DownTime,BusinessUnit,A2,ODate,">="&TODAY()-30)
formatted with custom format [hh]:mm:ss

This custom format will give you correct hour values even when the hours are more than 24. In ericathome's file, the date portion would be cut off, and you would see 25:34:00 as 1:34:00, which can be quite misleading.

Percentage uptime for the last 30 days
=1-C2/(60*24*30)

Please see attached file.

cheers, teylyn
Q-25600843-Outage-calculations.xlsx
0
 
LVL 5

Expert Comment

by:ericathome
ID: 29112005
good catch - I did see the 30 day requirement.
0
 
LVL 1

Author Comment

by:securitythreat
ID: 29117262
This worked.. I will probably have more help request.. but thanks.. this is a great start.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

599 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question