First, if you use VBA/UDF to do this and you have lots of these formulas, you'll
find that recalculating takes longer.

Second, if you're using xl2007, you may want to look at =iferror(). The formula
won't be duplicated.

But this did work for me:

Option Explicit
Function myRate(Product, ProductRng, RateRng, Qtyrng) As Double

Dim res As Variant
Dim myFormula As String
myFormula = "sumproduct(--(" & ProductRng.Address(external:=True) _
& "=" & """" & Product & """" & ")," _
& RateRng.Address(external:=True) & "," _
& Qtyrng.Address(external:=True) & ")/" _
& "sumproduct(--(" & ProductRng.Address(external:=True) _
& "=" & """" & Product & """" & ")," _
& "--(" & RateRng.Address(external:=True) _
& "0)," _
& Qtyrng.Address(external:=True) & ")"

res = Application.Evaluate(myFormula)

If IsError(res) Then
res = 0
End If

myRate = res

End Function

FARAZ QURESHI wrote:

I often come across using a lengthy formula for a data like:

Product Rate Qty
xxxx xx xxx
xxxx xx xxx
xxxx xx xxx
xxxx xx xxx

to calculate the Weighted Average Rate where the conditions are that:

1. Product is "ProductA";
2. Rate exists; and
3. Return "0" to avoid a #DIV/0! error if no such product exists.

Such a formula is:

=IF(ISERR(
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B100),C2:C10)),0,
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B100),C2:C10))

Now I want to create an add-in for a function to be uploaded everytime I
open Excel.

What would be the appropriate piece of code for a function and arguments like:
MyRate(Product, Range1[Product List], Range2[Rate List], Range3[Qty List])

--

Dave Peterson