An AddIn for a custom function
 

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])

An AddIn for a custom function
 

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

An AddIn for a custom function
 

Thanx Dave!

You are great!

However I don't understand one thing, i.e. what is these two words OPTION
EXPLICIT? I was compiling it as Public Function myRate(Product, ProductRng,
RateRng, Qtyrng). Furthermore what is meant by the phrase "As Double"? Double
was the charateristic of a number wasn't it? Do I need to necessarily declare
the characteristc of a function?

Anyway, I am really thankful dear!
THANX AGAIN!

REGARDS
FARAZ

"Dave Peterson" wrote:

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

An AddIn for a custom function
 

"Option Explicit" tells excel that every variable you use will be declared by
you. If you use a variable that doesn't have a Dim statement (or a Const
or...), then the code won't compile.

It'll help you avoid any typing errors.

You don't need to do either the "option explicit" or declare the function as
double. But since you're returning a number, why wouldn't you declare it as
double?

And finding typing errors can be a pain. I'd give excel as many hints so that
it could help me if I had trouble.

FARAZ QURESHI wrote:

Thanx Dave!

You are great!

However I don't understand one thing, i.e. what is these two words OPTION
EXPLICIT? I was compiling it as Public Function myRate(Product, ProductRng,
RateRng, Qtyrng). Furthermore what is meant by the phrase "As Double"? Double
was the charateristic of a number wasn't it? Do I need to necessarily declare
the characteristc of a function?

Anyway, I am really thankful dear!
THANX AGAIN!

REGARDS
FARAZ

"Dave Peterson" wrote:

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


--

Dave Peterson