Sumproduct to Table...

[DonkeyOte]

Does anyone know of a utility / function or have themselves coded something that permits an end user to analyse a given Sumproduct formula in greater detail - ie returning the results of the generated array into tabular form ?

I've just sat down thinking this would be quite useful, ie I click on any sumproduct formula and run a routine to "Analyse" which in turn generates a new sheet with a matrix of the results...

However having just started coding this myself it is now dawning on me that this could prove very difficult (read beyond my abilities) ... breaking out the formula itself etc into component parts is pretty easy but actually evaluating each part is a little more difficult.

I suspect someone knows of an elegant way to do this and I'd love to see it... it may of course be that you can do this already but I don't know how...

Thanks,
Luke

 

[DonkeyOte]

I agree entirely -- I'm very interested to see what people can come up with. Retrieving the bog standard stuff shouldn't be that much of an issue but per my prior example -- well an entirely different ball game. For ex... you would also want to return the criterion in addition to underlying values... using Aladin's example it's clear from the use of SEARCH that the initial underlying values of interest are those specified as A3:A6 however before returning those values they must be encased within "," so as to reflect what the formula is actually doing. In addition within that one component we are also going to be very interested in H3 ... but are we going to try and display both underlying values or look to break out the component into more parts... incredibly difficult.

Personally I'm wondering if this is at all feasible... time will tell... suffice to say this is far beyond my abilities so I intend to stand back and let the geniuses show us their stuff.

Incidentally where has the Schollar gone ?
We need to get him back onto this... both he & Rory have gone pretty quiet... boo.

 

[cornflakegirl]

(Actually, in that formula I don't think you do need to encase in "," - but obviously there would be other cases where it would be necessary.)

It's not really about understanding SP though, is it? It's about understanding the formulas of Aladin et al - a whole new project!

 

[DonkeyOte]

Oh to be a pedant... I think we both know that the formula was written with real life strings in mind... (ie not one character values)... anyway I guess my point was there are in effect 4 formulas in that one component -- only the outer of which is returned by the SPA sheet... question is what should be returned for the "underlying" value ? You would really need to see contents of A AND the result of the INDEX in order to decipher how that tied back to the SPA value... but working out what's crucial and what isn't is no doubt a nightmare.

Enough from me... I promise to be quiet from now on else this thread will look more like a conversation and less like what it's meant to be... the start of the "Sumproduct Revolution"

 

[cornflakegirl]

(I wasn't being that pedantic - even with real-life strings, I don't think you need the commas. I am possibly missing something obvious. Will stop hijacking now.)

 

[xld]

The code I have to date will process "--ISNUMBER(SEARCH("",""&$A$3:$A$6&"","","",""&INDEX($E$3:$E$4,MATCH($H3,$F$3:$F$4,0))&"",""))" and return $A$3:$A$6.

Similalrly, --(C5=A1:A10) retums A1:A10, as does --(A1:A10=C5).

But I am trying to get it to return all ranges, and check the size against our condition bounds, and also gto manage range names.

 

[DonkeyOte]

Just thought I'd bump this back up into people's minds... so as far as I'm aware the analysis of the result in terms of numerics is complete but we're now waiting for messrs Rory, Richard & Bob to come up with something elegant to retrieve underlying values ?

If we don't think the underlying values is going to be possible, or at least not in the immediate future I might for now just chuck what I have into an .xla for personal use and also pop it up on a couple of forums to get some serious testing done on it.

Thoughts ? Realistic expectations / timeline regards underlying values ?

 

[RoryA]

I'm leaving it to you guys for now. I don't have enough solid blocks of time to devote to a project like this that needs more than a couple of minutes here and there!

 

[DonkeyOte]

whereas I on the other hand have time to burn but alas not the requisite skillset...

 

[cornflakegirl]

Please put it into a .xla - I don't care about underlying values, but I do want to learn how that bit works!

 

[DonkeyOte]

Have put into a standard .xla with a standard toolbar ("SPA").

Can be downloaded from:

http://www.idontthinkso.co.uk/spa/sumproduct_analyse.xla

Once downloaded (make note of location) go to Excel, Tools -> Addins -> Browse -> find file and click OK.

To run - select a formula and press the button on the toolbar.

Let me know if you have any problems.