Calculating Average from dynamic range

[possom]

Hi there,

I have a sheet with a growing number of columns, each date has a start, end and duration. I would like to have one set of columns which will dynamically calculate the average of all appropriate columns. The layout is below:

---------------------------------------------------------------------------------------- 
      |        Average           |       20/12/2004         |       21/12/2004         |... 
---------------------------------------------------------------------------------------- 
Name  | Start |  End  | Duration | Start |  End  | Duration | Start |  End  | Duration |... 
---------------------------------------------------------------------------------------- 
proc1 | 02:40 | 03:00 | 00:20    | 02:10 | 02:45 | 00:30    | 03:10 | 03:15 | 00:05    |...  
proc2 | 03:40 | 04:00 | 00:20    | 03:10 | 03:45 | 00:35    | 04:10 | 04:20 | 00:10    |... 
proc3 | 04:00 | 04:20 | 00:10    | 04:00 | 04:20 | 00:10    | 04:00 | 04:20 | 00:10    |... 
...

I am currently manually adding the new date's information to a formula, but this is too much work and the number of characters seems to be starting to exceed the maximum. Below is an example of the start-time average formula (note the above data is in "DD/MM/YYYY HH:MM:SS" format, but formatted to only show time):

=AVERAGE(TIME(HOUR(J4),MINUTE(J4),SECOND(J4)),TIME(HOUR(M4),MINUTE(M4),SECOND(M4))...

I was thinking of creating a macro which updates the average values, when the sheet is activated, for each row's start, end and duration. Any ideas would be appreciated.

Thanks,
possom

 

[vinman]

You should be able to use a sumif formula based on the header row. This way everytime you add a new column with the same header name it will automatically be added to your formula.

Lets assume your average columns are A,B & C and your data is in groups of three beginning on row 2 in column d,e & f and going out as far as you like. As long as the headers for each new set of columns that are added each day are consistant, you can use the formula below beginning in cell A2. You should be able to drag it down as far as you like.


=SUMIF($D$1:$IV$1,A$1,$D2:$IV2)/COUNTIF($D$1:$IV$1,A$1)

Vinman

 

[possom]

hi Vinman,

I think I tried what you suggested on some simplified data:

SAP Middleware Runtimes.xls
	A	B	C	D	E	F	G	H	I	J	K	L
1	Average			13/12/2004			14/12/2004			15/12/2004
2	Start	End	Duration	Start	End	Duration	Start	End	Duration	Start	End	Duration
3	#DIV/0!			14:01:34	14:02:48	00:01:14	23:56:24	23:58:10	00:01:46	14:01:37	14:03:03	00:01:26
4	0			14:02:52	14:04:04	00:01:12	23:58:18	00:00:04	00:01:46	14:03:06	14:04:27	00:01:21
5				01:24:14	05:41:59	04:17:45	01:01:50	04:54:05	03:52:15	01:06:17	05:20:28	04:14:11
6				05:43:43	05:45:17	00:01:34	04:55:57	04:57:36	00:01:39	05:22:47	05:24:22	00:01:35
Sheet1

It seems that it is not using the column header to select the records - what am I not doing? I'll reiterate, I need A3 to be an average of D3, G3, J3 etc.

Besides this, do you think a sum/count will average times?

Thanks,
possom

 

[Krishnakumar]

Hi Possom,

Little tweak in the formula..

Try,

=SUMIF($D$2:$IV$2,A$2,$D3:$IV3)/COUNTIF($D$2:$IV$2,A$2)

 

[Aladin Akyurek]

...
...some simplified data:

...
...I need A3 to be an average of D3, G3, J3 etc.

...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1	Average			13/12/2004			14/12/2004			15/12/2004
2	Start	End	Duration	Start	End	Duration	Start	End	Duration	Start	End	Duration
3	17:19:52	19:31:30	00:01:54	14:01:34	14:02:48	00:01:14	23:56:24	23:58:10	00:01:46	14:01:37	14:03:03	00:01:26
4	17:21:25	10:33:13	00:01:51	14:02:52	14:04:04	00:01:12	23:58:18	00:00:04	00:01:46	14:03:06	14:04:27	00:01:21
5	01:10:47	05:58:42	05:18:56	01:24:14	05:41:59	04:17:45	01:01:50	04:54:05	03:52:15	01:06:17	05:20:28	04:14:11
6	05:20:49	06:02:43	00:02:03	05:43:43	05:45:17	00:01:34	04:55:57	04:57:36	00:01:39	05:22:47	05:24:22	00:01:35
7
Sheet1

A3, copied to C3 then down:

=SUMPRODUCT(--(MOD(COLUMN(D3:$IV3)-CELL("Col",D3)+0,3)=0),(D3:$IV3))/(COUNT(D3:$IV3)/3)

 

[possom]

Thanks guys, both formulas return the same data...

 

[possom]

Doing further testing, the formulas work fine as long as all the dates are regular with no breaks. I have cases where there is no entry (empty cells) for a scheduled date, and my scheduled dates have breaks (during the weekends). Both of these cases cause the the formulas provided by Aladin and vinman/Krishnakumar to skew the results.

Sorry for not being clearer before, but all of my data is date-time (but have showed it as HH:MM:SS). For this average, all I require is the time component of the value, thus my formula that used AVERAGE( TIME(HOUR(...), MINUTE(...), SECOND(...)) ...).

Is there a way of only using the time in the above formulas?

Thanks,
possom

 

[possom]

Any ideas with the above question? I think that I probably will need to write a macro to calculate the average times when the sheet is refreshed/updated.

Cheers,
possom

 

[Aladin Akyurek]

Any ideas with the above question? I think that I probably will need to write a macro to calculate the average times when the sheet is refreshed/updated.

Cheers,
possom

Maybe you could try to modify the layout such that the relevant fields follow a regular pattern, which you initially implied to be the case with your data.