2.1.4: Advanced Formulas and Functions

📌 Click here to view Microsoft Excel Building Proficiency Chapter Overview

---

Understanding Complex Formulas

Formulas can be simple mathematical formulas or complicated formulas involving multiple mathematical operations, multiple cell ranges and nested functions. While it is a good idea to remember the order of operations rules, complex formulas can use parentheses to identify the arguments of functions and to override the order of operations. In mathematical operation formulas, operations within parentheses are performed before those outside of it. For example, in =A3+B3*C3, B3 is multiplied by C3 before A3 is added to the result, but in =(A3+B3)*C3, A3 and B3 are added together first, then the result is multiplied by C3.

Parentheses in operations may be nested inside each other. The operation in the innermost set of parentheses will be performed first. Whether nesting parentheses in mathematical operations or in nested functions, always be sure to have as many closed parentheses in the formula as there are open parentheses, or Excel will return an error message. In the illustrated worksheet below, cell D30 contains a complex formula that calculates the differences between rooms available and rooms rented to determine the total number of unrented rooms in the Balcony and Penthouse categories. The formula in D30 is: =(SUM(C5:C8)-SUM(D5:D8))+(SUM(C25:C28)-SUM(D25:D28)). Notice the number of open and closed parenthesis!

Complex Formula

​Absolute versus Static References

One of the first things that new Excel users need to realize is that Excel is so much more powerful than a calculator. Some users insist in setting up a worksheet as if it is merely a calculator. These people fail to realize the power of cell references. Instead they use static references to constant values. A static reference is a reference to a value that does not change.

Cell References example

In the example above, instead of using static references (= 9000-3952.96) for the formula in D5, the user should employ cell references. (= B5-C5). The main benefit of use cell references is that the user can manipulate the input values in columns B and C, and the formulas in column D will remain accurate. If the user changes either input values in columns B or C with static references, the formulas in column D will need to be manually updated.

Cell referencing is an extremely useful feature when formulas need to be copied across ranges in a worksheet. When creating formulas that contain references to cells or cell ranges, the default cell reference is considered to be a relative cell reference. When formulas with these type of cell references are copied to other cells the formulas will automatically change relative to the cells that they are copied to. However, there may be instances where it is necessary for Excel to keep the exact cell referenced in a formula when copying to other cells. A cell reference that does not change when copied is called an absolute cell reference (sometimes called a fixed cell reference). The indicator that a cell reference is absolute is the presence of a dollar sign symbol in front of both the column letter and row number, such as $A$3. To create an absolute cell reference, either type the dollar sign manually, or press the F4 after entering the cell reference. Repeatedly pressing the F4 key cycles the reference through the four different combinations of relative, absolute, and mixed cell references.

Mixed cell references are a combination of relative and absolute: either the column is relative and the row fixed (absolute), for example D$2, or the column is fixed and the row relative: $D2. Mixed cell references are rarely used, but they play a significant role when it is necessary to keep a single row or column unchanged while copying the formula. Mixed cell references are commonly used when creating a table of values, like a multiplication table or a mortgage rate table.

Whenever possible, utilize relative and/or absolute references instead of static cell references.

Relative Cell Reference

In the worksheet above, the formula in C6 is using relative cell references to both the Sales amount (B6), and the Commission Rate (B3). Unfortunately, if this formula is copied (perhaps by using the fill handle) to rows 7:16, the resulting formulas will be wrong. Some cells will display zeros or a #VALUE! error, while others will display inflated amounts because the Rate percentage is being replaced by higher sales values. Therefore, the reference to B3 in the formula should be an absolute cell reference, i.e. =B6*$B$3. It may be tempting to construct the formula using a static reference to the constant value of .075 (i.e. =B6*.075). However, if the rate needs to be changed to 7.25%, the formulas with the static references would each need to be changed manually. Conversely, if the formulas in column C are using absolute cell references to $B$3, a single edit to B3 is all that is needed to update all of the cells in column C. Knowing the differences between the various type of cell reference will make the worksheet more scalable and flexible for future expansion.

---

Scenario

WorldCorp receives many client orders every day, and the corporation needs to have an accurate count of inventory to ensure that they have the capacity to deliver orders on time. This real-time inventory handling and customer relationship management can only happen by implementing computer systems that track these business activities. Microsoft Excel can be programmed to help in fulfilling these tasks with the right data. Yet, as you can imagine, this enormous data load can be daunting, and occasionally, small errors may be made in some formulas. Excel has various tools for the user to identify such errors and thus correct them.

 

Reasons to Audit Formulas

