Nothing like a good night's sleep!

I agree, I finally got a good night sleep too.

This formula looks great (a bit over my head at first
glance) but I probably won't need it since A, B, C and D
values will never be negative or null ( I have
preprogrammed them). I tried it but since I don't really
know how to work it ( I plugged in the a, b, c, d range
for wa()) and got a value#.

Here's another option, which works for all
numbers, positive, negative, zero and empty cells.
For more than 4 weights, just add to Array(0.5, 0.25,
0.15, 0.1)

Function Wa(ParamArray A() As Variant)
'Leo Heuser, 22 Aug. 2003
Dim Counter As Long
Dim Total As Double
Dim WeightPercent As Variant

'Weighted percentages by default
WeightPercent = Array(0.5, 0.25, 0.15, 0.1)

For Counter = LBound(A) To UBound(A)
Total = Total + WeightPercent(Counter) * (A
(Counter) < 0)
Next Counter

For Counter = LBound(A) To UBound(A)
Wa = Wa + WeightPercent(Counter) / Total * _
A(Counter) * (A(Counter) < 0)
Next Counter

End Function

If the values are in a contiguous range and the weights
are
in a contiguous range, it's not necessary to use a UDF.

This formula will do the job, assuming values in B23:B26
and weights in A23:A26. Works for all numbers and empty
cells.

=SUMPRODUCT(B23:B26*A23:A26/SUMPRODUCT((B23:B26<0)
*A23:A26))

--
Best Regards
Leo Heuser
MVP Excel

Followup to newsgroup only please.

"Leo Heuser" skrev i en
meddelelse
...
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.




.