Nested Functions in MS Excel
A nested function in MS Excel is series of function(s) that are defined within each other. In most cases, they are applied when one wants to carry out a multiple computation at once. An example of such computation include; logical tests, decision tests etc. One of the hectic things when dealing with nested function is how to insert functions within other function. More specifically the point at which one should close the function brackets. Chances of missing the order of opening and closing brackets in Excel are very high since the formula bar is small to visualize a long formulas. If you miss the sequence of brackets in a nested function, an error will automatically be thrown. This is something that you need to avoid as much as possible in order to save data modelling time. To avoid this, Excel Function box provides a better way of dealing with function’s brackets.
Assuming you want to make a decision for profiling customers based on the loyalty. The classifications fall in the following categories; premium, latent, inertia, and no loyalty. MS Excel IF function plays a key role in determining the classification of data values. Column A is the number of years of customer engagement and Column B is the classification of every customer. Customers with more than 7 years of engagement fall under premium loyalty, between 4 and 7 fall under latent loyalty, 1 to 4 fall in inertia loyalty and less than one year falls in no loyalty category.
- Navigate to cell C2 to enter the IF function to make the decision. Instead of typing the formula manually, click on the function box drop down to select IF function.
- To nest the functions, select the function drop down list and select the function to nest. You can use the auto-fill handle to auto populate the rest of the values.
With this simple procedure you avoid inserting opening and closing brackets in Excel nested functions. This saves you from manually entering the opening and closing formulas thus minimizing chances of getting errors in nested formulas computation. You also save time by avoiding error debugging thus increasing workplace productivity. Creating error-free nested formulas is a recipe of creating robust and dynamic decision models in excel. To learn more about MS Excel functions and time saving tricks, please plan to join our up coming Advanced MS Excel training.