Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call VBA Gurus - Help to Optimise two small UDF's
I have written two small VBA UDF's to solve a problem that I could not
solve through standard excel functions. I would love to have some guru feedback on speeding them up, as they are going to be used very heavily. Here is sample data, no macros so u can safely open with macros disabled (The spreadsheet has some comments to clarify what I am doing): http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF And here is a text file containing the two functions: http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248 Thanks In Advance Orekin |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call VBA Gurus - Help to Optimise two small UDF's
Orekin ,
I have written two small VBA UDF's to solve a problem that I could not solve through standard excel functions. Good News! these CAN simply be done in standard worksheetfunction.. it's no more then a multi column lookup.. enter in c2: =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21) copy it down the column.. and you're done :) of course you could make it nicer by adding some names... names define.. CNnrs = offset($h$1,1,0,counta($h:$h)-1,1) CNamt = offset(cnnrs,0,1) now in c2 = SUMPRODUCT(--(A2=cnnrs),cnamt) excel can be simple.. you just gotta learn how:) <vbg keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q for KeepITCool /
-- converts the boolean to number and is called a UNARY minus.
keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "ALEX" wrote: In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21) What does (--($A2=$H$2:$H$21) mean??? "orekin" .com... I have written two small VBA UDF's to solve a problem that I could not solve through standard excel functions. I would love to have some guru feedback on speeding them up, as they are going to be used very heavily. Here is sample data, no macros so u can safely open with macros disabled (The spreadsheet has some comments to clarify what I am doing): http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF And here is a text file containing the two functions: http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248 Thanks In Advance Orekin |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q for KeepITCool /Again
arghh :)
sumproduct multi column lookup comes up in 5% of all questions here ... it compares a2 with all cells in h2:h21 --(a2=h2:h21) produces a series of zeros and ones.. sumproduct(--(a2=h2:h21),i2:i21) multiplies that series of 0/1 with value in i2:i21 and sum it... keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "ALEX" wrote: OK, then what does the following do? Can you elaborate? $A2=$H$2:$H$21 "keepITcool" дÈëÏûÏ¢ÐÂÎÅ ... -- converts the boolean to number and is called a UNARY minus. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "ALEX" wrote: In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21) What does (--($A2=$H$2:$H$21) mean??? "orekin" .com... I have written two small VBA UDF's to solve a problem that I could not solve through standard excel functions. I would love to have some guru feedback on speeding them up, as they are going to be used very heavily. Here is sample data, no macros so u can safely open with macros disabled (The spreadsheet has some comments to clarify what I am doing): http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF And here is a text file containing the two functions: http://s11.yousendit.com/d.aspx? id=F70160A5983AF60BEDB0DD29262F7248 Thanks In Advance Orekin |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Q for KeepITCool /Again
Just butting in...
Try this formula in another cell: =SUMPRODUCT(--($A2=$H$2:$H$6),$I$2:$I$6) (just a smaller range) Now highlight ($A2=$H$2:$H$6) in the formula bar and hit F9. You'll see something like: =SUMPRODUCT(--{FALSE;TRUE;FALSE;TRUE;FALSE},$I$2:$I$6) Now highlight the last minus through the closing right curly bracket -{FALSE;TRUE;FALSE;TRUE;FALSE} and hit F9 You'll see something like: =SUMPRODUCT(-{0;-1;0;-1;0},$I$2:$I$6) Now the same for: -{0;-1;0;-1;0} and hit f9 you'll see: =SUMPRODUCT({0;1;0;1;0},$I$2:$I$6) Since =sumproduct() likes to deal with numbers, you had to convert those true/falses to positive 1's and 0's. The first unary minus converted true/falses to -1/0's and the second one made the -1's, +1's. ===== And if I highlight $i$2:$6 and hit f9, I get: (with my test data) =SUMPRODUCT({0;1;0;1;0},{3;3;5;2;0}) The I get 0*3 + 1*3 + 0*5 + 1*2 + 0*0 or just 5. Remember to hit escape or edit|undo so you don't screw up your real formula when evaluating this way. ALEX wrote: OK, then what does the following do? Can you elaborate? $A2=$H$2:$H$21 "keepITcool" дÈëÏûÏ¢ÐÂÎÅ ... -- converts the boolean to number and is called a UNARY minus. keepITcool < email : keepitcool chello nl (with @ and .) < homepage: http://members.chello.nl/keepitcool "ALEX" wrote: In =SUMPRODUCT(--($A2=$H$2:$H$21),$I$2:$I$21) What does (--($A2=$H$2:$H$21) mean??? "orekin" .com... I have written two small VBA UDF's to solve a problem that I could not solve through standard excel functions. I would love to have some guru feedback on speeding them up, as they are going to be used very heavily. Here is sample data, no macros so u can safely open with macros disabled (The spreadsheet has some comments to clarify what I am doing): http://s2.yousendit.com/d.aspx?id=B0...196A729CFE93FF And here is a text file containing the two functions: http://s11.yousendit.com/d.aspx?id=F...B0DD29262F7248 Thanks In Advance Orekin -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Call VBA Gurus - Help to Optimise two small UDF's
Thanks!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
UDF's | Excel Discussion (Misc queries) | |||
UDF's & puzzling recalculation behavior | Excel Worksheet Functions | |||
UDF's in Excel '07 | Setting up and Configuration of Excel | |||
select numbers from a list and add to optimise result | Excel Worksheet Functions | |||
UDF's using other UDF's | Excel Worksheet Functions |