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]

Come on guys, someone can surely help with this

 

[PCL]

Did you ever try a Pivot table

 

[Mike Blackman]

Many many times, but a Pivot Table is not going to do for this one I'm afraid.

 

[Andrew Poulsom]

In B21 try:

=(SUM(IF($A$3:$A$16=$A21,IF($B$3:$B$16<=B$20,IF($C$3:$C$16>DATE(YEAR(B$20),MONTH(B$20)+1,1),DATE(YEAR(B$20),MONTH(B$20)+1,1),IF($C$3:$C$16>=DATE(YEAR(B$20),MONTH(B$20),1),$C$3:$C$16,DATE(YEAR(B$20),MONTH(B$20),1))))))-SUM(IF($A$3:$A$16=$A21,IF($B$3:$B$16<=B$20,IF($B$3:$B$16>DATE(YEAR(B$20),MONTH(B$20),0),$B$3:$B$16,DATE(YEAR(B$20),MONTH(B$20),1))))))*24*60

which must be confirmed with Ctrl+Shift+Enter. Copy down and across.

 

[Mike Blackman]

Hi Andrew,

Nearly there, The results I'm getting are;

My God.xls
	A	B	C	D
20		Jan08	Feb08	Mar08
21	W000001	44640	69317.86667	33633.95
22	W000002	0	40476.9	0
Summary

For some reason Row 4 is not picking up in the Jan 08 slot, can't pinpoint why that would be in all honesty.

I also notice you are using the dates to replace Column D, unfortunately Column D is not a direct representation of ((C3-B3)*60)*24 but a slightly different stat based upon Outage Time and not the full ticket time.

Excellent job of picking up what I'm trying to do though.

The Stat I'm calculating is mean time between failure (MTBF) but I doubt such knowledge is actually going to help any

 

[Andrew Poulsom]

With your sample data and the formula I posted I got:

<TABLE style="WIDTH: 338pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=451 border=0 x:str><COLGROUP><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 95pt; mso-width-source: userset; mso-width-alt: 4644" span=3 width=127><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl24 id=td_post_1863135 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=70 height=17> </TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=127 x:num="39478">31/01/2008</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=127 x:num="39507">29/02/2008</TD><TD class=xl25 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 95pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=127 x:num="39538">31/03/2008</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>W000001</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="51774.233333319426">51774.23333</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="69317.866666652262">69317.86667</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="41969.283333336934">41969.28333</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl26 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>W000002</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="16226.566666663857">16226.56667</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num="40476.899999995949">40476.9</TD><TD class=xl24 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" align=right x:num>0</TD></TR></TBODY></TABLE>

 

[Mike Blackman]

Ahhh, Sorry Andrew, I meant to say and knew it would cause issue but still forgot to say;

My Header dates are the first of the month not the final day.

Also, as i mentioned, I need to be using Column D and not the minutes the ticket is open, I think that is causing issue. The right hand side of my initial table was calculating perferctly, the reults within each row were adding up to the value in Column D so I'd expect to see a result of;

My God.xls
	A	B	C	D
24		Jan08	Feb08	Mar08
25	W000001	44681.1288641122	41918.8711358878	36388.9499999979
26	W000002	16,035.85	40,001.15	-
Summary

 

[Colin Legg]

Okay, think I got it....

Going with you result table cell locations per post #6, try this formula in B21 then copy across and down. For the Mar 08 values to show you will have to put in Apr 08 in cell E20.

=SUM((($A$3:$A$16=$A21)*((IF($C$3:$C$16 > (C$20-(1/86400)),C$20-(1/86400),
    $C$3:$C$16)-IF($B$3:$B$16 > B$20,$B$3:$B$16,B$20)))*($B$3:$B$16 < C$20)
    *($C$3:$C$16 > B$20))/($C$3:$C$16-$B$3:$B$16)*($D$3:$D$16))
 
<?XML:NAMESPACE PREFIX = C$20)*($C$3 /><C$20)*($C$3:$C$16>

</C$20)*($C$3:$C$16>

 

[Andrew Poulsom]

I amended my formula:

=(SUM(IF($A$3:$A$16=$A21,IF($B$3:$B$16<DATE(YEAR(B$20),MONTH(B$20)+1,1),IF($C$3:$C$16>DATE(YEAR(B$20),MONTH(B$20)+1,1),DATE(YEAR(B$20),MONTH(B$20)+1,1),IF($C$3:$C$16>=DATE(YEAR(B$20),MONTH(B$20),1),$C$3:$C$16,DATE(YEAR(B$20),MONTH(B$20),1))))))-SUM(IF($A$3:$A$16=$A21,IF($B$3:$B$16<DATE(YEAR(B$20),MONTH(B$20)+1,1),IF($B$3:$B$16>DATE(YEAR(B$20),MONTH(B$20),0),$B$3:$B$16,DATE(YEAR(B$20),MONTH(B$20),1))))))*24*60