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]

It took me so long to post I missed Rory's but at least I was along the right lines which is reassuring... Rory I would say that the

On Error GoTo Fatality:

line should still exist prior to the initial Evaluation command in case the formula is invalid... it could be left in as is too (ie post Resume Next) rather than the GoTo 0 statement I had in earlier...

EDIT: Ignore me...
(like I need to say it )

 

[cornflakegirl]

Rory - your code works beautifully on my example.

 

[cornflakegirl]

It just worked on this:

Excel Workbook
	B	C	D	E	F
45	2	3	4	5	6
46
47	47
48	48
49	49
50	50
51	51
52
53
54	990
Sheet1

I am so impressed!

 

[RoryA]

On Error GoTo Fatality:

it's a small point, but the colon on the end of that line is unnecessary - there is no additional code on that line.

 

[DonkeyOte]

Yes habit of mine from my .net stuff... soz.

I am sure there will be plenty of room for streamlining the code... there normally is where I'm involved :-0

 

[RoryA]

Oops - I forgot to adapt my credits line from this:

'tough bits coded by messrs. rorya, RichardSchollar & CornflakeGirl (aka Rory, Richard & Emma)

to this:

'tough bits coded by messrs. rorya, RichardSchollar & CornflakeGirl (muliebrity notwithstanding) - a.k.a. Rory, Richard & Emma.

 

[cornflakegirl]

And I get to learn a new word too, albeit one that I am unlikely often to exercise

 

[DonkeyOte]

Emma, regards multitude of operators.

I *think* you would always have to assume one given rules, namely:

A comma always supercedes an asterisk as an operator

the former could never appear outside of parentheses acting as anything but a delimiter (semi colon regional settings aside), whereas the latter could (loosely) , eg

=SUMPRODUCT(--(A1:A100<70),A1:A100*B1:B100)
where A1:A100 contains =ROW() and B1:B100 = A1+1

I say loosely because in the above you're interpreting A1:A100*B1:B100 as 1 "component" when technically I guess you could argue that it is in fact two.

Given the above I think to permit both comma and asterisk delimiters you would have to jump to the assumption that any asterisk appearing outside of parentheses (ie where running parentheses count = 0) is a delimiter ... continuing the above example this would mean returning 3 columns (if you wished to run with 2 delimiter option), eg

Col B: A1:A100<70
Col C: A1:A100
Col D: B1:B100

with Col E: result

it would be impossible to differentiate / ascertain as to whether or not the user intended to see the A1:A100*B1:B100 as one argument or two...

So I guess my point would be probably best to leave as an either/or else you risk returning more data than end user expected to see...

What do you guys think ?

 

[cornflakegirl]

I think making the * into a delimiter breaks the calculation down into more obvious chunks.

One thought though - you probably need something that handles when * is doing matrix multiplication rather than working as a delimiter, eg:

Excel Workbook
	B	C	D	E	F
45	2	3	4	5	6
46
47	47
48	48
49	49
50	50
51	51
52
53
54	4900
Sheet1

Excel Workbook
	A	B	C	D
1	Formula Location:	Sheet1!C54
2	Formula:	=SUMPRODUCT(B45:F45*B47:B51)
3	Result:	4900
4
5	Component Part(s):	"B45:F45"	"B47:B51"
6
7	Row/Column:	Result:		Total(s)
8		2	47	94
9		3	48	144
10		4	49	196
11		5	50	250
12		6	51	306
SPA_231008_124945

 

[DonkeyOte]

Emma, can you elaborate -- not quite sure I follow...