Calculating Average from dynamic range

possom

New Member
Joined
Dec 23, 2004
Messages
13
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:

Code:
---------------------------------------------------------------------------------------- 
      |        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):

Code:
=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
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

vinman

New Member
Joined
Jan 27, 2003
Messages
19
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

New Member
Joined
Dec 23, 2004
Messages
13
hi Vinman,

I think I tried what you suggested on some simplified data:
SAP Middleware Runtimes.xls
ABCDEFGHIJKL
1Average13/12/200414/12/200415/12/2004
2StartEndDurationStartEndDurationStartEndDurationStartEndDuration
3#DIV/0!14:01:3414:02:4800:01:1423:56:2423:58:1000:01:4614:01:3714:03:0300:01:26
4014:02:5214:04:0400:01:1223:58:1800:00:0400:01:4614:03:0614:04:2700:01:21
501:24:1405:41:5904:17:4501:01:5004:54:0503:52:1501:06:1705:20:2804:14:11
605:43:4305:45:1700:01:3404:55:5704:57:3600:01:3905:22:4705:24:2200: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

Well-known Member
Joined
Feb 28, 2003
Messages
2,615
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209

ADVERTISEMENT

possom said:
...
...some simplified data:

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

...
Book1
ABCDEFGHIJKL
1Average13/12/200414/12/200415/12/2004
2StartEndDurationStartEndDurationStartEndDurationStartEndDuration
317:19:5219:31:3000:01:5414:01:3414:02:4800:01:1423:56:2423:58:1000:01:4614:01:3714:03:0300:01:26
417:21:2510:33:1300:01:5114:02:5214:04:0400:01:1223:58:1800:00:0400:01:4614:03:0614:04:2700:01:21
501:10:4705:58:4205:18:5601:24:1405:41:5904:17:4501:01:5004:54:0503:52:1501:06:1705:20:2804:14:11
605:20:4906:02:4300:02:0305:43:4305:45:1700:01:3404:55:5704:57:3600:01:3905:22:4705:24:2200: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

New Member
Joined
Dec 23, 2004
Messages
13

ADVERTISEMENT

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

New Member
Joined
Dec 23, 2004
Messages
13
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
possom said:
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.
 

Forum statistics

Threads
1,147,626
Messages
5,742,214
Members
423,714
Latest member
ftp2jz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top