Creating Formulas That Accept Multiple Ranges
 

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.

Creating Formulas That Accept Multiple Ranges
 

You need to be aware that there is a nasty Excel bug discovered by Howard
Kaikow with multi-area range arguments and UDFs.
You should only use them if you are sure that your workbook will never
contain more than one worksheet.

See UDF Areas bug at http://www.DecisionModels.com/downloads.htm

(There is an extremely ugly bypass there as well)

Charles
______________________
Decision Models
The Excel Calculation Site.
www.DecisionModels.com

"Brian" wrote in message
...
Thanks a million! I was hurting my brain trying to figure this one out.

"Tom Ogilvy" wrote:

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.