4.2 Author formulas for complex models - Video Tutorials & Practice Problems

<v Instructor>Excel functions and formulas</v> are at the core of data processing within Excel. However, quite often, Excel users will jump to writing formulas without understanding the overall structure and objective of the final deliverable. This is not recommended for complex models that need to be maintained over the long-term. Since there are often multiple ways of implementing our requirements within Excel, it is important that before we begin writing formulas for our models, we spend some time to familiarize ourselves with the inputs for our solutions, including reference data, assumptions, input variables and source data profiles and the desired output formats, including visuals, layout and structures. Understanding the provided inputs and or desired outputs helps us to determine the intermediary steps and the appropriate data structures we should be using to simplify our model design and to introduce transparency within the necessary data transformations. We can then determine how our formulas will be copied, moved, or filled within our model. This allows us to write formulas with an appropriate mix of cell reference types in order to minimize the number of unique formulas we must write and to allow for more efficient, sustainable and reusable formulas. When implementing complex calculations, it is best to break down the problem into smaller components. While this may require us to introduce multiple steps, potentially across multiple worksheets before getting to the desired results, it will ensure that our authored models are easy to develop, understand, and maintain over the long-term. In some cases we may need to use intermediary data preparation worksheets to perform detailed calculations before summarizing the results onto a report or a summary page. This will also allow for more transparent data processing within our models. In general, we should avoid long and complex formulas and try to simplify them by exploring alternate functions, data structures, or approaches to solve the problem at hand. Keeping our formulas short and simple enhances their readability, development, and future troubleshooting activities. In the same context, it is also recommended to add some structure for our formula code. This will further improve readability and future support activities. Set structure may include a space after commas and opening brackets before closing brackets and around mathematical or logical operators like plus, minus, and percent, equal, et cetera. We can also use new lines within formulas by using the Alt + Enter key combination. Lines can typically be added within multi-parameter functions like if after logical breaks within a long formula and after specific parameters within a long function, for example, the multiple criteria range and criteria pairs for the SUMIFS function. Now let's look at some examples for how some structure within our formula code can help. For these examples, we'll use a couple of variables, A and B as listed in the top left. For our first example, we have a simple formula in cell B10, which compares the value of these two variables to then output an appropriate result. As you can see, this formula looks like a long string of text right now. Now let's try to add some of the structure elements that we just discussed. So we'll add some spaces around the brackets and operators after each parameter, and just before the final closing bracket. We will also add new lines after each parameter and then press Enter to refresh the code layout. When we press F2, we can see that the formula now is much more readable than its previous version. We can clearly identify the individual arguments for the F function, including the expressions for the true and false values. Now, let's try to implement this for some more complex formulas. Here, we'll use the same variables to look up the corresponding result values from the table above, and then sum those values to calculate the result. For the first formula, we will use matrix multiplication with the SUM function. For this example, I've already added the spaces as we discussed previously. Now let's try to identify some logical breaks within this long formula and add new lines at those locations. Typically in these cases, we'd wanna add a break just prior to an operator. So in this example, we'll add a couple of new lines just prior to the multiplication operator. As you can see by just adding those two new lines, the formula is much more readable than before. We can now easily identify the three arrays that we're using for our product. Another way of this formula can be written with the SUMIFS function. Similar to the previous example, we again have a long string of text for the formula code. However, we have multiple parameters here which can be typically used for new line breaks. Now let's look at the syntax for this function. As you can see, we have an initial sum range and then pairs of criteria ranges and their corresponding criteria, thus logical spot for the new line breaks could be after the initial sum range, and then after each criteria range and criteria pair. Again, by just adding those two new line breaks, we can easily identify the field we're calculating the sum for and the two criterias being used to calculate that sum. In addition to the layout of our models and the structure within our formula code, the following tips may also be beneficial when authoring formulas in Excel. When simultaneously reviewing the formula code for multiple cells on a spreadsheet, we can use a single code prefix to disable formula evaluation and display the formula text in the same cell. We can also use the Ctrl plus grave accent key keyboard shortcut to toggle between formula code and their resulting values on the current sheet. The grave accent key is typically found under the escape key on our keyboards. To evaluate specific parts of a formula, we can use the evaluate formula option under the formulas tab on the ribbon bar. In the resulting dialog box, we can individually evaluate different components of this formula. And in some cases, we can even step in and look at the underlying references. We can then step out, look at our calculated values and complete our evaluation. Once done, we can simply click on the close button and exit the dialog box. Another way of evaluating specific parts of a formula is by simply selecting the part we wanna evaluate and then press F9. To switch back to the formula text, we can use the Ctrl + Z keyboard shortcut. This is the typical undo shortcut on our keyboards. Lastly, we should avoid using constants within our formulas and duplicating formula code across multiple cells. In both these cases, we should instead use the defined names capability within Excel to either reference the constant from the input sheet we discussed previously or the result of the formula either entered in a cell or within the expression of a defined name. So with that, we have now covered the topics related to authoring formulas for complex models. We will be using some of these concepts to develop a data analysis solution later in this course.