How to rewrite excel formulas in a readable manner?
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]
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
Naming some of the cells you refer to might make the whole thing more readable
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/