Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex excel formula Array how do I convert it to a vba Function
I have an Array Formula:{=SUM(IF((B2:B7="New") * (D2:D7="Test1")*(A2:A7="Band
A"),C2:C7*E2:E7)) + SUM(IF((B2:B7="New") * (F2:F7="Test1")*(A2:A7="Band A"),C2:C7*G2:G7))} in a cell, I have managed to alter this for use in VBA to change variables eg """ & Var & """ but want to do it as a WorksheetFunction so I don't have to add the formula to cells to get the answer. thank you for any help you can give. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
complex excel formula Array how do I convert it to a vba Function
Rob wrote...
I have an Array Formula:{=SUM(IF((B2:B7="New") * (D2:D7="Test1") *(A2:A7="Band A"),C2:C7*E2:E7)) + SUM(IF((B2:B7="New") * (F2:F7="Test1")*(A2:A7="Band A"),C2:C7*G2:G7))} in a cell, . . . You could simplify this to the nonarray formula =SUMPRODUCT((A2:A7="Band A")*(B2:B7="New")*C2:C7*{1,0,1}, --(D2:F7="Test1"),E2:G7) . . . I have managed to alter this for use in VBA to change variables eg """ & Var & """ but want to do it as a WorksheetFunction so I don't have to add the formula to cells to get the answer. So you want to make this a user-defined function you could call from cell formulas? You'd be better off using your original formula or the alternative I gave. Udfs are slow. Also, unless you add a fair amount of error checking to udfs, they only return #VALUE! errors when anything goes wrong. Your formula above would return a somewhat more useful #N/A if one of the ranges spanned a different number of rows than the other. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function or formula to convert "text" month to number of month | Excel Discussion (Misc queries) | |||
Excel should have a formula to convert number into words | Excel Worksheet Functions | |||
Formula checking multiple worksheets | Excel Worksheet Functions | |||
CONVERT Function Disappered in Excel | Excel Discussion (Misc queries) | |||
Excel: Add "dBm" and "dBW" to CONVERT worksheet function Power u. | Excel Worksheet Functions |