Sumproduct Utility - willing testers required

[DonkeyOte]

This is I guess a bit of a duped thread but I kicked off a post a while back about building a utility to analyse Sumproduct Formulae ... given that the post now has 150 posts and over a 1000 hits (made up by about 5 of us!) I'm guessing the majority of MrE people won't be following it (I tend to avoid those as I know it's being dealt with by other people!) ... post is here: http://www.mrexcel.com/forum/showthread.php?t=348519

Anyway to cut a long story short I have now finally put the beta version of this code into a bog standard .xla file... the things is I now really need as many people to test out for me as possible so I can be sure it's working ... it has worked thus far on anything myself & Emma (CG) have thrown at it but you never know... therefore the more of you willing to do me a favour and test it out the better

The .xla file can be downloaded from here:

http://www.box.net/shared/6s1rdmtob0

There is also a sample .xls file which you can download if you wish, this file contains a mixture of sumproduct formulae (in various guises in green cells) on which the .xla can be tested (and output reviewed):

http://www.box.net/shared/73mg4fdj4t

In essence once the add-in has been installed you will get a very basic floating toolbar with "Analyse Sumproduct" on it... if you highlight any Sumproduct formula and hit the button you should get a new sheet appear with the breakdown of how the formula was computed in terms of a numerical matrix... should the "component" arrays be multi dimensional (ie > 1 column) you will find the answer column is itself a Sumproduct formula and can be drilled into also.

In short this is meant more as a training aid to help people understand how sumproduct works and the basic premise behind it was to automate xld's illustrations on the subject which can be found here:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

I personally will also find it useful to audit formulae etc where I'm getting unexpected results etc or if I am receiving error result etc...

Big thanks to Emma, Rory & Richard for their help on this and also xld who is doing some work on further developing the concept. I should add that without both Rory & Richard's expert insight in terms of some of the fundamentals (like how to really use the VBA Evaluate function!) this would never have got off the ground.

Thanks for reading and thanks also in advance to anyone who provides feedback be it good or bad.

Cheers,
Luke

 

[Jonmo1]

That is really cool Luke. It will definately be usefull, not only to show people how it works, but to troublehsoot as well. You can see which rows are not returning what you think they should. VERY cool tool. Good job.

 

[PatPat]

Thanks for this excellent product. Where would you like us to post feedback on it?

 

[DonkeyOte]

Jonmo1 -- thanks !

PatPat -- here is fine as long as it's not too offensive

 

[PatPat]

I have used the product very quickly so please excuse me if this is covered somewhere in the literature which I haven't had a chance to fully read.

I would suggest that you add a row number where the calculation is based as with big sheets I found it a bit time consuming to extrapolate the row from the calculation number.

Although it helped me find an issue that had been pulling my hair out for a long time, I would find a facility which highlights/lists or otherwise shows any error results in the calculations. i.e. not true or false results as with complex worksheets using calculations dependent on many other calculations and cells, a single error value in one cell can invalidate the sumproduct result. To find this error value is a nightmare.

Hope this makes sense.

Thanks once again though for an excellent addition

 

[DonkeyOte]

...literature...

Ahem...

Thanks for the feedback -- of all of which I agree with... the issue I had when I was looking at that was evaluating the first "component" part and establishing which of the variety of ranges that could be within it was in fact the main precedent range...some SUMPRODUCTS are obviously pretty complex and can involve a multitude of ranges within each component part... moreover some of those ranges could be being transposed etc...so what applies for one range does not for another ... eg the below entered as an array:

=SUMPRODUCT(A1:A4*TRANSPOSE(B1:E1))

will generate a result sheet matrix of 4 rows and 2 columns... however putting 1,2,3,4 as the row are not valid references for both ranges as the latter is in fact looking at B1:E1 not B1:B4.

I will have a think about it though...

Regards the highlighting of errors -- should be easy enough.

 

[DonkeyOte]

Below is the link to an updated version of sumproduct_analyse.xla

This latest version will highlight any error(s) that appear in the resulting matrix:

http://www.box.net/shared/zvugsqc5zv

If downloaded previously (and active) I would advise first to deactivate the add-in in Excel, then save the download over the existing file then reactivate the add-in.

Thanks,

 

[DonkeyOte]

Just a note to say the .xla has been updated again (same link as prior post) as I discovered that the following would generate errors:

=SUMPRODUCT(--(A1>{0,10,20,40}),A1-{0,10,20,40},{15,-5,-4,-2})

because the parentheses count was not looking at possibility of above, ie this would have worked:

=SUMPRODUCT(--(A1>{0,10,20,40}),(A1-{0,10,20,40}),({15,-5,-4,-2}))

The latest update has fixed this bug by adding the { } to the parentheses count Select Case...so the first formula would now generate correctly.

If anyone finds any more issues please let me know.

Cheers

 

[cornflakegirl]

Just a note to say the .xla has been updated again (same link as prior post) as I discovered that the following would generate errors:

=SUMPRODUCT(--(A1>{0,10,20,40}),A1-{0,10,20,40},{15,-5,-4,-2})

Can't believe I didn't spot that one before...

 

[JugglingReferee]

In essence once the add-in has been installed you will get a very basic floating toolbar with "Analyse Sumproduct" on it... if you highlight any Sumproduct formula and hit the button you should get a new sheet appear with the breakdown of how the formula was computed in terms of a numerical matrix... should the "component" arrays be multi dimensional (ie > 1 column) you will find the answer column is itself a Sumproduct formula and can be drilled into also.

Just a hiccup.... I have the XLA installed, can see the floating toolbar, but I cannot get the add-in to work. I made a cell that has a simple sumproduct in it. I still can't get any output from the add-in.

Do I position the cursor at that cell, and then press the toolbar button? Highlight/Select the sumproduct formula and it's arguement in the formula and then press the formula toolbar?

Thanks.