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

MorleyD

New Member
Joined
Jul 31, 2007
Messages
2
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..
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

arthurbr

Well-known Member
Joined
Dec 8, 2006
Messages
2,496
Office Version
  1. 365
  2. 2010
Would a Pivot Table be of help?
( billing nrs in col field and average tim ine data field)?
 

Thorin

Board Regular
Joined
Feb 8, 2007
Messages
243
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

New Member
Joined
Jul 31, 2007
Messages
2
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

Board Regular
Joined
Feb 8, 2007
Messages
243
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 :-

Code:
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 :-

Code:
=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 :-

Code:
=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
 

Forum statistics

Threads
1,181,412
Messages
5,929,782
Members
436,689
Latest member
IbraheemWard

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top