squall_web//5.0

Weighted averages

Table of values:

Weighted Avg is ((10+15+12)*(100+75+80)) / (100+75+80)

Solution is to sum the fields in SQL like this

Weighted Avg is: (1000+1125+960) / (100+75+80)

Excel has problems actually doing the Grand Total summing in a pivot table for some reason.

Unfortunately you cannot use a User Defined Function in a Calculated Field on a Pivot Table - makes me very sad.

T-Sql:

SELECT SUM(num * den)/SUM(den) AS WeightedAvg
FROM YourTable

SELECT cyear, SUM(num * den)/SUM(den) AS WeightedAvg
FROM YourTable
GROUP BY cyear

SUM(num * den)/SUM(CASE WHEN num IS NULL THEN 0 ELSE den END)

There are also Indexes as a summation tool in Excel, takes weighted averages according to this formula:
KB Article

((value in cell) x (Grand Total)) / ((Grand Row Total) x (Grand Column Total))

Public Function WA(v As Range, d As Variant)
Dim dsum As Double, dwt As Double
For i = LBound(d, 1) To UBound(d, 1)
dsum = dsum + v(i, 1) * d(i, 1)
dwt = dwt + d(i, 1)
Next
If dwt <> 0 Then
WA = dsum / dwt
Else
WA = CVErr(xlErrDiv0)
End If
End Function

Function wa(v As Variant, w As Variant) As Double
Dim aw() As Double, t As Double, x As Variant
Dim sv As Double, sw As Double
Dim nv As Long, nw As Long
'make sure v and w aren't scalars
If Not IsArray(v) Then v = Array(v)
If Not IsArray(w) Then w = Array(w)
nw = 16  'positive initial value - modify as needed
ReDim aw(1 To nw)
nv = 0   'first using nv to count items in w
For Each x In w
nv = nv + 1
If nv >= nw Then
nw = 2 * nw
ReDim Preserve aw(1 To nw)
End If
aw(nv) = x
Next x
nw = nv
ReDim Preserve aw(1 To nw)
nv = 0   'now using nv to count items in v
For Each x In v
nv = nv + 1
t = aw(nv)
If t > 0 Then
sv = sv + x * t
sw = sw + t
End If
Next x
If nv = nw And sw > 0 Then
wa = sv / sw
ElseIf nv <> nw Then
wa = CVErr(xlErrNA)
Else
wa = CVErr(xlErrDiv0)
End If
End Function

Trackback This Post | Subscribe to the comments through RSS Feed