Function WeightedAverage(Range1, Range2)
Dim x As Long, j As Long
Dim SumProduct As Double, Total As Double
Dim rng1 as Range, rng2 as Range
For x = 1 To Range1.Areas.Count
Set rng1 = Range1.Areas(x)
Set rng2 = Range2.Areas(x)
If rng1.Count < rng2.Count Then
WeightedAverage = CVErr(xlErrRef)
Exit Function
End If
For j = 1 To rng1.Count
If Not IsError(rng1(j)) And _
Not IsError(rng2(j)) And Not _
IsEmpty(rng1(j)) And Not _
IsEmpty(rng2(j)) Then
SumProduct = SumProduct + rng1(j) * rng2(j)
Total = Total + rng1(j)
End If
Next j
Next x

WeightedAverage = SumProduct / Total
End Function


--
Regards,
Tom Ogilvy




"Brian" wrote in message
...
I've created the following formula to calculate the weighted average a two
ranges.

Function WeightedAverage(Range1, Range2)
Dim x As Integer
Dim SumProduct, Total As Double
For x = 1 To Range1.Cells.Count
If Range1.Cells(x) < "N/A" And Range2.Cells(x) < "N/A" And Not
IsEmpty(Range1.Cells(x)) And Not IsEmpty(Range2.Cells(x)) Then
SumProduct = SumProduct + Range1.Cells(x) * Range2.Cells(x)
Total = Total + Range1.Cells(x)
End If
Next x
WeightedAverage = SumProduct / Total
End Function

The formula works well if a I use two single ranges. For example,
WeightedAverge(a1:a5,b1:b5). However, I want to convert the formula to
accept multiple ranges. For example:
WeightedAverage((a1:a5,a10:a15,a20:a25),(b1:b5,b10 :b15,b20:b25).

Does anyone have any ideas on how to covert this function? Thanks.