Average for specific dates of gap time.

**rvancura** · 03-16-2009, 01:36 PM

Hi everyone,

I would like to create an average function that will take an average of the Column labeled "Gap Time (Hours/Min/Sec)". I only want it to take the average for this for each new start date. These values will change daily so I was hoping that someone may help me write a function that will work when data changes instead of manually taking the average every time data is entered. I appreciate your help.

-Ryan-

**DonkeyOte** · 03-16-2009, 01:46 PM

Why not use a Pivot Table to do this for you -- see attached (2007 file format)

**rvancura** · 03-17-2009, 07:17 AM

Originally Posted by DonkeyOte

Why not use a Pivot Table to do this for you -- see attached (2007 file format)

This is great. I haven't really worked with Pivot tables in the past. I tried recreating it on my own. However, I'm not sure how you added "months" and "years" to the "PivotTable Field List" and under "Column Labels" I'm not sure how you added "Values". I'll continue to research and try to figure this out. I appreciate any help and I am grateful for you assistance.

**DonkeyOte** · 03-17-2009, 07:23 AM

In answer to your first question re: Months & Years - this is done by "Grouping"

-- Select a Date as listed in your Row Label Field
-- Right Click
-- Select Group
-- Select Days & Months & Years
-- Click OK

Re: Values - the Value field is set to Gap Time column but Field Setting is altered to AVERAGE (will default to COUNT I suspect) -- to Set to Average:

-- Right click on the Column Header
-- Choose "Value Field Settings"
-- Select AVERAGE
-- Click Number Format
-- Select Custom
-- in "Type box" enter: [hh]:mm:ss
-- Click OK
-- Click OK

Done

**rvancura** · 03-17-2009, 07:56 AM

Thank you again. Would you by any chance have a suggestion on how to create a function for the pivot table that would not count the the gap time if exceeds more than 5 hours?

**DonkeyOte** · 03-17-2009, 08:00 AM

I don't have time at the moment to look at this in detail I'm afraid but off hand a quick solution may be to alter source data (Gap Time column) such that:

L3:
=IF((F3-H2)>TIME(5,0,0),"",F3-H2)
copied down

Refresh the PT (you will of course get DIV/0 error on the PT if there are no entries for a given day that are less than the 5 hour threshold)