Conceive Excel Formulas that are Easier to Write and Maintain
Are you dealing with nested Excel Formulas with a jungle of parenthesis and semicolons?
Limit the space for errors with simpler Formulas. It requires technique and hard work but here you will receive useful guidelines to achieve it.
Whatever the technique to be used you need to fully grasp the most used Excel Functions
Here are some useful articles:
- Long Excel Formula – 10 Practical Tips to Avoid It
- Long Excel Formula – Tips to Avoid Blocked Column Headers or Hidden Cells in Excel 2003
- Excel Names – More than One Reason to Use them in Formulas
- Excel SUM – Problems to Be Aware of When You Use this Common Function
- A New Explanation about an Array Formula so You can Start Creating Shortest Excel Formulas Since Now
- Excel Formulas – 3 Reasons I love Entering Arguments by Hand
- Excel Formulas – 3 Popular Ways to Enter Arguments in a Formula
- Excel Formulas – 4 Popular Ways to Debug Formulas
- Excel Formulas – 3 Proven Tactics to Find Help about How to Write a Formula
- Excel Formulas – A Way to Show Messages Instead of Results
Planning before typing will avoid you to be trapped in long and bored chains of arguments and functions. There are other techniques that will empower you to work smarter with formulas, before you know them let me tell you a short Excel Formula story…
Karen spent two weeks of hard work; she was building a complex Spreadsheet full of advanced Vlookup formulas her boss requested.
In the due date, her face changed from glory to desperation. A keystroke triggered a disaster…
The #REF! error spread like sugar over the Sheet. Now something is wrong, somewhere. How did she fix this due-date-error?
She needed to focus on the solution but…
An imminent call from her boss increased her frustration; she didn’t have clarity to solve the problem.
Working without a layout in Excel is terrible; you cannot predict where the next blast could occur.
You can avoid this unnecessary frustration by conceiving smarter formulas.
How to conceive Excel Formulas that are Easier to Write and Maintain?
Here’s how…
1. Master Excel Concepts/Functions to Build any Formula and to Make It Simpler
=A2*B2+A3*B3+A4*B4+A5*B5+A6*B6+A7*B7+A8*B8+A9*B9+A10*B10
Just simpler and more elegant.
Read these articles: Learn the Way to Show Messages Instead of Results
2. Plan First, Write Formula Next
Are you one of those Excel users who write formulas directly on the cell? Then Listen to this:
“By failing to prepare you are preparing to fail.” – Benjamin Franklin
3. Add Intermediate Steps to Limit the Depth of your Excel Formula
Build shorter Formulas by adding intermediate steps in cells, then use the intermediate ranges nested in formulas.
This technique empowers you to debug, add and read Formulas more efficiently even week, months or year ahead.
4. Never Introduce Hard Coded Numbers
Keep in mind that introducing hard coded numbers in your Formulae is a short term gain. You may say “I write it just to get a quick answer” but remember than a common question after you deliver an answer is “what if?”…
Be aware of other reasons, read: 7 Critical Reasons to Avoid Hard Coded Numbers
Other times you may introduce constants as hard coded number because you are sure they are fixed quantities, but this decision may lead to unnecessary manual work in the future and may make your formula not so much readable.
Read: How to Store Constants in a Central Location with Excel Names
5. Develop your Excel User Defined Functions Library
What looks better?
=IF(F$1=”Shift 1″;IF($B2<16;IF($C2>16;16- $B2;$C2-$B2);0);IF(F$1=”Shift 2″;IF($C2<16;0;IF($B2>=16;$C2-$B2;$C2- 16))))
Or…
=HOURSPERSHIFT(B2;C2;2)
If you are not an insane coder you like the second option.
Excel Custom Functions are very versatile but they need to be constructed according to the Excel conventions you are so accustomed, this way you can use UDFS frequently as Built-In functions, read here how to do it: Excel UDF
Return from Excel Formulas to Excel Spreadsheet Authors Home
Popularity: 6% [?]