# Calculating Average from dynamic range

#### possom

##### New Member
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

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

#### vinman

##### New Member
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
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
Hi Possom,

Little tweak in the formula..

Try,

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

##### MrExcel MVP
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
Thanks guys, both formulas return the same data...

#### possom

##### New Member
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
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

##### MrExcel MVP
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.

Replies
3
Views
69
Replies
5
Views
121
Replies
4
Views
215
Replies
1
Views
252
Replies
5
Views
212

1,181,753
Messages
5,931,819
Members
436,805
Latest member
waseem abbas

### 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.

### Which adblocker are you using?

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

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