An AddIn for a custom function

G

Guest

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...
 
G

Guest

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

Can I reference =, <, or > sign in SUMPRODUCT 7
Need formula help for Excel 8
HOW TO COUNT VALUES BASED ON MULTIPLE CRITERIA IN EXCEL? 2
use cell text in lookup function 3
Sum function with asterisks 3
SHORTEN A FORMULA - AVOIDING AN EXTRA COLUMN 1
Is there an such a function? 2
SHORTEN A FORMULA BY AVOIDING SETTING UP AN ADDITIONAL COLUMN 4

Top