Average Number of Days in a Quarter

**dvent** · 11-15-2007, 10:31 AM

I have a worksheet containing 5 columns. A start and end date, a number of days between these dates, the quarter number of the start date (i.e. 1 to 4) and then the year.

I want to calculate the average number of days per quarter so an average of days in column C, based on the details in column D and E.

Can anyone help?

I have attached an example workbook.

Thanks
dvent

**Ron Coderre** · 11-15-2007, 10:46 AM

I'd use a Pivot Table....

From the Excel Main Menu: <Data><Pivot Table>
Use: Excel……Click [Next]
Select your data……Click [Next]
Click the [Layout] button

ROW:
...Drag the YEAR field here
...Drag the QUARTER field here
COLUMN: (leave this area blank)
DATA: Drag the NUMBER OF DAYS field here
...dbl-click it and set it to Average
Click [OK]
Select where you want the Pivot Table…Click [Finish].

BUT...we're not quite there yet!
You threw a curve ball in the YEAR column....Those are dates displayed as years.

Right-click on the YEAR heading in the Pivot Table
...Select: Group and show detail....Group
...Set: Year..(de-select anything else)
...Click [OK]

NOW the pivot table shows each YEAR and QUARTER and the Average of NUMBER OF DAYS.

Note: if you don't want the average days per qtr for the year...
Right-click on the YEAR field....Field settings....Subtotals: None

To refresh the Pivot Table, just right click it and select Refresh Data

Is that something you can work with?

**daddylonglegs** · 11-15-2007, 11:17 AM

But I think dvent wants to calculate an average separately for each quarter of each year...

If so then here's a formula approach,

Quarter number, e.g. 2 in J2
Year in K2, a number not a formatted date, e.g. 2005

then in L2

=AVERAGE(IF($D$2:$D$1000=J2,IF(YEAR($E$2:$E$1000)=K2,$C$2:$C$1000)))

confirmed with CTRL+SHIFT+ENTER

copy the formula down for averages for each year/quarter combination shown in cols J and K

**dvent** · 11-15-2007, 11:22 AM

A pivot table seems to do the trick thanks!

I have tried to write the pivot table creation bit into a macro but have come across a problem.

What I have is this:

Sub Macro4()
'
' Macro4 Macro
'
    Lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
    Range("A1").PivotCaches.Add(SourceType:=xlDatabase, SourceData:="'Average Deployment'!R1:R" & Lastrow).CreatePivotTable TableDestination:="", TableName:="PivotTable10", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    ActiveSheet.PivotTables("PivotTable10").AddFields RowFields:=Array("Year", "Quarter")
    With ActiveSheet.PivotTables("PivotTable10").PivotFields("Number of Days")
        .Orientation = xlDataField
        .Caption = "Average of Number of Days"
        .Function = xlAverage
    End With
    Range("A4").Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, False, False, True)
    Columns("C:C").NumberFormat = "0"
End Sub

but I am coming up with the error Run Time Error 438, Object doesn't support this property or method.

Can anyone assist?

Thanks
dvent

**Ron Coderre** · 11-15-2007, 12:17 PM

