Posted to microsoft.public.excel.programming
|
|
Is there more efficient formula?
If all the values are non zero ones then your function
works great. I will check out what corrections you have
suggested in your next post. Thanks.
-----Original Message-----
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
.
|