As a business grows, its activities grow in complexity, requiring higher-level and concurrent data analyses. As a result, managers build large Excel workbooks to handle these business operations, designing spreadsheets with a wide selection of formulas, functions, PivotTables, arrays, data ranges, What-If Data Tables, and other Excel data exploration tools. Each of these tools requires user input, which makes them inevitably subject to user error. The process of using Excel tools to monitor and fix errors in worksheets is called auditing formulas.

Remember that almost all formulas or functions have relationships to other areas of the workbook. Cell references or named ranges may even refer to other cell references, creating a complex web of references. If the key data points do not have the right formula or function, the resulting value will not be correct. You may also see an error message in Excel if the user settings are set to alert the user when a formula or function has a broken link or incorrect syntax.

Identifying Errors in Worksheets and Tables

There are many ways to check for errors in your workbook. These include tracing precedent functions, tracing dependent functions, showing the formula, and automated checking of errors. When tracing precedent or dependent functions, you are finding the relationships between cells.

When you are tracing precedent functions, you are finding the source of an error in a formula by locating the cells that provide the data to perform the calculation in the active cell. The arrows will point to the cells that are used to construct the formula used in the active cell so you may review them before making a correction. When you are tracing dependent functions, you are finding any cells affected by the active cell. You would want to do this before deleting a cell from a worksheet. These specifically relate to checking cell references and the information found within them. Figure 10.42 shows the output when tracing precedents for a cell.

Figure 10.42: The arrows point to cells B2 and E2 that are used to determine the highest value in the formula. (Used with permission from Microsoft)

Showing the formula is simply looking at a certain formula or function and manually checking its syntax. There is a command on the Formulas tab called Show Formulas that will automatically show which cells contain formulas. The actual formula in the cell is displayed, replacing the value that was calculated from that formula. It changes the content of the worksheet to show exactly what formulas are in the cells. This is a good first step to finding errors in formulas. To hide the formulas and return to the original spreadsheet with the computed values, simply select the Show Formulas command again. There is also an Error Checking command, which can help to identify specific errors in formulas.

Before any error checking can occur, first make sure that your version of Excel is tracking the errors when they happen. Click on the File tab and select Options. In the dialog box, choose the Formulas tab, and check the “Enable background error checking” option. You should also check all of the “Error checking rules” boxes, and then click OK, as shown in Figure 10.43.

Figure 10.43: Error checking starts with having Excel notify you if any error happens. (Used with permission from Microsoft)

Remember that there are two main characteristics that all formulas have:

1. All formulas must start with the equals sign (=).
2. Formulas have constants, cell references, or both, and operators, functions, or both.

Constants are numbers that are typed inside a formula; they can be integers or rational numbers. Operators are math notation signs for performing different calculations, such as +, –, *, /, and ^. These operators must respect the order of operations (PEMDAS). If the formula is complex—if it has multiple functions, for example—you must ensure that the function’s syntax rules are obeyed. For example, the PV function must include values for RATE, NPER, PMT, and FV, in that order, or the function will not work.

There are several types of potential errors. You may have syntax errors (not having the right argument in the function or having too few or too many arguments); capacity errors (in Excel, you cannot have more than 64 functions in a cell); or sheet or cell reference errors. It is worth noting that, when linking a formula in one worksheet to a cell in another worksheet, Excel automatically adds the sheet’s name and “!” after the sheet’s name; you must double-check that you didn’t delete this mark. For example, WorldCorp’s data for two clients is seen in Figure 10.44a. You summarize the overall total on a separate sheet, which references the first sheet. Notice in the formula the “!” after the worksheet named “data” and before the cell reference you include in the calculation (Figure 10.44b).

Figure 10.44: (a) You can use the data on one worksheet in a formula on another worksheet. (b) Excel will include the name of the worksheet followed by “!” in the formula. (Used with permission from Microsoft)

Common Error Codes

Additionally, several errors have specific code assignations that should automatically appear in your cell if you have an error. These code errors are preceded by “#” to indicate that they are errors. Some of the common code errors are described in Table 10.5.

Table 10.5: Common Code Errors These are many of the most common code errors, and how to fix them.

