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

 

[kanwal_no1]

Hi DonkeyOte,

Does it help somehow ? Seems related to me.
http://www.decisionmodels.com/calcsecretsi.htm

Regards
Kanwaljit

 

[kanwal_no1]

Hi DonkeyOte,

I am quite eager to dip my fingers in next version of SPA. It is just amazing........

Regards
Kanwaljit

 

[DonkeyOte]

If I'm honest Kanwaljit I've not looked at this any further... I'm not sure the issues discussed can be surmounted at least not with my abilities ... it all boils down to the Evaluation of component parts and some seemingly are not evaluated as you may expect...