Good morning!


Nothing like a good night's sleep!

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.