# Code	Explanation	How to Fix It
####	This common error is a formatting error rather than a formula error. It occurs when the column is not wide enough for the numbers to be displayed.	Make the column wider.
#NAME?	This error is displayed if the function is not well-written, such as if there is a typo in the name of the function, or the designated function name is not complete or is written incorrectly.	Find the correct wording of the function by clicking on the Insert Function tool.
#DIV/0!	This error happens when you divide by zero. This can also be caused if a linked cell reference has no value.	Enter a value in the cell, or add the correct constant in the denominator.
#NULL!	This happens when the Excel notation syntax rules are not obeyed, such as if an operator is incorrect or missing.	Follow the pop-up menu that appears when writing a function. It tells where commas should be, what value should be referenced, and so on. If the error was made in the past, you can consult the roster of functions on the Formulas tab in the ribbon and selecting the Insert Function icon.
#NUM!	This happens when a cell reference contains contradictory or unsound numbers that stop the function from calculating the result.	Check the referenced cells, and retype the numbers that are unsound.
#REF!	This means that the cell references in a formula are not there anymore, or the cell reference was entered incorrectly in the formula.	Find the correct cell reference and fix the formula.
#VALUE!	This happens when a cell reference has a different data type, for example, when you use a function that requires two numerical values but you insert text for one of them.	Use the same data types.
#N/A	This happens when a function searches for a certain value and the function cannot find it.	Change the criterion on the search operators (e.g., LOOKUP function).
#GETTING_DATA	This is a temporary error. While waiting for Excel to calculate a complicated query, Excel will process the answer in a few seconds and display this error.	Wait for Excel to calculate the answer.

---

Statistical Functions

Data surrounds us. In business, there is always a need to better understand the company, the competitors, and the customers. You gain this understanding through data collection and data analysis. The field of statistics is centered on analyzing and making sense out of data. You may have taken a statistics class at some point, but even if you have not taken a statistics class, you have probably used some statistical concepts—maybe more often than you think. Concepts such as averages and probabilities are often applicable to everyday life. For example, you may have been exposed to some statistical concepts when learning about political candidates prior to an election. This could include the percentage of registered voters of a particular demographic or the correlation between geographic location and political party. Statistics can be applied across nearly all fields and situations, and there is almost always a need to know more and dig deeper into what the data are showing.

There are two branches of statistics: descriptive and inferential. Descriptive statistics, as the name implies, describe situations. For example, the average transaction amount for purchases in a store at a specific location is descriptive information about that location. Another example of descriptive information is the turnover rate of employees in a manufacturing facility.

Inferential statistics relate to predicting and forecasting. With inferential statistics, you are using historical data and trends to forecast future performance. The regional sales manager might use the last five years of sales data to make a forecast for this year’s total sales. A nonprofit organization might use historical fundraising efforts to make predictions about fundraising for the next two years. Both branches are useful in understanding data. Information garnered from statistics can be used in a variety of ways in businesses to improve performance and track progress. Microsoft Excel has a variety of tools to make statistical analysis easier.

Basic Statistical Functions

There are several commonly used statistical functions that can give you a basic summary and understanding of the information in your spreadsheet. You can access the functions through the Formulas tab. From this tab, you can either choose Insert Function on the far left of the tab or use the grouped functions in the Function Library (Figure 11.28). The Function Library is helpful if you are not quite sure of the appropriate function for your needs. The functions are organized by application (e.g., financial) and include the ones that are most commonly used.

Figure 11.28: Functions can be found in the Insert Function tool or in the Function Library. (Used with permission from Microsoft)

Using the Insert Function command has some advantages. When you click on Insert Function, a window appears with the functions grouped by category like in the Function Library, and then listed alphabetically within each category. When you click on a function, a brief description of the function appears in the window. There is also an option to search for functions by typing a description or keyword. This is useful if you are unsure of the function’s purpose (Figure 11.29).

Figure 11.29: The Insert Function window lists all the built-in functions in Excel. (Used with permission from Microsoft)

To use functions, first, click in the empty cell where you want to put the function. Then, use either the Insert Function option or the Function Library to select the function. For this example, you will choose the AVERAGE function to calculate the average of a list of data. When you select a function from the Insert Function window, an input window will appear where you will select the range of data for analysis (Figure 11.30). After you select the range, click OK. Notice that the result of the function is displayed in the lower left of the input window. Also notice that a dotted line will surround the data identified for analysis. The function will also be visible in the Formula Bar.

Figure 11.30: The input window to insert a function will display the answer for the given function at the bottom of the window. (Used with permission from Microsoft)

When you select a function from one of the groups in the Function Library, the function will appear in the spreadsheet (Figure 11.31). You can select the data for analysis and press Enter. A dotted line will still appear around the selected range of cells, and the function still displays in the Formula Bar.

Figure 11.31: The selected cells for the function are displayed on the spreadsheet. (Used with permission from Microsoft)

One of the most commonly used functions is SUM. It even has its own button on the Home tab as the AutoSum feature (Figure 11.32). The function gives the total for a selected range of cells. Other commonly used functions in data analysis are MODE, RANGE, MEDIAN, and AVERAGE, which returns the mean of a selected range of data.

Figure 11.32: The AutoSum shortcut is a very useful tool. (Used with permission from Microsoft)

Other Useful Functions

