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

 

[GlennUK]

I often calculate parts of Sumproduct formulas by doing array formulas across multiple cells. Would doing that suit your purposes? Or do you really want some wonderful analysis tool for this?

 

[cornflakegirl]

Yeah, agree with Glenn. Tools-Formula Auditing-Evaluate Formula is quite handy too.

Why is evaluating each part difficult? (That's not a rhetorical question btw - am impressed that you're even contemplating this!)

 

[RoryA]

Do you always do your SUMPRODUCT formulas in the same format? I.e. do you always use the =SUMPRODUCT(--Criteria1,--criteria2,range) type format, or do you sometimes use multiplication of the criteria (or other mathematical operation)?

 

[Krishnakumar]

I think he is asking something similar to Show Detail function on Pivot Table.

 

[DonkeyOte]

Thanks for responses and apologies for delay in response (have been offline)

I feel like a bit of a muppet insofar as the Evaluate formula does I guess what I want albeit I'd like to get that data returned into a table in a sheet ... can this be done I wonder ?

Rory, yes I would set up / have setup the first bit to break out the formula into components but was going around in circles trying to work out the best way to return the results of each separate component/argument... I figured given XL must be doing this itself in memory there may be a way to get at it and write it back to a sheet... I think CG & co pretty much have it...

This was what I'd put together earlier (the VERY basics and some hardcoded values which would be changed later) just to create a sheet and split out the formula into separate pieces but this wasn't really doing anything useful as yet and I was sure that someone would have a better idea... it would be a COOL tool though, no ?

Sub Analyse_SumProduct()
'-------------------------------------------------------------------------------------------------------------------------
'coded (poorly) by DonkeyOte Weds 22 Oct 2008
'abbreviations
'SP - SumProduct
'SPA - SumProduct Analysis
'-------------------------------------------------------------------------------------------------------------------------
'#DEFINE VARIABLES (Assign/Set where possible)
'-------------------------------------------------------------------------------------------------------------------------
Dim s_s1 As String: s_s1 = ActiveSheet.Name                 'to hold sheet name on which SP formula resides
Dim s_s2 As String                                          'to hold sheet name of SPA sheet (to be created)
Dim s_c1 As String: s_c1 = ActiveCell.Address(0, 0)         'to hold cell address in which SP formula resides
Dim s_c1_f As String: s_c1_f = ActiveCell.formula           'to hold SP formula being analysed
Dim s_c1_f_res As Long                                      'to hold result of SP formula being analysed
Dim i_c1_f_i As Long                                        'to hold mid char point when iterating formula string
Dim i_c1_p_cnt As Long                                      'to hold running total of parentheses
Dim i_c1_f_start As Long                                    'to hold start char pos of SP component
Dim s_c1_f_delim As String: s_c1_f_delim = ","              'to hold SP component delimiter (eg "," or "*") - change to InputBox later
Dim i_c1_f_c As Integer                                     'to hold count of "components" within SP formula
'-------------------------------------------------------------------------------------------------------------------------
'#SET APP SETTINGS
'-------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With
'-------------------------------------------------------------------------------------------------------------------------
'#STEP 1: VALIDATE FORMULA BEING ANALYSED -- EXIT IF INAPPROPRIATE FOR THIS ROUTINE
'-------------------------------------------------------------------------------------------------------------------------
On Error Resume Next
s_c1_f_res = Evaluate(s_c1_f)
e = Err.Number
On Error GoTo 0
If InStr(UCase(s_c1_f), "=SUMPRODUCT") = 0 Then e = 1       'although formula is valid it's not a SUMPRODUCT formula!
If InStr(s_s1, "SPA") Then e = 1                            'firing code from previously created SPA sheet!
'exit routine if formula not valid for analysis
If e <> 0 Then
    MsgBox "Current Formula Not Valid for SUMPRODUCT Analysis"
    GoTo ExitHere:
End If
'-------------------------------------------------------------------------------------------------------------------------
'#STEP 2: INSERT A NEW SHEET ON WHICH THE SPA WILL BE HELD
'set name convention to utilise system time so as to remove possibility of duplication without need for ws testing / deletion
'set up headers and format column A width
'-------------------------------------------------------------------------------------------------------------------------
Sheets.Add
ActiveSheet.Name = "SPA_" & Format(Now(), "DDMMYY_HHMMSS")
s_s2 = ActiveSheet.Name
Cells(1, 1) = "Formula Location:"
Cells(1, 2) = s_s1 & "!" & s_c1
Cells(2, 1) = "Formula: "
Cells(2, 2) = "'" & s_c1_f
Cells(3, 1) = "Result: "
Cells(3, 2) = s_c1_f_res
Cells(5, 1) = "Component Part(s):"
Cells(7, 1) = "Row/Column:"
Cells(7, 2) = "Result:"
Columns(1).AutoFit
'-------------------------------------------------------------------------------------------------------------------------
'#STEP 3: BEGINNING STRIPPING DOWN FORMULA INTO "COMPONENT" PARTS
'first remove =SUMPRODUCT() leaving just innards behind...
'loop the remaining string using predefined component "delimiter" (eg "," or "*") to break into parts (check parentheses)
'-------------------------------------------------------------------------------------------------------------------------
s_c1_f = Replace(s_c1_f, "=SUMPRODUCT(", "")
s_c1_f = Left(s_c1_f, Len(s_c1_f) - 1)
'default start pos of string to be 1
i_c1_f_start = 1
'iterate formula string
For i_c1_f_i = i_c1_f_start To Len(s_c1_f) Step 1
    'ascertain current character and act appropriately
    Select Case Mid(s_c1_f, i_c1_f_i, 1)
        Case "("
            'opening parentheses so add 1 to parentheses count
            i_c1_f_p_cnt = i_c1_f_p_cnt + 1
        Case ")"
            'closing parentheses so remove 1 from parentheses count
            i_c1_f_p_cnt = i_c1_f_p_cnt - 1
        Case s_c1_f_delim
            'current char appears to be delimiter however only valid end point IF count of parentheses is 0
            If i_c1_f_p_cnt = 0 Then
                'increment count of components
                i_c1_f_c = i_c1_f_c + 1
                'paste formula component as header in column B onwards (row 5)
                Cells(5, 1 + i_c1_f_c) = Mid(s_c1_f, i_c1_f_start, i_c1_f_i - i_c1_f_start)
                'reset start pos to be current string pos + 1
                i_c1_f_start = i_c1_f_i + 1
            End If
    End Select
Next i_c1_f_i
'insert final component
'increment count of components
i_c1_f_c = i_c1_f_c + 1
'paste formula component as header in column B onwards (row 5)
Cells(5, 1 + i_c1_f_c) = Mid(s_c1_f, i_c1_f_start, i_c1_f_i - 1)
'-------------------------------------------------------------------------------------------------------------------------
'#EXIT POINT
'-------------------------------------------------------------------------------------------------------------------------
ExitHere:
'-------------------------------------------------------------------------------------------------------------------------
'#RESET APP SETTINGS
'-------------------------------------------------------------------------------------------------------------------------
With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With
'-------------------------------------------------------------------------------------------------------------------------
'#END
'-------------------------------------------------------------------------------------------------------------------------
End Sub

 

[RoryA]

For a straightforward SUMPRODUCT(--(crit1),--(crit2),data) format, something like this might work:

Sub AnalyseSumproduct()
   Dim rng As Range
   Dim varParts, varSubParts, varOutput
   Dim strFormula As String, strTemp As String
   Dim lngIndex As Long
   Dim wksOut As Worksheet
   
   Set rng = ActiveCell
   If rng.HasFormula Then
      ' check it's a SUMPRODUCT formula
      If InStr(1, rng.Formula, "=SUMPRODUCT", vbTextCompare) = 1 Then
         Set wksOut = ActiveWorkbook.Worksheets.Add
         rng.Parent.Select
         strFormula = Mid$(rng.Formula, 12)
         varParts = Split(strFormula, ",")
         For lngIndex = LBound(varParts) To UBound(varParts)
            strTemp = varParts(lngIndex)
            If lngIndex = LBound(varParts) Then strTemp = Mid(strTemp, 2)
            If lngIndex = UBound(varParts) Then strTemp = Left(strTemp, Len(strTemp) - 1)
            varOutput = Application.Evaluate(strTemp)
            wksOut.Cells(1, lngIndex + 1).Resize(UBound(varOutput)).Value = varOutput
         Next lngIndex
         wksOut.Cells(1, lngIndex + 1).Resize(UBound(varOutput)).FormulaR1C1 = "=PRODUCT(RC1:RC[-1])"
      End If
   End If
End Sub

 

[DonkeyOte]

Simply awesome... am going to play around with this to make it "pretty" and try and add in some of my archaic stuff to handle delimiters that aren't true delimiters (ie non standard formulae) ... but in short Rory you're a genius...

 

[RoryA]

I know.

 

[Richard Schollar]

...to mention nothing of his inherent modesty!