Hi

Your function doesn't return the same value as Diana's
function, when A, B, C or D is zero, and if A, B, C and D
are cell references, your formula returns an error, if one or
more of the cells is empty!?

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"keepitcool" skrev i en meddelelse
...
Diana,

let me add my version too... please!!!



Public Function WeightedAvg#(Optional A, Optional B, _
Optional C, Optional D)

'Created by Diana Popovska on 08/21/03
'edited by keepITcool :)
Dim Weights, Values, noNull%(0 To 3), i%

Weights = Array(0.5, 0.25, 0.15, 0.1)
Values = Array(A, B, C, D)
For i = 0 To 3
If IsError(Values(i)) Then
noNull(i) = 0
Values(i) = 0
Else
noNull(i) = 1
End If
Next

With Application.WorksheetFunction
WeightedAvg = .SumProduct(Weights, Values) / _
.SumProduct(Weights, noNull)
End With
End Function





keepITcool

< email : keepitcool chello nl (with @ and .)
< homepage: http://members.chello.nl/keepitcool


"Diana" wrote:

I just wrote the formula below and it computes the
weighted average of 4 values. If one or more values is
null the % weight is proportionately redistrubuted over
the non null values. The weighted percentages for the
values are as follows:
A=50%
B=25%
C=15%
D=10%

Please, let me know if you could write this in a more
efficient way.

Public Function WeightedAvg(A, B, C, D)
'Created by Diana Popovska on 08/21/03

Dim ValueA, ValueB, ValueC, ValueD, Total As Double

'Weighted percentages by default
<SNAP