# Flatten Excel Formula (2007)

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!

-
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 '14 at 13:45

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

-
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
``````
-