user defined function

[tomas]

Hi

I am trying to build my first custom function. It returns a value error. I thought the error might be the type of data for my arguments. I tried range, long variant but still value error.

Function weightedaverage(array1 As Range, array2 As Range, array3 As Range) as long


Dim client As Long
Dim panel As Long

client = Application.WorksheetFunction.SumProduct(array1, array3)
panel = Application.WorksheetFunction.SumProduct(array2, array3)

weightedaverage = Round((client / panel - 1) * 100, 2)

End Function

 

[Hui]

Tomas
I've never seen this formula for Weighted Averages, but...

Your code works fine with simple data

1. Is it in a Code Module ?
2. Are the ranges of equal sizes ?
3. Are all the values In ranges 1, 2 & 3 all values ?

Can you post a sample file ?

 

[Hui]

Also I think the Function line should be:
Function weightedaverage(array1 As Range, array2 As Range, array3 As Range) As Double

because
weightedaverage = Round((client / panel - 1) * 100, 2) will round a number to 2 decimals
which will get lost in the translation to a Long

eg:


and

 

[tomas]

Tomas
I've never seen this formula for Weighted Averages, but...

Your code works fine with simple data

1. Is it in a Code Module ?
2. Are the ranges of equal sizes ?
3. Are all the values In ranges 1, 2 & 3 all values ?

Can you post a sample file ?

Hi Hui

thanks for reply

weighted average is just a working name better would be client coefficient. In aa attachement in cell C 4 is final result, which is result of two steps and I tried to get this result in cell d4 with help of custom function

 

[Hui]

Change your code as below:

Function weightedaverage(array1 As Range, array2 As Range, array3 As Range) As Double

Dim client As Double
Dim panel As Double

client = Application.WorksheetFunction.SumProduct(array1, array3)
panel = Application.WorksheetFunction.SumProduct(array2, array3)

weightedaverage = Round((client / panel - 1) * 100, 2)

End Function

Making Client and Panel Long rounds the values to 0

 

[Hui]

Also the formula should be used as:
=weightedaverage(A2:A3,B2:B3,C2:C3)

You had:
=weightedaverage(A2:A3,B2:B3,C2:C4)

The ranges must be the same length

 

[tomas]

Change your code as below:

Function weightedaverage(array1 As Range, array2 As Range, array3 As Range) As Double

Dim client As Double
Dim panel As Double

client = Application.WorksheetFunction.SumProduct(array1, array3)
panel = Application.WorksheetFunction.SumProduct(array2, array3)

weightedaverage = Round((client / panel - 1) * 100, 2)

End Function

Making Client and Panel Long rounds the values to 0

thanks now it's working. I am aware of ranges, just mistyped.