0

I have data in below format. It shows starting and end time of an activity and calculates duration accordingly. The activity is performed through out the day at different times.

enter image description here

I have added a pivot. I want to find out the average duration in a workday or a holiday(Day category). When I am trying to apply average in the current pivot, it is dividing the total duration by the number of sessions in a day.For example in week 1, an activity was done on 4 work days and the total duration for the activity in workdays was 04.19, I want to divide this number by 4 and find out the average time spent on each day but the pivot divides it by 11 which is the total number of sessions in the four days.

enter image description here

Link for data

Vinamra Bali
  • 81
  • 8

1 Answers1

1

Steps:

  1. Add a helper column to identify how many unique pairs of Dates/Day Categories there are: =IF(SUMPRODUCT(($A$2:$A2=A2)*($B$2:$B2=B2))>1,0,1) You can add extra products to this formula to force extra fields to be unique to be counted as well.

SRC:Simple Pivot Table to Count Unique Values

  1. Add a Calculated Field in the PivotTable that is: SUM(Duration)/SUM([Helper Column Name]) and include it in the 'Values' section of the PivotTable. Due to the new column being added, you might have to re-create the PivotTable.

This should produce the average in the manner that you want.

Ben Nowotny
  • 39
  • 2