Vlookup or 2d array?

[base_boot]

Hi folks,
Pretty stumped with how to go about the following:

I've been asked to create a "serives uptime" reporting tool for work.

One worksheet "ServiceList" lists the current services:

Services Availability v1.2.xls
	A	B	C	D
1	Services
2	Stratis
3	WAN
4	Email
5	Network
6	Internet
7	RemoteAccess
8	CallCentre
9	Voice
10	ExternalWebsite
11
ServiceList

A simple user form populates an Outages worksheet with outages:

Services Availability v1.2.xls
	A	B	C	D	E	F	G	H	I	J	K
1	Service	Type	StartDate/Time	EndDate/Time	TotalDuration(minutes)	BusinessDuration(minutes)	TotalMonthlyBusinessTime(minutes)	Month/Year	FinYear	ITSMInc#	Comments
2	WAN	Infrastructure	Thu1/01/200914:07	Thu1/01/200914:07	0	0	12000	Jan-09	FY08-09	n/a	FirstInfrastructureentry-required
3	RemoteAccess	Infrastructure	Thu1/01/200911:00	Fri2/01/200911:20	1460	200	12000	Jan-09	FY08-09	n/a	Checkpointpatchrequired.
4	WAN	Infrastructure	Fri2/01/200908:00	Fri2/01/200913:30	330	330	12000	Jan-09	FY08-09	n/a	Telstraissue
5	Voice	Infrastructure	Fri16/01/200917:30	Sat17/01/200912:00	1110	30	12000	Jan-09	FY08-09	n/a	NECrebootedQmaster
6	Stratis	Stratis	Thu8/01/200910:55	Thu8/01/200911:57	62	62	12000	Jan-09	FY08-09	n/a
7	WAN	Infrastructure	17/12/20088:30	17/12/200811:30	180	180	12000	Dec-08	FY08-09	n/a	Wandown
8	CallCentre	Infrastructure	15/12/200811:30	15/12/200812:30	60	60	12000	Dec-08	FY08-09	n/a	Phonesdown
9	Internet	Infrastructure	Thu12/02/200908:05	Thu12/02/200911:32	207	207	12000	Feb-09	FY08-09	n/a
10	Internet	Infrastructure	4/02/200912:00	4/02/200913:00	60	60	12000	Feb-09	FY08-09	n/a	.
11	ExternalWebsite	Infrastructure	19/11/20088:06	19/11/200810:06	120	120	11400	Nov-08	FY08-09	.
12	Internet	Infrastructure	27/12/200811:13	27/12/200813:04	111	0	12000	Dec-08	FY08-09	n/a
13	Stratis	Stratis	11/11/200814:25	11/11/200820:00	335	215	11400	Nov-08	FY08-09	n/a
Outages

 

[Lincoln Six Echo]

Basically, you want to show totals beside the row headers in Sheet 1?
You could use a pivot table, or the SumIf function.

You didN,t say what the report should look like, so it's a bit hard to help ...

 

[base_boot]

What I need to create is the following:

Services Availability v1.2.xls
	A	B	C	D	E	F	G	H
1	Availability	Target	Nov-08	Dec-08	FY09/10	Jan-09	Feb-09
2	WAN	99.0%	100%	99%	99%	97%	100%
3	Email	99.0%	100%	100%	100%	100%	100%
4	Network	99.0%	100%	100%	99%	100%	97.77%
5	Internet	99.0%	100%	100%	100%	100%	100%
6	RemoteAccess	99.0%	100%	100%	99%	98%	100%
7	CallCentre	99.0%	100%	100%	100%	100%	100%
8	Voice	99.0%	100%	100%	100%	100%	100%
9	ExternalWebsite	99.0%	98%	100%	100%	100%	100%
10
11	Infrastructure	99.0%	99.75%	99.75%	99.57%	99.42%	99.72%
12
13	Stratis	99.00%	99.11%	100.00%	99.74%	99.48%	100.00%
14
Demo Report

Essentially from the outages worksheet the "business duration" for each service for each month is divided by the "Total Monthly Business Time (minutes)" to find a percentage downtime. If there are multiple entries for any particular service for that month then their respective Business Duration (minutes) are combined to calculate the percentage. IE service Internet in Feb 09.

If there are no entries for the month for the services listed on the "ServiceList" worksheet then the percentage uptime is 100%

The Services listed on the "ServiceList" worksheet may change.

No idea how to go about this. I think it may be a simple vlookup, but not sure how to create an array from the "ServiceList" worksheet to crosscheck against the "Outages" worksheet.

Any help would be much appreciated!

 

[base_boot]

Hey Lincoln Six Echo,
you guys are quick! I was adding the second part to the post as you replied!

I did try a pivot table, but it doesn't allow me to calculate each sevice independantly. It groups them all under the type Infrastructure / Stratis (column B "outages" worksheet). Totals each "business duration minutes" for each month and from the calculates the percentage availabilty.

Calculated Field
PercentageUptime = ('Total Monthly Business Time (minutes)'-'Business Duration (minutes)') /'Total Monthly Business Time (minutes)'

To give:

Services Availability v1.2.xls
	A	B	C	D
3			Data
4	Month/Year	Type	SumofBusinessDuration(minutes)	PercentageUptime
5	Nov-08	Infrastructure	120	98.95%
6		Stratis	215	98.11%
7	Dec-08	Infrastructure	240	99.00%
8		Stratis		100.00%
9	Jan-09	Infrastructure	560	95.33%
10		Stratis	62	99.48%
11	Feb-09	Infrastructure	267	97.78%
12		Stratis		100.00%
report

 

[Lincoln Six Echo]

I did it for 2 services only. And I didn't include the type. If the formula works for you, just add a 3rd criteria for the type ...

Excel Workbook
	C	D	E
23		Dec-08	Feb-09
24	WAN	180	530
25	Call Centre	60	92
Sheet1

 

[base_boot]

Hey,
again thanks for getting back to me.

the sumifs function is excel 2007. I could use SUMPRODUCT, however that won't take into account a return if there is no outage reported for a particualr service for the month.

I'll try to explain it again.

We have a list of services. Each time there's an outage for one it's logged on the "outages" worksheet. There's the "Total Monthly Business Time (minutes)" and "Business Duration (minutes)" for each logged outage.

At the end of the month I need to caculate the Business Time percentage uptime for each service listed on the outages worksheet.

Add up all the "Business Duration (minutes)" for each service for the month. individually.
100 - ((Each totals divided by Total Monthly Business Time (minutes)) x 100) = percentage uptime for each service

If however there's no entry for any particular service then the percentage uptime is obviously 100%

Example:

Service Internet in Feb 09:
100 - ((267 / 12000) x 100) = 97.77(%)

Service Stratis in Feb 09:
no entry => 100(%)

I'd be keen on doing this via a macro rather than formula, easier to debug and at least for me eaiser to understand!

Thanks for your help
Shane.

 

[base_boot]

Any thoughts folks?

If further clarification, (or perhaps just clarification!), is required please let me know!!

Really appreciate any thoughts/suggestions/hints.