Getting around nested formula in Excel

nest

If you work with lots of data, you must have at least used a nested formula in excel. Nested formula can be really annoying to use sometimes as you may find at the end of your statement that you have not entered all arguments or you have simply got wrong results. A nested formula in Microsoft Excel means writing a function in another function. A lot of people might just refrain from using nested formula simply because they can a lookup function such as vlookup or find. In as much as these functions will get you results, you may not necessarily get to the desired end result in one step but having to formulate additional new steps to get to the end. No matter how badly you need to stay away from nesting, it will always catch up with you. It is important to take the steps below to get your formula working correctly.

  1. Uninterrupted concentration is important when you start writing your formula. When you miss one step or argument, you cannot close your formula. Concentrating also helps you keep a mental picture of where you are in your nest.
  2. As you may already be aware, each function in excel requires some arguments. Most functions require specific arguments. The default separator for arguments in excel is usually a comma. When dealing with functions with limited arguments, you need the exact number of commas separating each argument. This is true whether you are nesting or not. Excel shows a preview of where you are with each function and the specific argument is usually in bold text (see below) and this is very useful when nesting.nested3
  3. An important thing to consider is the result of each nested function. Each excel function returns a specific result if all arguments are correctly entered. Some return true/false, others return a value, others a position and so on. This is critical as you will have to decide how the sub function handles the results of the parent function in relation to the data you are referencing.
  4. WYSIWYG! Nesting depends a lot on your knowledge of the data you are dealing with. You need to know exactly what you are looking for before you start writing your formula. For instance, if you are using a formula to validate a record with three fields returning a specific information, you need to consider using an open-ended function which can accommodate all three logical statements. If not, excel will work with the logical statement you have written.
  5. Consider how consistent you data is. If you work with data that has been manually entered, your nested statements might fail to deliver. In fact this is true for all logical functions whether or not they are nested. Special characters such as space, are important. Always pre-check your raw data and process it if you obtain from manual input. If you own the database for which you work with, you will always know of key parameters change, otherwise you will have to pre-check your data as if it is new before applying your nested formula.
  6. Always maintain a clean backup template for all your reports for which you have prepared a nested function. Use an Hlookup function to fill the fields if the need arises otherwise always ensure the number and position of each field is consistent with your template.
  7. Work through the last sub-function back to the top. This is the part where you have started your formula. Ensure that you concentrate on the most recent function. Always ignore the next parent function until you have closed the sub-function with a parenthesis. The excel function preview will always guide you back to the parent-function with by showing the function in preview as well as highlighting the needed argument as we discussed above. Continue to work through your logical statements until you are back to the initial function.

Excel helps with nested functions in many ways. The most important of them is the colored parenthesis. This helps to identify which argument or function you are closing off. You can always confirm this by looking at the animated parenthesis immediately you close it. The final parenthesis in a nested formula is always black, thus any extra closing parenthesis indicates an apparent error and the formula will not close off.

nested4

Working with nested queries can be fun especially if it solves a repetitive task in a report for you. It saves a lot of time and effort for data analysts. How you nest depends on what the expected results is. You may practice some examples from the Microsoft Office Website.