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

A combination of helper columns and named ranges would make that formula quite simple.

In the following image you can see how named ranges can unclutter a formula:

Notice that "prices" is name for range A2:A7 and "inflated_prices" is name for B2:B7.

Notice also that names are intelligent: sum(prices) will sum the whole range, whereas =+prices*2 in B2 resolves to =+A2*2, =+prices*2 in B3 resolves to =+A3*2 and so on.

Answer 2

FormulaDesk is a free Excel add-in that makes complex formulas more readable and easier to understand without having to rewrite them. It makes creating, editing, debugging and understanding formulas much easier. It has two modes: 'Edit View' and 'Explore View', which can be toggled.

• The ‘Edit’ view is an enhanced formula editor, with Intellisense etc. It formats as you type, vertically offsetting nested elements for clarity and understandability.

• The ‘Explore’ view presents the formula in a simple nested/rolled-up way, with the simplest top-level view of your formula first, but allowing you to drill-down to nested expressions. This enables you to quickly understand how/why it is returning the current result. Click the green bars (rolled-up results) to drill-down. Alternatively, click the ‘Expand all’, ‘Collapse all’ buttons.

• In both views you can hover over various elements (eg: functions, parameters etc) to see a popup with more information, such as definition/description, current value etc. Hovering over a range will display its current value for example.

• There are quite a few other capabilities.

[Disclosure: I am the author of FormulaDesk]

Answer 3

FormulaDesk only works on windows.

In addition to FormulaDesk above:

Here's a link to FastExcel's current Beta 3. But they seem to have been around for over a decade. $29 bucks. I think windows only. The site is unclear.

http://fastexcel.wordpress.com/2014/04/28/making-sense-of-complex-formulas-an-indenting-viewer-editer/

Similar functionality from Precision Calc. $12 Has an Nagware free version if you only need this now and then. Windows only.

http://precisioncalc.com/tf/what_is_the_formulator.html