I am so excited to see your responses today, and I can't
wait to check out the formulas this weekend. Thank you.
You guys rock. I hope your intelligence rubs off on my
future formula writing...

-----Original 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

.