# Sumproduct Utility - willing testers required



## DonkeyOte (Nov 1, 2008)

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 (Nov 7, 2008)

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 (Nov 7, 2008)

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


----------



## DonkeyOte (Nov 7, 2008)

Jonmo1 -- thanks !

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


----------



## PatPat (Nov 7, 2008)

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 (Nov 7, 2008)

PatPat said:
			
		

> ...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 (Nov 7, 2008)

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 (Nov 9, 2008)

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 (Nov 10, 2008)

DonkeyOte said:


> 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 (Nov 13, 2008)

DonkeyOte said:


> 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.


----------



## DonkeyOte (Nov 1, 2008)

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 (Nov 13, 2008)

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 (Nov 13, 2008)

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 (Nov 13, 2008)

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


----------



## JugglingReferee (Nov 13, 2008)

DonkeyOte said:


> 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 (Nov 13, 2008)

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 (Nov 13, 2008)

DonkeyOte said:


> 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 (Nov 13, 2008)

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


----------



## DonkeyOte (Nov 14, 2008)

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 (Nov 15, 2008)

DonkeyOte said:


> 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 (Nov 15, 2008)

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,


----------



## DonkeyOte (Nov 1, 2008)

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 (Nov 28, 2008)

Hi,

I am not able to find Sumproduct Analyse.xla. Is it still available for download.

Regards
Kanwaljit


----------



## ZVI (Nov 28, 2008)

Hi Luke!

Very interesting idea with impressive result, thank you and all collaborators!

Will try to play with Add-In. But firstly I have investigated the issue mentioned by JugglingReferee and would confirm its presence.

The source of a problem is in a method the CommandBars("SPA") is installed. Add-in, being loaded first time, sets a full pathname to OnAction property of its command bar button.  But uninstallation does not remove CommandBars("SPA") , just switches off its visibility. Thus, next time being reinstalled or reloaded from another folder the Add-In only sets visibility of already existing CommandBars("SPA") , but the old full pathname is still present in its OnAction property. As a result it cause to Add-In macro calling error (macro not found).

The issue can be solved by adding this fixing sub:

```
Private Sub Workbook_Open()
  On Error Resume Next
  With Application.CommandBars("SPA")
    .Visible = True
    .Controls(1).OnAction = "Analyse_SumProduct"  ' Without path!
  End With
End Sub
```

Removing of Add-Ins’ command bar at uninstallation is desirable too and could be made as follows:

```
Private Sub Workbook_AddinUninstall()
  On Error Resume Next
  Application.CommandBars("SPA").Delete
End Sub
```

User friendly auto positioning of Add-Ins’ command bar at installation could be added as well:

```
Private Sub Workbook_AddinInstall()
  With Application.CommandBars("SPA")
    .Visible = True
    .Position = msoBarTop
  End With
End Sub
```

Surely, all code is in ThisWorkbook module.

Thank you for cool tool  ones more!

Regards,
Vladimir


----------



## DonkeyOte (Nov 29, 2008)

Kanwaljit the .xla should still be available using the last link in the thread: 

http://www.box.net/shared/zvugsqc5zv
Note: I've updated this today per Vladimir's post to account for the add-in being relocated post installation and/or being removed & reinstalled in different locations - so thanks for that Vladimir.

An .xls file is also still available for download which can be used to test/display the output of the above for various sumproduct formulae:
http://www.box.net/shared/73mg4fdj4t

Thanks for the interest -- please post back if you encounter any other bugs etc...


----------



## kanwal_no1 (Dec 1, 2008)

Hi,

Thanks for providing the link. A perfect tool I would say. Over the past half month I was looking for a tool which can provide the arrays evaluated in Sumproduct and it is just what I wanted. I have started using it and would respond as and when it is appropriate or desired.

Just a quick thought. It would look better if SPA is given some FACE ID or Icon instead of "Analyse Sumproduct". And that would save screen space too.

Regards
Kanwaljit


----------



## irfananeeza (Dec 13, 2008)

Hello, 

I downloaded Sumproduct_Analyses.xla from the below link.  http://www.box.net/shared/zvugsqc5zv

I saved it on my desktop.  When I open the file I see  a blank grey screen with a little box on the right hand side that say's "SPA" and "Analyse sumproduct".  When I clik on the "Analyse Sumproduct" I get an error message saying 
"Run time error 91
Object variable or With block variable is not set"

Can you tell me what am I doing wrong and how can I make this add in working.?
Thanks.


----------



## DonkeyOte (Dec 13, 2008)

OK an add-in does not work in the same way as a standard .xls(*) file.

I'll outline a basic process for installation but first...

a) Quit your current XL

b) Better to store the add-in somewhere other than your Desktop -- the most common place for these to reside is:



> C:\Program Files\Microsoft Office\Office *x*\Library
> _*Where x dependent upon the version you're running (Office 2007 = 12 for ex.)*_
> 
> Make a note of the location.



Once Add-in saved and location noted, open Excel -- then:



> -- Tools -> Addins (or use ALT + T -> I)
> -- Click Browse
> -- Navigate to the Location of the Add-in File (eg in the previously mentioned folder)
> -- Select Sumproduct_Analyse.xla
> ...



Now the Add-in is ready to use.

Open a spreadsheet and select a cell with a Sumproduct Formula in it and press the button to get a breakdown of the calculation.  

If you need examples use the additional file available for download (.xls) which has a variety of sumproducts available for testing/review.


----------



## kanwal_no1 (Dec 17, 2008)

Hi,

I have tested your marvellous utility. In one of my files the utility is not giving correct results. I can upload the file for you, but don't know how to do it.

Regards
Kanwaljit


----------



## arkusM (Dec 17, 2008)

"that'll do Donkey, that'll do"...

Wow, nice ultility! The xla "install" worked perfectly. 
I will see if I can teach people how to use the Sumproduct construct with this great little tool.

Cheers!


----------



## DonkeyOte (Dec 17, 2008)

kanwal_no1 -- I have PM'd you such that you can mail me the offending formula etc so I can replicate / investigate... I've experienced a few issues myself when using named ranges but not had the time to look into in great depth.

arkusM, thanks... I hope it helps... kind of how I came up with the idea myself... I saw one of Aladin's formulae and couldn't get my head around what exactly it was generating and wanted to find out... just to reiterate this is not all my own work -- Rory (rorya), Richard (Schollar) & Emma (CornflakeGirl) all made MAJOR contributions... I was just the doley who had time on his hands to put it all together.


----------



## DonkeyOte (Dec 31, 2008)

Just as a follow up in response to kanwal_no_1's posts... there is an issue using the tool if the Sumproduct formula contains INDIRECT references either in the formula itself or via Named ranges ... use of ROW() etc within referenced named ranges can also cause issues but not 100% of the time.

At present I can't think of an obvious solution to the above issues so will have to go back to the gurus (the R's) and see if they can think of a work around.

Thanks all.


----------



## DonkeyOte (Nov 1, 2008)

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 (Jan 12, 2009)

Hi DonkeyOte,

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

Regards
Kanwaljit


----------



## kanwal_no1 (Apr 10, 2009)

Hi DonkeyOte,

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

Regards
Kanwaljit


----------



## DonkeyOte (Apr 11, 2009)

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...


----------

