Array Formula

[Mike Blackman]

Hi,

I am trying to write an array formula that will work out how many minutes of tickets are open within a particular month. I have a formula shown on the first table which calculates how many minutes a ticket is open for within a particular month, but this only works for an individual ticket, Basically I neet to populate the 2nd table with an array formula that will calculate the number of minutes within the month that numerous tickets are open, effectively a SUMPRODUCT but I can't get it to work.

The right hand side of Table one doesn't exist in my main file, I am trying to populate the 2nd table using only the first four columns within Table 1.

My God.xls
	A	B	C	D	E	F	G	H
1			01/02/2008			Jan08	Feb08	Mar08
2						31	29	31
3	W000002	20/01/200817:33:26	29/02/200802:36:54	56037		16,035.85	40,001.15	-
4	W000001	27/01/200801:05:46	20/02/200803:17:52	200		41.13	158.87	-
5	W000001	22/03/200809:06:18	24/03/200808:33:57	789		-	-	789.00
6	W000001	24/03/200817:56:01	28/03/200813:23:42	1,966		-	-	1,966.00
7	W000001	07/06/200820:27:18	09/06/200806:42:22	214		-	-	-
8	W000001	24/06/200817:14:39	10/07/200807:19:43	2,882		-	-	-
9	W000001	13/08/200806:29:36	16/08/200808:14:53	2,344		-	-	-
10	W000001	27/09/200815:49:53	28/09/200809:26:05	1,026		-	-	-
11	W000001	24/10/200820:33:09	26/10/200810:04:30	13		-	-	-
12	W000001	20/11/200802:03:28	26/11/200815:38:23	6645		-	-	-
13	W000001	20/11/200808:18:32	24/11/200811:26:38	1073		-	-	-
14	W000001	15/12/200812:20:59	18/01/200915:54:22	3,445		-	-	-
15	W000001	18/01/200916:32:47	19/01/200912:50:47	317		-	-	-
16	W000001	26/12/200714:24:00	24/03/200808:33:57	127810		44,640.00	41,760.00	33,633.95
Summary

My God.xls
	A	B	C	D
20		Jan08	Feb08	Mar08
21	W000001	???	???	???
22	W000002	???	???	???
Summary

I know this is as clear as mud guys but would really appreciate some help with this.

 

[Mike Blackman]

Hi Colin,

The date headers we've been playing with are housed on half a dozen other sheets also and all reference a set of dates that the dashboard houses, shows and references.

Basically the first month is hard coded although I have a fairly snazzy userform to allow the to select this, although manually changing it is allowed, the following 11 months are populated using;

=DATE(YEAR(B11),MONTH(B11)+1,1)

So all the dates are controlled from the dashboard, The reason I didn't buy into the additional month was that all the reports including the Dashboard have Total Columns in the adjacent cell. Now I could have added and hidden an extra column but I'd probably be digging into some code that kicks in on the Dashboard and to be fair, I knew we could avoid the extra date, just I get lost in these crazy Array formula. Hence my rebuttle of the idea

Add that with the users ability to break the most protected of things i didn't want to place my trust in the hundreds of SM's that use this badboy.

Enough babble

Thanks again for your help.

 

[Mike Blackman]

Hi Barry,

I do have an slight problem with your formula that I don't get with Colin's and thats the fatal Leap Day on these two items (well one of), I remember seeing quite an in depth thread regarding this between Peter & Luke and assume its the Leap Day thats foiling your formula;

My God.xls
	A	B	C	D	E	F	G	H
1	TicketNumber	AlternateTicketNumber	CircuitId	OutageCause	CreatedDate/Time	ClosedDate/Time	TotalMinutes	OutageTimeMinutes
2	2008012002668		W00000001	******	20/01/200817:33:26	29/02/200802:36:54	56703	56037
3	2008012700204	15469016	W00000001	CUST	27/01/200801:05:46	20/02/200803:17:52	34692	0
Data

This also re-emphasizes why I needed to use Column D (H in the real Data) and not the calc between the two dates. Just so you don't think I was being picky

Thank you all for your highly appreciated help guys.

 

[barry houdini]

Hello Mikey,

Sorry I'm not clear what the problem is. I can't tell from your example, can you explain?

There shouldn't be any issue with leap days or years with my suggested formula, or Colin's........

 

[Mike Blackman]

Hi Barry,

I can't explain it, I replicated it with just the two items and it worked perfectly, placed your formula next to Colin's back in my model with the correct ranges and again, different, 95% of the calculations are perfect just the odd few don't seem to pick up correctly. Here's another;

My God.xls
	D	E	F	G
15	W0S19349	30/01/200805:33:27	12/02/200810:04:51	10215
Data

The results;

Barclays Capital Global SLA Jan09 090218 New Template.xls
	Z	AA	AB	AC
32	10215	8,845.27	2,217.00	-
MTBF

Z holds your formula and AA is Colin's, both reading the same period.... It is however clear its not actually related to the Leap Day itself, at least not obviously.

Z = =SUM(IF(CircID=$A32,IF(DateCreated>H$11+32-DAY(H$11+31),0,IF(DateClosed<H$11,0,(IF(DateClosed>H$11+32-DAY(H$11+31),H$11+32-DAY(H$11+31),DateClosed)-IF(DateClosed< H$11,H$11,DateCreated))/(DateClosed-DateCreated)*OutTimeMins))))

AA = =SUM(((CircID=$A32)*((IF(DateClosed>(EOMONTH(H$11,0)+1-(1E-24)),EOMONTH(H$11,0)+1-(1E-24),DateClosed)-IF(DateCreated>H$11,DateCreated,H$11)))*(DateCreated<EOMONTH(H$11,0)+1)*(DateClosed>H$11))/(DateClosed-DateCreated)*(OutTimeMins))

CircID being Column A from original, DateCreated being B, DateClosed being C, OutTimeMins beind D.