Nii-ce! Thanks alot.

-----Original Message-----
Diana

How about this cut-down version :-)

The function can easily be changed to cover more
than 4 values. It's just a matter of adding lines.

Assuming, as does your example, that only positive
numbers and/or 0 are used.

Function WeightedAvg(A As Double, B As Double, C As
Double, D As Double)
'Leo Heuser, 22 Aug. 2003
Dim ValueA As Double
Dim ValueB As Double
Dim ValueC As Double
Dim ValueD As Double
Dim Total As Double

'Weighted percentages by default
ValueA = 0.5
ValueB = 0.25
ValueC = 0.15
ValueD = 0.1

Total = _
ValueA * (A 0) + _
ValueB * (B 0) + _
ValueC * (C 0) + _
ValueD * (D 0)

WeightedAvg = _
ValueA / Total * A * (A 0) + _
ValueB / Total * B * (B 0) + _
ValueC / Total * C * (C 0) + _
ValueD / Total * D * (D 0)

End Function


In the line

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

only Total is dimensioned as a double. The other
variables
are dimensioned as Variant. In order to have all
dimensioned
as Doubles, you have to put them on separate lines as
shown
in my function or declare each one explicitly:

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

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Diana" skrev i en meddelelse
...
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
ValueA = 0.5
ValueB = 0.25
ValueC = 0.15
ValueD = 0.1

'If a percentage is higher than 1, only 100% is counted
in the formula.
'It is already applied to A, B, C and D locally in
excel.


'There are 16 possible combinations of A, B, C & D
(2*2*2*2)
'Each case is examined below. 1 notes not null. 0
notates
null.

'1111 - CASE 1
If A 0 And B 0 And C 0 And D 0 Then
WeightedAvg = ValueA * A + ValueB * B + ValueC * C
+
ValueD * D
'0111 - CASE 2
ElseIf A = 0 And B 0 And C 0 And D 0 Then
Total = ValueB + ValueC + ValueD
WeightedAvg = (ValueB / Total) * B + (ValueC /
Total)
* C + (ValueD / Total) * D
'0011 - CASE 3
ElseIf A = 0 And B = 0 And C 0 And D 0 Then
Total = ValueC + ValueD
WeightedAvg = (ValueC / Total) * C + (ValueD /
Total)
* D
'0001 - CASE 4
ElseIf A = 0 And B = 0 And C = 0 And D 0 Then
Total = ValueD
WeightedAvg = (ValueD / Total) * D
'0101 - CASE 5
ElseIf A = 0 And B 0 And C = 0 And D 0 Then
Total = ValueB + ValueD
WeightedAvg = (ValueB / Total) * B + (ValueD /
Total)
* D
'0100 - CASE 6
ElseIf A = 0 And B 0 And C = 0 And D = 0 Then
Total = ValueB
WeightedAvg = (ValueB / Total) * B
'0010 - CASE 7
ElseIf A = 0 And B = 0 And C 0 And D = 0 Then
Total = ValueC
WeightedAvg = (ValueC / Total) * C
'0000 - CASE 8
ElseIf A = 0 And B = 0 And C = 0 And D = 0 Then
WeightedAvg = 0
'1000 - CASE 9
ElseIf A 0 And B = 0 And C = 0 And D = 0 Then
Total = ValueA
WeightedAvg = (ValueA / Total) * A
'1100 - CASE 10
ElseIf A 0 And B 0 And C = 0 And D = 0 Then
Total = ValueA + ValueB
WeightedAvg = (ValueA / Total) * A + (ValueB /
Total)
* B
'1110 - CASE 11
ElseIf A 0 And B 0 And C 0 And D = 0 Then
Total = ValueA + ValueB + ValueC
WeightedAvg = (ValueA / Total) * A + (ValueB /
Total)
* B + (ValueC / Total) * C
'1010 - CASE 12
ElseIf A 0 And B = 0 And C 0 And D = 0 Then
Total = ValueA + ValueC
WeightedAvg = (ValueA / Total) * A + (ValueC /
Total)
* C
'1011 - CASE 13
ElseIf A 0 And B = 0 And C 0 And D 0 Then
Total = ValueA + ValueC + ValueD
WeightedAvg = (ValueA / Total) * A + (ValueC /
Total)
* C + (ValueD / Total) * D
'1101 - CASE 14
ElseIf A 0 And B 0 And C = 0 And D 0 Then
Total = ValueA + ValueB + ValueD
WeightedAvg = (ValueA / Total) * A + (ValueB /
Total)
* B + (ValueD / Total) * D
'1001 - CASE 15
ElseIf A 0 And B = 0 And C = 0 And D 0 Then
Total = ValueA + ValueD
WeightedAvg = (ValueA / Total) * A + (ValueD /
Total)
* D
'0110 - CASE 16
ElseIf A = 0 And B 0 And C 0 And D = 0 Then
Total = ValueB + ValueC
WeightedAvg = (ValueB / Total) * B + (ValueC /
Total)
* C
End If


End Function




.