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]

xld, thanks again.

Yes the issue of mixed separator's is something we've discussed on the thread previously but I will investigate in more detail as I had not come across the calculation errors you mention but suspect this was because I had not tested thoroughly enough -- the issue is muddied further when multiplying arrays.

re: underlying values... yes, this again is something I've mooted since the commencement of the thread... essentially my "dream" end-result was to was replicate the Evaluate formula option of XL but return the values to a matrix and thus mimic your own web page illustrations as I found this the best way to both understand and explain the use of SUMPRODUCT.

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

 

[xld]

I agree with you that it would be a superb addendum to that page, a tool to help tyros understand what is going on, which is why the values being tested would be so illuminating. As I said, getting the 'real' components shouldn't be that hard, but getting the values bing tested is a bit tough. As a simple example, if you have a test of

--(ISNUMBER(MATCH(A1:A10,E1:E5,0)))

the range being tested is A1:A10, but extracting that is no so easy. And of course, more exotic formulae can easily be racked up.

 

[RoryA]

If the ranges are on the same sheet, you could probably do something relatively straightforward with the Precedents collection but it would get more tricky with ranges on other sheets. If they were straight address references, I suppose you could do it with regexp since you know there would be some text, an exclamation mark, then an address; could get very tricky with named ranges though!

 

[DonkeyOte]

I must confess I'd been thinking about the issue of mixed delimiters for a while ... walking the dog just now a few things occurred to me that I think you could use to govern how you assess the formula... namely:

1. A comma can not appear as anything other than a delimiter where count of parentheses is 0.

2. The same can not be said of an asterisk. An asterisk can act in different ways as so well pointed out by Emma earlier... eg

=SUMPRODUCT(A1:A5*B1:F1)

acts or rather for the purposes of this exercise is treated very differently to

=SUMPRODUCT(A1:A6*TRANSPOSE(B1:F1))
(as array)

or non-array form

=SUMPRODUCT(A1:A6*B1:B6)

This is why in the code analysis is conducted based on the UBound array values assigned to 2 in each array to ascertain as to how the formula/component must be calculated.

3. It can be argued though that an asterisk acts purely as a delimiter whereby either (or both) of the preceding and subsequent component contain a "test"(s) ... ie an operator (=, >, <) or an ISNUMBER etc... the easiest way (I think) to test for this is to see in those cases where the * could be acting as a delimiter (ie parentheses count = 0) what the preceeding / subsequent characters are ... if it's a closing / opening parentheses then this implies to me at least that the * is most likely acting as a delimiter (but alas not guaranteed to be so...)

Based on the above and totally ignoring the issue of ; as delimters I think you could make the case to let the code ALWAYS assume "," to be the default delimiter (as it can not exist outside of parentheses count as anything but) and then analyse any * that could be perceived to be a delimiter (based on parentheses count = 0) and test based on conditions 2 & 3...

I still think there are issues wherein the formula is constructed incorrectly...eg

=SUMPRODUCT(A1:A6>1*B1:B6,C1:C6)

versus

=SUMPRODUCT((A1:A6>1)*B1:B6,C1:C6)

As I say this would dreamt up walking around a field so it's most likely full of holes... I plan to do a little testing to this effect today.

 

[DonkeyOte]

And for now I'm not even thinking about getting the underlying values...

 

[DonkeyOte]

As a quick (final) follow up point re: my main post...

The last version of the code I posted up is incorrect... the following line in STEP 4:

Select Case Application.WorksheetFunction.CountIf(Range("9:9"), "Result(s)") = Application.WorksheetFunction.CountA(Range("10:10"))

needs to be changed to:

Select Case Application.WorksheetFunction.CountIf(Range("9:9"), "Result(s)") = Application.WorksheetFunction.CountA(Range("10:10")) - 1

This is to account for the insertion of values in A that were not there when first coded... result of present code is to always conduct a SUMPRODUCT on the resulting arrays which is not always what is required.

Regards the comma as default... as a quick fix for testing xld's scenario... changing the below (Step 3):

Case s_c1_f_delim

to include the comma at all times should resolve that particular issue (though obviously not in terms of an ultimate fix)

Case s_c1_f_delim, ","

 

[cornflakegirl]

Luke - maybe include ; as well as an automatic delimiter?

 

[xld]

I agree, but I think that as this is a tool to help, not an intrinsic function within Excel, if they construct the formula incorrectly, it goes wrong. It is not a SUMPRODUCT generator, it is a debugging aid. Accepted, part of debugging is getting the syntax right, but what is frigt in this context, we have pushed SP so hard, much of it can look very odd.

 

[xld]

BTW, can you explain reset to me, in what circumstances would reset be triggered?

 

[xld]

If the ranges are on the same sheet, you could probably do something relatively straightforward with the Precedents collection but it would get more tricky with ranges on other sheets. If they were straight address references, I suppose you could do it with regexp since you know there would be some text, an exclamation mark, then an address; could get very tricky with named ranges though!

Even same sheet gets problemmatical, precedents and dependents seem to be very problemmatical. For instance, if you have a simple =A1:A10 in say B1, show precedents only shows A1. Change B1 to =A2:A10 and you get the lot! So it seems that the formula has to be outside of the reange being evaluated, which is self-defeating when you are trying to determine that range.