Menu
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.
616
By adding white space to the formula, we can expose this pattern visually. To do this with a nested IF, first expand the formula bar. We need to be able to see more than one line. Then, click into the first IF statement and use the tip window to select the value if true.
To copy the actual value instead of the formula from the cell to another worksheet or workbook, you can convert the formula in its cell to its value by doing the following: Press F2 to edit the cell. Press F9, and then press ENTER.
In your Excel worksheet, go to the Formulas tab > Formula Auditing group and click the Show Formulas button. Microsoft Excel displays formulas in cells instead of their results right away. To get the calculated values back, click the Show Formulas button again to toggle it off.
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/
131
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