There are several other functions available that are not specifically statistical functions, but they can be quite useful when dealing with large datasets or with text. Some of these were covered in Advanced Excel Formulas, Functions, and Techniques. These functions can help you manage and sort datasets of any size. Table 11.1 summarizes some useful functions for data analysis.

Table 11.1: Numerical Functions

Function	Definition
AVERAGEIF	Returns the average of a range that meets specific criteria
COUNT	Counts the number of entries in a range
COUNTIF	Returns the count of a range that meets specific criteria
CORR	Returns the correlation between two ranges of data
IF	Tests a condition and returns a predetermined value
MAX	Returns the maximum value in a range
MIN	Returns the minimum value in a range
SUMIF	Returns the sum of a range that meets a specific criterion
SUMIFS	Returns the sum of a range that meets multiple specific criteria

Let us look closer at the AVERAGEIF function. It returns the average of a selected range according to a set of criteria. As with other functions, it is accessed through the Insert Function button on the Data tab or from the Function Library. When selected from the Insert Function tool, an input window will appear with three arguments to fill in (Figure 11.33). In the first box, input the range to examine against the criteria. In the second box, set the criteria, and in the third box, input the range of numbers to be averaged. Both formulas and text can be entered into the criteria. Be sure to put text elements in quotation marks when establishing criteria.

Figure 11.33: The AVERAGEIF function returns the average of a selected range according to a set of criteria. (Used with permission from Microsoft)

Let’s revisit the employee vacation data and use it to find the average number of vacation days employees have remaining for the North location. The criteria range is the Location column (D2:D21), the criteria is “North,” and the average range is the Days Left column (C2:C21) (Figure 11.34). The function then returns the average number of vacation days remaining for all the employees in the list at the North location.

Figure 11.34: The function can use categorical data as criteria instead of just numerical information. (Used with permission from Microsoft)

In some cases, you might need to evaluate data using more than a single criterion. The AVERAGEIFS, COUNTIFS, and SUMIFS functions give the flexibility to add additional criteria. Just remember that each criterion must be matched with a range where the function will search (Figure 11.35). To add an additional criterion with its corresponding range to the analysis, simply press the Tab key on your keyboard and additional criteria will be added to the function arguments.

Figure 11.35: If multiple criteria are needed, use the AVERAGEIFS function. (Used with permission from Microsoft)

The functions we have discussed so far deal with numbers, but there are some functions that are useful for dealing with text. These can be particularly helpful when you are importing data into a spreadsheet. These functions can separate out parts of text or take the first several characters and put them in a separate column. Table 11.2 summarizes some useful functions for managing text in a spreadsheet.

Table 11.2: Text Functions

Function	Description
CONCATENATE	Combines information in multiple cells into one cell
LEFT	Will extract characters from the left of text
RIGHT	Will extract characters from the right of text
TRIM	Removes spaces from cells except the single space between words

The CONCATENATE function is useful when data comes from a database or other files. It combines information from multiple columns into a single column. For example, data imported into an Excel spreadsheet often contains first and last names in separate columns. With the CONCATENATE function, you can easily combine those two columns into a single column. To use the function, select the text elements that you want to combine into one cell (Figure 11.36). To include a space, in the Text2 argument insert “ ” (quotation marks with a space between). This will then output the first name last name correctly spaced (Figure 11.37). Use AutoFill to copy the CONCATENATE function through the entire list. In newer versions of Excel, the function has been replaced with CONCAT, which includes additional features.

Figure 11.36: The CONCATENATE function can be useful when importing data from a database. The CONCAT function takes data from two columns. (Used with permission from Microsoft)

Figure 11.37: Then the CONCAT function combines it into one column. (Used with permission from Microsoft)

The Text to Columns tool on the Data tab is the opposite of the CONCATENATE function. It takes a single column of text and separates it into two or more columns. This tool is also a helpful tool if you are importing information. Sometimes data imported from another program might not be in the format you desire or need for further analysis. For example, you can use this tool to place First Name and Last Name in a separate column if they are imported into a single column. Text to Columns can also be used to separate out City, State, and Zip into columns.

Spotlight on Ethics - Data Integrity

There are many ways that information and data can be manipulated to tell a different story from what is factual or to influence your interpretation of the information to slant a certain way. Simple changes such as adjusting the scaling on a chart or adding formatting changes to numbers can alter the way people interpret the information. For instance, you may want to show a larger than actual impact. You might decide to use a baseline number other than zero along the x-axis to show a much larger trend in the data than what is real. Or, perhaps you can choose to emphasize information and impacts by using very small units of measure. With today’s endless access to data, the influence of the media, and the ever-present “fake news” online, it is important to be diligent about making sure that data is accurately reported, analyzed, and presented to the intended audience.