You can simplify this type of calculation by using array variables. It also makes it easy to extend the calculation for more input
values or generalise it for any number of input values. The following illustrates the idea:

Sub Test()
Dim Value(1 To 4)
Dim A(1 To 4)
Dim Total As Double
Dim TotalA As Double
Dim WeightedAvg As Double
Dim i As Integer

Value(1) = 0.5
Value(2) = 0.25
Value(3) = 0.15
Value(4) = 0.1

A(1) = 10
A(2) = 0
A(3) = 5
A(4) = 30

For i = 1 To 4
TotalA = TotalA + A(i) * Value(i)
If A(i) 0 Then Total = Total + Value(i)
Next i

WeightedAvg = TotalA / Total

MsgBox WeightedAvg

End Sub


--

John Green - Excel MVP
Sydney
Australia


"Diana" wrote in message ...
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