Subtotals and Weighted Averages

jslipher

New Member
Joined
Jul 27, 2011
Messages
22
I have a set of data that I ran Excel's embedded Subtotals function in the Data tab and used the Sum function in column "I" for each change in column "B". I am wanting to do a weighted average of column "L" based on column "I". I found this set of code in the forum that should take of this:

Sub test()

For Each c In Intersect(ActiveSheet.UsedRange, ActiveSheet.Range("I:I"))
If Left(c.FormulaR1C1, 4) = "=SUB" Then
c.Offset(0, 3).Formula = "=(SUMPRODUCT(" & c.Precedents.Address & "*" & c.Precedents.Offset(0, 3).Address & ")/SUM(" & c.Precedents.Address & "))"
End If
Next


End Sub

Unfortunately instead of returning =SUMPRODUCT(I:I*L:L)/SUM(I:I) it is returning =SUMPRODUCT(I:I,A:A*L:L,D:D)/SUM(I:I,D:D). Does anyone know why it is doing this? Thanks ahead of time!
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Just a guess as I'm not an expert on Precedents;
Are your values in I or L by chance references back to Col A?
 
Upvote 0
One other thing to note. The data in column I and column L is a VLOOKUp function, looking up column A. I assume this is why the Precedents VBA function returns column A as well. Is there anyway to bypass this?
 
Upvote 0
back to 'should work' status (got flustered in testing :-) )

Code:
            'c.Offset(0, 3).Formula = "=(SUMPRODUCT(" & c.Precedents.Address & "*" & c.Precedents.Offset(0, 3).Address & ")/SUM(" & c.Precedents.Address & "))"
            
            AddressFromSubT$ = Mid(c.Formula, 13, InStr(1, c.Formula, ")", vbTextCompare) - 13)
            c.Offset(0, 3).Formula = "=(SUMPRODUCT(" & AddressFromSubT$ & "*" & Range(AddressFromSubT$).Offset(0, 3).Address & ")/SUM(" & AddressFromSubT$ & "))"
 
Upvote 0

Forum statistics

Threads
1,226,730
Messages
6,192,711
Members
453,748
Latest member
akhtarf3

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
Back
Top