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. |
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. |
All times are GMT +1. The time now is 10:19 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com