[Akuller]

Hi, I'm using Excel 2003, and I've built a macro to do various things, one of them to add subtotals based on the change in data. I simply used the function under Data>subtotals to do the summing but I need one column to sum and then divide by the column next to it in order to give a weighted average.

I do not know VBA, but is there an easy way to do this?

Thanks!

[Zack Barresse]

Hi there,

Do you have a sample of your data? And can you please post your code? If the data is not your data, please make sure the structure is equivalent. But yes, to answer your question, there is a way to do it, and it should be fairly easy.

[Akuller]

Current macro:

Cells.Select
With Selection
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Rows("1:8").Select
Selection.Delete Shift:=xlUp
Range("O1").Select
ActiveSheet.Shapes("CwusLogo.jpeg").Select
Selection.Delete
Cells.Select
Range("F1").Activate
Selection.Sort Key1:=Range("F2"), Order1:=xlAscending, Key2:=Range("J2") _
, Order2:=xlAscending, Key3:=Range("K2"), Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:= _
xlSortTextAsNumbers
Range("A3").Select
Selection.End(xlUp).Select
Range("A2").Select
Selection.End(xlDown).Select
ActiveWindow.SmallScroll Down:=18
Rows("4851:5971").Select
Selection.ClearContents
Columns("S:S").Select
Selection.Insert Shift:=xlToRight
Range("S1").Select
ActiveCell.FormulaR1C1 = "^Wt. Avg"
Range("S2").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*RC[-2]"
Range("S2").Select
Selection.AutoFill Destination:=Range("S2:S4850")
Range("S2:S4850").Select
Columns("S:S").EntireColumn.AutoFit
Range("K7").Select
Selection.Subtotal GroupBy:=9, Function:=xlSum, TotalList:=Array(14, 15, 16, _
17, 19), Replace:=True, PageBreaks:=False, SummaryBelowData:=True
Range("A1").Select


Would now like to make the change to all subtotals under Column "S", WT. Avg, to have that subtotal divided by subtotal in column Q, amount. So the subtotal for Wt. Avg (548.28)/subtotal for amount (33.16) should give wt.avg of (16.53). It is the 16.53 I'd like to see, even if I have to insert a new column next to Wt. Avg (T). I'd like this to calculate within the macro at each section that has a subtotal.
Q R S T
amount amt.per Wt. Avg
10.36 16.3 168.868
13.39 16.6 222.274
9.41 16.7 157.147
33.16 548.289 16.5

Let me know if this is asking too much...thanks!

[Zack Barresse]

Do you have a sample file you can post? Data would help...