An AddIn for a custom function

F

FARAZ QURESHI

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:B10>0),C2:C10)),0,
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B10>0),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])
 
D

Dave Peterson

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 said:
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:B10>0),C2:C10)),0,
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B10>0),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])
Click to expand...
 
F

FARAZ QURESHI

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 said:
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 said:
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:B10>0),C2:C10)),0,
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B10>0),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])
Click to expand...
Click to expand...
 
D

Dave Peterson

"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 said:
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 said:
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 said:
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:B10>0),C2:C10)),0,
SUMPRODUCT(--(A2:A10="ProductA"),B2:B10,C2:C10)/
SUMPRODUCT(--(A2:A10="ProductA"),--(B2:B10>0),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])
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Need formula help for Excel 8
Can I reference =, <, or > sign in SUMPRODUCT 7
SHORTEN A FORMULA - AVOIDING AN EXTRA COLUMN 1
use cell text in lookup function 3
SHORTEN A FORMULA BY AVOIDING SETTING UP AN ADDITIONAL COLUMN 4
Is there an such a function? 2
Sum function with asterisks 3
How to write a function for column A = product of two columns, B a 5

Top