How to rewrite excel formulas in a readable manner?

Q: How do I make an Excel formula more readable?

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.

Q: How do I convert a formula to normal text in Excel?

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.

Q: How do you display and edit formulas?

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.

Tags:

excel

vba

excel-formula

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?

616

asked Oct 03 '12 17:10

0x90

1 Answers

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

answered Sep 19 '22 22:09

Dick Kusleika

Related questions
                            
                                Scripting Office Outlook 2016 on Mac [closed]
                            
                                ideal way of clearing out collections
                            
                                Removing the first character of a string if it equals something
                            
                                How do I copy a range of formula values and paste them to a specific range in another sheet?
                            
                                Range() VS Cells() - run times
                            
                                Outlook VBA - Get Details Of Current Active (Or Open) Email
                            
                                Is Excel VBA the same as VB.NET
                            
                                Run a Macro from Another Workbook
                            
                                Access VBA: Is it possible to reset error handling?
                            
                                How to setup a unit test in VBA Excel Macro?
                            
                                Array in excel vba
                            
                                Working with current open email
                            
                                Regular Expressions in MS Access VBA?
                            
                                Suppress "Save As" prompt
                            
                                VBA Shell function in Office 2011 for Mac
                            
                                "Return without GoSub" when using subforms in Access
                            
                                Dim As Dictionary. Compile Error: User-defined type not defined
                            
                                Python - IronPython dilemma
                            
                                Get start range and end range of a vertically merged cell with Excel using VBA
                            
                                Copy data from another Workbook through VBA

Donate For Us

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