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

#### MorleyD

##### New Member
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
Would a Pivot Table be of help?
( billing nrs in col field and average tim ine data field)?

#### Thorin

##### Board Regular
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
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
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

Replies
1
Views
2K
Replies
3
Views
506
Replies
8
Views
258
Replies
3
Views
927
Replies
0
Views
142

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.

### Which adblocker are you using?

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

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