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

 

[RoryA]

It appears that EVALUATE returns an array for ROW (even with one cell), which causes it to treat everything that follows as an array formula (at least, that's how it appears), whereas with 1, it doesn't. Why it should ignore the two arrays in MATCH I'm not sure.

 

[cornflakegirl]

Thanks Rory - that makes sense. I am strangely comforted that you don't know either. Especially since Luke has just made it work anyway.

Luke - the output is looking really good!

 

[cornflakegirl]

I missed another also... smartarse

That's a little harsh, given that it was the VB Editor that spotted them!

I'm actually feeling quite sad that I don't know anyone off-board who is going to appreciate this. Maybe I can convince my husband to be interested...

 

[RoryA]

Has he fixed his computer yet?

 

[cornflakegirl]

Mostly. I think. Although sound goes funny on Skype quite a lot so [insert technical stuff here].

 

[DonkeyOte]

You mean his old Oracle Atmos or whatever it is given Emma won't let him buy a new one (or is it the other way around I can never remember...)

Regardless of whether or not anyone other than me ever uses this I'm chuffed to bits... I think this could come in pretty handy... question is whether or not it's possible to return more info (underlying values in ranges) and/or if it's even possible to get it to work if the component's contain errors... afterall that's probably where it would be really useful (trace the cause of the error in the matrix)

EDIT: Yes would be the answer -- simply removing the initial evaluation of the formula (Step 1 -- leaving in check that it's a SUMPRODUCT formula and that the formula is not resident on an analysis sheet)) should result in a matrix being generated showing the errors.... so making it a useful audit tool (in my eyes).

 

[sous2817]

If someone could help me with my request made back on the first page, I know there would be quite a few people in my office who would find this very usefull...

 

[DonkeyOte]

One more minor change to Step4 code to handle possibility of end user trying to multiply two ranges one of which may contain alpha's... the present PRODUCT in final column would ignore text and generate numerical result when SUMPRODUCT would in fact generate 0, so instead of:

Cells(8, l_c1_f_comp_i).Resize(UBound(v_c1_f_output)).FormulaR1C1 = "=PRODUCT(RC2:RC[-1])"

I would use:

Cells(8, l_c1_f_comp_i).Resize(UBound(v_c1_f_output)).FormulaR1C1 = "=IF(COUNT(RC2:RC[-1])<>COLUMNS(C2:C[-1]),0,PRODUCT(RC2:RC[-1]))"

 

[cornflakegirl]

Luke - just a thought - having just the PRODUCT formula is quite cool, as I reckon even non-nerds can understand what it's doing quite easily. Maybe put the alpha test into the code rather than the formula?

 

[DonkeyOte]

Emma, not quite sure I follow... at present no formulas are shown on the result SumProduct Analysis sheet as the formulas are usurped by their values. Do you mean - check if COUNT <> COLUMNS and if so write back 0 else write back PRODUCT formula ? Obviously writing back the PRODUCT formula where alphas exist in the matrix will produce incorrect results.