Tough one

[mzsuga]

I have a subtotal but subtotals can only show 1 groupby. I want a column to be average instead. Is there any way to do that? I was thinking maybe finding the word total in column A since subtotal has total in it then inserting a formula in column F to find the average above. This would work for the first subtotal since it can to average to the first row, but how about the other subtotals? Is there a way to average a subtotal until the word subtotal appears?

So if row 500 has the word subtotal in it which totals up row 300-499, then can I average row 300-499 based on the existence of the word subtotal in row 299?

[p45cal]

Consider using a Pivot Table.

[jtl]

Try this out. Assuming that the word "Subtotal" is located in column 1:

[VBA]
Sub sample()

Dim subtotalCol As Integer, averagecol As Integer
Dim firstRowtoAverage As Long, subtotalRow As Long
Dim x As Long, findtotal As Integer

subtotalCol = 2
averagecol = 3
findtotal = WorksheetFunction.CountIf(Columns(1), "Subtotal")

If findtotal <> 0 Then
firstRowtoAverage = 2
For x = 1 To findtotal
subtotalRow = Columns(1).Find(What:="Subtotal", _
After:=Cells(firstRowtoAverage, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Cells(subtotalRow, averagecol).FormulaR1C1 = "=average(R" & firstRowtoAverage & "C" & subtotalCol & ":R" & subtotalRow - 1 & "C" & subtotalCol & ")"
firstRowtoAverage = subtotalRow + 1
Next x
Else
MsgBox ("Subtotal does not exist")
End If
End Sub
[/VBA]

Edit Lucas: VBA tags added to code.

[mzsuga]

Try this out. Assuming that the word "Subtotal" is located in column 1:

[vba]
Sub sample()

Dim subtotalCol As Integer, averagecol As Integer
Dim firstRowtoAverage As Long, subtotalRow As Long
Dim x As Long, findtotal As Integer

subtotalCol = 2
averagecol = 3
findtotal = WorksheetFunction.CountIf(Columns(1), "Subtotal")

If findtotal <> 0 Then
firstRowtoAverage = 2
For x = 1 To findtotal
subtotalRow = Columns(1).Find(What:="Subtotal", After:=Cells(firstRowtoAverage, 1), SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
Cells(subtotalRow, averagecol).FormulaR1C1 = "=average(R" & firstRowtoAverage & "C" & subtotalCol & ":R" & subtotalRow - 1 & "C" & subtotalCol & ")"
firstRowtoAverage = subtotalRow + 1
Next x
Else
MsgBox ("Subtotal does not exist")
End If

End Sub
[/vba]

subtotalCol = 2
averagecol = 3
For that part, the averagecol should equal to 5 since it's in column F, is that right?

Also do you know how to divide by the last cell in column B? =h7/last cell in column B?

Edit: VBA tags added to code.

[lucas]

Both of you seem fairly new to the forum.

If you select your code when posting and hit the green vba button, your code will be formatted for the forum.

[jtl]

-That's correct.
-You can use Find to locate the last non-blank row in your sheet and store the row number into a variable. Example:

[VBA]
lastRow = cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
Cells(x, y).FormulaR1C1 = "=H7/R" & lastRow & "C2"
[/VBA]

[jtl]

yes, pretty new to this site. Thanks for the info lucas!

[lucas]

no problem jtl, enjoy your time here.