Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Say cell A1 has the formula A2 + A3

and B1 has the formula B2 + B2

and C1 has the formula A1 + B1

I need an easy and quick way to create the formula for C1 in the flattened state..

i.e. (A2 + A3) + (B1 + B2)

Although the above example is trivial, I am trying to simplify a formula that refers to around 40 cells (and around 70 calls through a third party plugin).

Any help appreciated!

share|improve this question
    
What you are asking is legitimate, but functionally it isn't ideal. Complex calculations are very difficult to troubleshoot. It is best practice to show your calculation, even if the details of the calculation are ultimately hidden from the end user. –  guitarthrower Oct 31 '13 at 20:29
    
A bit late, but shouldn't that be (A2 + A3) + (B2 + B2)? –  Arjan Jul 25 at 13:45

2 Answers 2

If all you need is to see the formula in the C1 cell you could try this:

= CONCATENATE("= (";SUBSTITUTE(FORMULATEXT(A1);"=";"");") + (";SUBSTITUTE(FORMULATEXT(B1);"=";"");")

It does work on Excel-2013 but I couldn't test on Excel-2007.

If you need to execute that formula, you might want to check this answer on how to convert that string to a real formula:

http://stackoverflow.com/questions/4471884/turn-a-string-formula-into-a-real-formula

share|improve this answer
1  
FORMULATEXT function is only available in Excel 2013 –  barry houdini Oct 24 '13 at 10:05
    
Thanks - I also would like to automatically find the precedents and build up the formula that's actually executed –  sjhuk Oct 24 '13 at 10:29

If you don't have Excel 2013, you can fake the FORMULATEXT function with a short function in Visual Basic. I did use the question that douglaslps referenced to return it as a value instead of just the string. (Very neat answer, that one.) In cell C1, you'd use the formula:

=CombineFormulas(A1:B1,"+")

and you'd put this function in the workbook's modules somewhere:

Option Explicit
Public Function CombineFormulas(RangeToCombine As Range, Operator As String)
    'Input:  Range of cells and an operator to use when combining their formulas
    'Output: String of the combined formulas
    Dim cell As Range
    Dim s As String
    s = "="
    For Each cell In RangeToCombine.Cells
        If Len(s) > 1 Then s = s & Operator
        s = s & "(" & Mid(cell.Formula, 2, Len(cell.Formula)) & ")"
    Next
    Application.Volatile
    CombineFormulas = Evaluate(s)
End Function
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.