User-Defined Functions

Chris
I've just looked at this again. I think what I posted only gives the simple average.
I can't follow your code. What is "Weight"? Is it the range of cells that contains the weighting for each cell in "Notional"? If so, I think the following does it :-

Option Explicit
Dim Notional As Range
Dim Weight As Range
Function WAverage(Notional, Weight)
Application.Volatile True
With Application.WorksheetFunction
WAverage = .SumProduct(Notional, Weight) / .Sum(Weight)
End With
End Function

This is the equivalent of :-
=SUMPRODUCT(A1:A5,B1:B5)/SUM(B1:B5)

The function would be entered as :-
=WAverage(A1:A5,B1:B5)

Perhaps I'm misunderstanding what you need?

Celia

You are correct. "Weight" is the range of cells that contains the weighting for each cell in "Notional". The caveat, however, is that "Weight" will contain text that I need to convert. Thus, my rational for using "Select Case".

The most recent code is exactly what I am looking for with the exception of creating the flexibility to reference text, which then cross-references a constant.

You have been EXTREMELY helpful. I greatly appreciate your assistance.

I am receiving the #VALUE! error, when the arguments are referencing a range in a closed file. Below is the code. I'm simply trying to write a weighted average function. Any suggestions would be greatly appreciated. Thanks in advance for your time and consideration.

Option Explicit
Dim Notional()
Dim Weight()

Function WAverage(Notional, Weight)

Application.Volatile True

Dim i As Integer
Dim x As Long
Dim y As Long

Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4

x = 0
y = 0
WAverage = 0

For i = 1 To Notional.Cells.Count
x = x + Notional(i)
Select Case Weight(i)
Case Is = "a"
y = y + Notional(i) * a
Case Is = "b"
y = y + Notional(i) * b
Case Is = "c"
y = y + Notional(i) * c
Case Is = "d"
y = y + Notional(i) * d
Case Else
y = y + Notional(i) * 0
End Select

Next i

WAverage = y / x

End Function

Chris

Option Explicit
Dim Notional As Range
Dim Weight As Range
Function WAverage(Notional, Weight)
Application.Volatile True
Dim i As Integer, x As Integer, y As Integer
Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4
For i = 1 To Weight.Cells.Count
Select Case Weight(i)
Case Is = "a"
x = x + a
y = y + Notional(i) * a
Case Is = "b"
x = x + b
y = y + Notional(i) * b
Case Is = "c"
x = x + c
y = y + Notional(i) * c
Case Is = "d"
x = x + d
y = y + Notional(i) * d
End Select
Next
WAverage = y / x
End Function

Celia

Chris
I think the following eliminates the #VALUE error.
I have indicated where changes have been made (only in two places).

Function WAverage(Notional, Weight)

Application.Volatile True

Dim i As Integer
Dim x As Long
Dim y As Long

Const a As Integer = 1
Const b As Integer = 2
Const c As Integer = 3
Const d As Integer = 4

x = Notional.Cells.Count 'CHANGED
y = 0
WAverage = 0

For i = 1 To Notional.Cells.Count
Select Case Weight "CHANGED
Case Is = "a"
y = y + Notional(i) * a
Case Is = "b"
y = y + Notional(i) * b
Case Is = "c"
y = y + Notional(i) * c
Case Is = "d"
y = y + Notional(i) * d
Case Else
y = y + Notional(i) * 0
End Select

Next i

WAverage = y / x

End Function

Post again if it still doesn't work.
Celia

Chris
If you are getting the error #VALUE, post your code so it can be checked.
If you are getting the error #NAME, the UDF is not beinng called properly. Is this the error you are getting?
Celia