| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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]) |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How I know if a file is using a custom addin | Excel Discussion (Misc queries) | |||
| How I know if a file is using a custom addin | Excel Worksheet Functions | |||
| Custom Functions saved as addin | Excel Worksheet Functions | |||
| Custom Function | Excel Discussion (Misc queries) | |||
| Emulate Index/Match combo function w/ VBA custom function | Excel Worksheet Functions | |||
Linear Mode
