5 minute average but the time data is not constant as filtered?

**NeilC73** · 11-04-2011, 12:13 AM

Hi forum

I have a little problem. I’m trying to create a macro or function to calculate an average for data over 5 minutes but the time column is filtered so that the number of cells over 5 minutes is not constant.

I can manually do this using the average function but the spreadsheet is massive and takes forever!

Below is a sample of data:

Time, Data, Average/5mins

13:39:00 125.24 = average 13:39 to 13:44
13:39:30 135.23 = average 13:39 to 13:44
13:40:00 200.36 = average 13:39 to 13:44
13:40:30 125.24 = average 13:39 to 13:44
13:41:00 100.69 = average 13:39 to 13:44
13:41:30 125.24 = average 13:39 to 13:44
13:42:00 125.24 = average 13:39 to 13:44
13:42:30 126.00 = average 13:39 to 13:44
13:43:00 254.36 = average 13:39 to 13:44
13:43:30 250.47 = average 13:39 to 13:44
13:44:00 254.82 = average 13:39 to 13:44

13:44:30 125.24 = average 13:44:30 to 13:49:30
13:45:00 113.42 = average 13:44:30 to 13:49:30
13:45:30 113.49 = average 13:44:30 to 13:49:30
13:46:30 148.65 = average 13:44:30 to 13:49:30
13:47:00 124.56 = average 13:44:30 to 13:49:30
13:49:00 113.42 = average 13:44:30 to 13:49:30
13:49:30 52.95 = average 13:44:30 to 13:49:30

13:50:30 52.95 = average 13:50:30 to 13:55:30
13:52:30 59.99 = average 13:50:30 to 13:55:30
13:55:00 135.69 = average 13:50:30 to 13:55:30
13:55:30 131.01 = average 13:50:30 to 13:55:30

Any advice would be greatly appreciated.

Many thanks
Neil

**Fotis1991** · 11-04-2011, 04:11 AM

Hi Neil.

Since as you say the column with the times using a filter, a possible solution is to use the cell above the headline "time", SUBTOTAL.
Assuming that the A2 is the headline "TIME" and have cells until 5000, then in A1,using the following formula:

Please Login or Register  to view this content.

Using the filter you already have(using Custom selection), you get your results

I hope this help's you.

**NeilC73** · 11-04-2011, 08:27 AM

Hi thanks for your reply.

When I say filtered I should have said manually filtered by me on another sheet to remove duplicates so there are no calculations or filters to display these selected times.

Hope this helps

Thanks again
Neil

**Fotis1991** · 11-04-2011, 08:38 AM

Hi

SO, in this case in line 2(that titles beeing) use the auto filter, and choose the custom option to have yours results

**NeilC73** · 11-06-2011, 01:33 AM

Hi Fotis

Thanks for your replys but I cant get past the 1st step as it throws up an error.

I have the following (original data before adding subtotal)

A1 = Time

A2 to A100+ = say from 12:00 to 15:00 but with a few randomly deleted times and dupicates so therefore not contant.

B1 = Data

B2 to B100+ with data.

I've followed your instruction but no joy. A little more help please to average my data over every 5 minutes.

Thanks again, Neil.

**DonkeyOte** · 11-06-2011, 03:54 AM

A sample file would help I think, however, if we assume your sample values are in A1:B23 with headers in A1:B1 then I would be inclined to do the following:

Please Login or Register  to view this content.

I would then push A1:Cn into a Pivot Table and use "0" [rename] as Row Label with "Data" as Data Field set to Average.

EDIT:

Attachment added for sake of explanation