3.4 Review formulas, functions, and compatibility - Video Tutorials & Practice Problems

<v Instructor>Formulas and functions are core</v> to performing calculations, analyzing data, and solving problems within Excel. At this point, we will highlight some differences between the two. We will also discuss some ways to access useful information regarding Excel functions and check for compatibility when building Excel models that might be used in different versions. Let's look at formulas first. Formulas are expressions used to calculate the value of a cell. Typically these expressions may use one or more of the following: constants, explicit cell references, defined names, structured cell references, and or built-in Excel functions. Let's review some examples of these expressions. For this first example, we have an expression with a constant value of five as entered in cell C3. Next, we have an expression that uses explicit cell references to add six to the value of cell C3. Removing the tilde from the front of the formula we can see the value once we press enter. For the next example, we use a defined name from lesson three to generate a conditional value. In the next example, we use structured references to get the value from column one and table ET TBL table one from the same row as the currently selected cell. And finally we use the built-in max function to get the maximum value from the numbers listed above. Excel functions as used in our last example are predefined formulas that are already available to use within Excel. Excel also provides the ability to write user-defined functions however, these are not within the scope of this course. When developing solutions within Excel quite often what we'll need to review the descriptions, usage, and compatibility constraints for the Excel functions. As a data analyst, we might even be required to build solutions that are compatible with our client's version of Excel, which might be different from our version. When working with Excel, we can have convenient access to such information from right within the application window. When authoring formulas we can access the Excel functions available within our version of Excel under the functions library group, within the formulas tab of the ribbon bar. We may sometimes want to review additional information about these functions to understand their usage or review their required arguments, and maybe some example use cases for additional clarity. Depending upon your preference you can use one of the following options to look up such information. To get a categorical list of Excel functions you can press F1 and then search for functions by category and click on the Excel functions by category link. As you navigate through the different categories here, you will notice that some functions have version markers next to them. These markers indicate the first version of Excel when this function was made available. If you prefer to get a complete list of Excel functions without the categorical breakdown, you can go up back into the search bar and search for functions, alphabetical, and then click on the Excel functions alphabetical link. Lastly, you can use the insert function dialog box from under the formulas tab on the ribbon bar to review the general usage of a function, the different arguments that it expects, and the definitions for each one of those arguments, the expected formula result at the bottom of the dialog box, and also a link to access additional information regarding this function. Clicking on this link brings us to the Microsoft documentation page for this function where you will see additional detail about the function, possibly some videos regarding the usage of the function, information regarding syntax, some additional examples for the usage and other notes and common problems to expect when using this function. With all this information in hand, you can determine functions best suited for a specific requirement and review other relevant information to easily incorporate them into your models.