The below code will build the Pivot Table in cell A1 on a specific sheet (I used Sheet2...You'll need to change those references in the code).
(edited to correct a typo referenced in subsequent posts...Ron)

Sub BuildMyPivotTable()
   ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlDatabase, _
      SourceData:="'Average Deployment'!" & Sheets("Average Deployment").Range("a1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)) _
      .CreatePivotTable _
         TableDestination:="Sheet2!R1C1", _
         TableName:="PivotTable2", _
         DefaultVersion:=xlPivotTableVersion10
         
   With Sheet2.PivotTables("PivotTable2")
      .ColumnGrand = False
      .RowGrand = False
      .PivotFields("Year").Subtotals = Array( _
         False, False, False, False, False, False, False, False, False, False, False, False)
      .AddFields RowFields:=Array("Year", "Quarter")
      With .PivotFields("Number of Days")
         .Orientation = xlDataField
         .Caption = "Average of Number of Days"
         .Function = xlAverage
      End With
    End With
    Sheet2.Range("a2").Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, False, False, True)
End Sub

Does that help?

**dvent** · 11-15-2007, 12:40 PM

Originally Posted by Ron Coderre

The below code will build the Pivot Table in cell A1 on a specific sheet (I used Sheet2...You'll need to change those references in the code).

Sub BuildMyPivotTable()
   ActiveWorkbook.PivotCaches.Add( _
      SourceType:=xlDatabase, _
      SourceData:="'Average Deployment'!" & Sheet("Average Deployment").Range("a1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)) _
      .CreatePivotTable _
         TableDestination:="Sheet2!R1C1", _
         TableName:="PivotTable2", _
         DefaultVersion:=xlPivotTableVersion10
         
   With Sheet2.PivotTables("PivotTable2")
      .ColumnGrand = False
      .RowGrand = False
      .PivotFields("Year").Subtotals = Array( _
         False, False, False, False, False, False, False, False, False, False, False, False)
      .AddFields RowFields:=Array("Year", "Quarter")
      With .PivotFields("Number of Days")
         .Orientation = xlDataField
         .Caption = "Average of Number of Days"
         .Function = xlAverage
      End With
    End With
    Sheet2.Range("a2").Group Start:=True, End:=True, Periods:=Array(False, False, False, _
        False, False, False, True)
End Sub

Does that help?

Ron, I get an error of Compile Error: Sub or Function not defined on Line 4 with SourceData:="'Average Deployment'!" & Sheet("Average Deployment").Range("a1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)) _

**Ron Coderre** · 11-15-2007, 12:45 PM

Originally Posted by dvent

Ron, I get an error of Compile Error: Sub or Function not defined on Line 4 with SourceData:="'Average Deployment'!" & Sheet("Average Deployment").Range("a1").CurrentRegion.Address(ReferenceStyle:=xlR1C1)) _

OOPS! Overzealous editing on my part (sorry)

Change "Sheet" to "Sheets".....without the quotes.

**Ron Coderre** · 11-15-2007, 12:31 PM

Evidently you posted your macro question to the Microsoft Communities:
http://www.microsoft.com/office/comm...e-3ce6b572195f

Now, you've got a whole different pool of talent working on the same issue as the talent in this forum. I'm guessing that I solved your issue with my last post, but those excellent people at the "other forum" will still be spending their valuable time on your issue, when they really should be helping others.

If you feel compelled to do that, you should let both forums know about the other posts (by pasting a link....just like I did, above).

**timbo1957** · 11-15-2007, 10:58 AM

Okay here goes.

In F2 put this calculation
=SUMIF($D$2:$D$512,1,$C$2:$C$512)/COUNTIF($D$2:$D$512,1)

In F3 put this calculation
=SUMIF($D$2:$D$512,2,$C$2:$C$512)/COUNTIF($D$2:$D$512,2)

In F4 put this calculation
=SUMIF($D$2:$D$512,3,$C$2:$C$512)/COUNTIF($D$2:$D$512,3)

In F5 put this calculation
=SUMIF($D$2:$D$512,4,$C$2:$C$512)/COUNTIF($D$2:$D$512,4)

This should give you

145 in F2
170 in F3
187 in F4
149 in F5

How does it work?

=SUMIF($D$2:$D$512,1,$C$2:$C$512)/COUNTIF($D$2:$D$512,1)

The sumif part of the calcualtion looks down the range D2:D512 and looks for the number 1, it then adds all cells in the range C2:C512 which have a 1 in column D identified together = 18263 days.

You then need to divide that figure by the number of times 1 appears in column D. We do this using /COUNTIF($D$2:$D$512,1) = 126.

18263/126 = 145.

Hope that helps.