How to rewrite excel formulas in a readable manner

Question

I have an Excel file with formulas in this manner:

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W$10)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)), IF((HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)<0,0,(HEX2DEC(W17)-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,FALSE)))))))

I would like to simplify them so it will be written in a more readable manner.

• Can I edit/write Excel formulas in indented way?
• What kind of simplifications can I do?
• Should I use an VBA script instead of Excel's formulas?

Answer 1

As an example using helper columns, you could shorten the formula with the following

[A1] =VLOOKUP(F16,$M$36:$N$41,2,FALSE)

[B1] =HEX2DEC(W$10)

[C1] =HEX2DEC(W16)

[D1] =HEX2DEC(W17)

then the large formula is shortened to

=IF(OR(ISERROR(G16),ISERROR(G17)),X16,IF(OR(G16="xxx",G16="yyy",G16="zzz"),Y16,IF(G16="333","N\A",IF(G17="333",Z16,IF(D17="",IF((B1-C1)/A1<0,0,(B1-C1)/A1), IF((D1-C1)/A1<0,0,(D1-C1)/A1))))))

This is particularly effective when using volatile functions such as DATE or NOW which you don't want to recalc for every cell when it's the same result.

Whether it's more readable, perhaps not but you can label column headings with appropriate comments

Answer 2

Naming some of the cells you refer to might make the whole thing more readable

Answer 3

You can simplify your formula substantially while still keeping a single formula. You are repeating almost the same expression 4 times with the HEX2DEC/VLOOKUP part, that can be reduced to a single instance if you recognise that this

=IF(formula<0,0,formula)

.....is equivalent to

=MAX(0,formula)

[for numeric results of formula]

and if you nest your IF(D17="".....expression within the main formula, i.e. this version

=IF(ISERROR(G16&G17),X16,IF(OR(G16={"xxx","yyy","zzz"}), Y16,IF(G16="333","N\A",IF(G17="333",Z16,MAX(0,(HEX2DEC(IF(D17="",W$10,W17))-HEX2DEC(W16))/VLOOKUP(F16,$M$36:$N$41,2,0))))))

Answer 4

If you have a formula that looks like that, then to get a meaningful response you will have to post a sample workbook on a forum with clear instructions on what you are trying to achieve.

Yes, some of the answers above point out how you can view the formula better, or get rid of some superfluous stuff, or hide the complexity within some VBA (which in my opinion only addresses cosmetics, probably with significant expense in terms of greatly increased recalculation time).

But without knowing the intent of the formula - and of the workbook in which it sits - one can only offer so much advice.

If you have tens of thousands of formulas like this in your workbook, then you have a data structure problem, and not a formula problem. The most efficient formula is the one that is avoided. If you were to redesign this workbook from scratch so that it leveraged off of Excel Tables, PivotTables, and perhaps the Advanced Filter, then you would avoid tens of thousands of formulas like this one. Maybe hundreds of thousands of formulas.

Answer 5

Since you asked about VBA code, I thought I'd give it a try. It's certainly more understandable and therefore maintainable, however the function has 11 arguments so it's a little unwieldy.

Function Magic(d17 As Range _
                , f16 As Range _
                , g16 As Range _
                , g17 As Range _
                , w10 As Range _
                , w16 As Range _
                , w17 As Range _
                , x16 As Range _
                , y16 As Range _
                , z16 As Range _
                , m36 As Range) As Variant


    Dim a As Variant
    Dim b As Variant

    If IsError(g16.Value) Or IsError(g17.Value) Then
        Magic = x16.Value
        Exit Function
    End If

    If g16.Value = "xxx" Or g16.Value = "yyy" Or g16.Value = "zzz" Then
        Magic = y16.Value
        Exit Function
    End If

    If g16.Value = "333" Then
        Magic = "N\A"
        Exit Function
    End If

    If g17.Value = "333" Then
        Magic = z16.Value
        Exit Function
    End If

    If d17.Value = "" Then
        a = Application.WorksheetFunction.Hex2Dec(w10.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        a = a / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If a < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = a
            Exit Function
        End If
    Else
        b = Application.WorksheetFunction.Hex2Dec(w17.Value) _
                - Application.WorksheetFunction.Hex2Dec(w16.Value)
        b = b / Application.WorksheetFunction.VLookup(f16.Value, m36, 2, False)
        If b < 0 Then
            Magic = 0
            Exit Function
        Else
            Magic = b
            Exit Function
        End If
    End If
End Function

To make it easier to follow your formula logic (and I didn't know what the cells represent), I named the variables for the cell references. You'll want to rename them to something meaningful. The code belongs in a module.

Answer 6

You can use Alt+Enter in the formula bar to make your formula multiline. Sadly, no tabs only spaces so it becomes tedious to create and edit. See also

http://www.dailydoseofexcel.com/archives/2005/04/01/excel-formula-formatter/