Help requested- How to run an average of a Subtotal function

[MorleyD]

I have a list of time sequences related to a billing number. I have used the subtotal function to get a time total for each billing number. I am trying to get an average of time spent on each bill. When I use the average function, the range selected contains all the entries and not just the subtotal entries, so the calculation does not give me the correct answer. All suggestions are be greatly appreciated..

 

[arthurbr]

Would a Pivot Table be of help?
( billing nrs in col field and average tim ine data field)?

 

[Thorin]

You can use the =SUBTOTAL function to do this one, intead of using 9 for SUM, use 1 for AVERAGE.

=SUBTOTAL(1,C2:C15)

Where C2:C15 is the total range, it will ignore any previous SUBTOTALS in the range and average the rest.

 

[MorleyD]

Request for help - Subtotal averages

Thanks Andy.

Your solution does help but it is opposite of the data I need. I need to average the subtotals not the data adding up to the subtotals.

 

[Thorin]

Morley,

I did come up with a way of calculating the average of SUBTOTAL calculations, but it seems a little long winded, I am posting it anyway in the hope that someone else can use this for you and find a way of making it work a little easier.

First I created a UDF in VBA :-

Function CELLFORMULA(cell) As String
'   Returns the formula in cell, or an
'   empty string if cell has no formula
    Dim UpperLeft As Range
    Set UpperLeft = cell.Range("A1")
    If UpperLeft.HasFormula Then
        CELLFORMULA = UpperLeft.Formula
    Else
        CELLFORMULA = ""
    End If
End Function

Then I inserted a column to the left of the amounts that contain the SUBTOTAL functions (you can always hide it later), then in the top cell (B4 in this case) type in :-

=RIGHT(LEFT(CELLFORMULA(C4),9),8)

Where C4 is the first cell in the range, then copy this formula down to the bottom of your range.

Lastly the following formula will give your average :-

=SUMIF(B:B,"SUBTOTAL",C:C)/COUNTIF(B:B,"subtotal")

As I said, seems a little long winded, there maybe a way to do without the inserted column B and combine it all in one formula.

HTH