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

 

[DonkeyOte]

Ensure the cell containing the formula is the active/selected cell then press the button on the toolbar marked "Analyse Sumproduct"

What happens ?

Assuming you don't get an output sheet:

a) Are you getting an Error MsgBox ?
b) Which version of XL are you running ?

I've not encountered this before so just trying to narrow down the possibilities.

 

[JugglingReferee]

The error message I receive is a dialog box:

A document with the name 'Sumproduct_Analyse.xla' is already open. You cannot open two documents with the same name, even if the documents are in different folders.

To open the second document, either close the document that's currently open, or rename one of the documents.

The only doc open is my ss. There isn't two docs open!?



My formula is:

=SUMPRODUCT(CIS_CW_Football_Men_Points,I7:P7)
Where CIS_CW_Football_Men_Points is a named region.

 

[DonkeyOte]

Hmm... where did you store the .xla file on your PC ? Is it by any chanced stored in your XLStart folder ?

 

[JugglingReferee]

Hmm... where did you store the .xla file on your PC ? Is it by any chanced stored in your XLStart folder ?

No. The file that I downloaded from the box.net site is in

C:\Program Files\Microsoft Office\Office10\Addins



Edit: it is XL 2002 (from Office XP). I have Office 2007 that I bought from work, but my laptop isn't very new so I was afraid that 2007 would run slow on it. Office XP runs well.

 

[DonkeyOte]

OK -- uncheck the Add-in via Tools -> Addins then close XL completely.

Re-open XL and via Tools -> Addins Browse to your stored version and click ok.

See if that resolves...

 

[JugglingReferee]

OK -- uncheck the Add-in via Tools -> Addins then close XL completely.

Re-open XL and via Tools -> Addins Browse to your stored version and click ok.

See if that resolves...

Ok, I will have to try later. On a part-time basis, I referee tackle (Canadian) football, and the league final is in 2 hours - and I'm refereeing the game. So I gotta go.... TY for the help!

 

[JugglingReferee]

I am still having trouble after the suggestions. I might try uninstalling Excel and re-installing. Thanks!

 

[DonkeyOte]

Well let me know -- prior to doing anything with XL itself perhaps uninstall the add-in XL again, delete any/all instances of the .xla file on your PC and download a fresh copy from box.net and reinstall.
As I say presently I can't replicate and have had no other users report this issue (as yet).. but that's not to say there isn't one so please keep me posted.

Thanks,
Luke

 

[JugglingReferee]

Well let me know -- prior to doing anything with XL itself perhaps uninstall the add-in XL again, delete any/all instances of the .xla file on your PC and download a fresh copy from box.net and reinstall.
As I say presently I can't replicate and have had no other users report this issue (as yet).. but that's not to say there isn't one so please keep me posted.

Thanks,
Luke

It works!!!

Here is what I did:

(I had already removed/re-added the XLA prior to your suggestion, but I made sure to follow your steps for consistency reasons.)

I knew that I could solve the problem without re-installing Excel, although the brute force method shoudl always work, right?

1. I removed the Add-in as per your instructions.

2. I typed the error text that I had received into Google (with quotation marks). This led me to a whack of incidents that seemed very similar, although nothing specifically identical. I re-associated extentions with the regserver:

excel /regserver

3. I then went into the registry, and removed any reference to Sumproduct_Analyse.xla whatsoever using regedit.

4. Re-downloaded the XLA file.

I loaded up Excel, installed the Add-In, opened my file, and tried it out on a simple sumproduct that I have, and presto:

WOW!

Thank you for this wicked utility!

 

[DonkeyOte]

Thanks for posting back with your resolution.

Please don't hesitate to post back here again should you experience any issues using the add-in itself.

Thanks,