PRDV006: Spreadsheets II: Formatting and Functions

Up to this point, the statistical IF functions that were demonstrated provided the ability to define one criteria or logical test used to select cells from a targeted range. The next set of statistical functions that will be demonstrated provides the ability to define multiple sets of criteria for selecting cells from a targeted range. We will begin with the COUNTIFS function.

It is easy to distinguish the difference between a statistical IF function that allows one criteria argument to be defined and one that allows multiple criteria arguments. If the IF at the end of the function name is plural, you can define multiple sets of criteria arguments. Therefore, the COUNTIFS function provides the option of defining multiple sets of criteria for selecting cells from a targeted range that will be used in the function output. The arguments for the COUNTIFS function are established in pairs. For example, the first arguments for the function are Criteria_range1 and Criteria1. The function will use the Criteria1 argument to select cells in the Criteria_range1 argument. A second pair of arguments, Criteria_range2 and Criteria2, can be defined to select a subset of cell locations that were selected in the Criteria_range1 and Criteria1 arguments. This process can be repeated for several pairs of criteria arguments. We will continue to work on the Portfolio Summary worksheet by adding the COUNTIFS function to count the number of poor performing investments by investment type. The following steps explain how to add this function to the worksheet:

1. Click cell B11 on the Portfolio Summary worksheet.
2. Click the Formulas tab of the Ribbon.
3. Click the More Functions button in the Function Library group of commands.
4. Place the mouse pointer over the Statistical option from the drop-down list.
5. Click the scroll down arrow on the second drop-down list to find the COUNTIFS function (see Figure 1 Selecting the COUNTIF Function from the Function Library).
6. Click the COUNTIFS function. This will open the Function Arguments dialog box.
7. Click the Collapse Dialog button next to the Criteria_range1 argument on the Function Arguments dialog box (see Figure 9 Defined Arguments for the COUNTIFS Function).
8. Click the Investment Detail worksheet tab.
9. Highlight the range A4:A18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
10. Click in the Criteria_range1 argument input box and place an absolute reference on the range A4:A18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
11. Press the TAB key on your keyboard to advance to the Criteria1 argument and type the cell location A11. The criteria for the function will be the investment type that is entered into cell A11 on the Portfolio Summary worksheet. You will notice that when you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.
12. Click the Collapse Dialog button next to the Criteria_range2 argument on the Function Arguments dialog box (see Figure 9 Defined Arguments for the COUNTIFS Function).
13. Click the Investment Detail worksheet tab.
14. Highlight the range W4:W18 on the Investment Detail worksheet and press the ENTER key on your keyboard.
15. Click in the Criteria_range2 argument input box and place an absolute reference on the range W4:W18. This is done by typing a dollar sign ($) in front of the column letter and row number for both cells in the range.
16. Press the TAB key on your keyboard to advance to the Criteria2 argument.
17. Type an open quotation mark followed by the logical test <−1% and then type a closing quotation mark (“<−1%”). The second criterion for this function is a logical test that will identify cell locations where the value is less than −1%. For the purposes of this exercise, a poor performing investment is one that is below the target growth rate by more than 1%.
18. Click the OK button at the bottom of the Function Arguments dialog box.
19. Copy the function in cell B11 and paste it into the range B12:B14 using the Paste Formulas command.
20. Enter a SUM function in cell B15 on the Portfolio Summary worksheet that sums the values in the range B11:B14.

Why?

Use Statistical IF Functions for a Summary Worksheet

When creating a summary worksheet that summarizes detailed data from other worksheets, such as the Portfolio Summary worksheet in Figure 8 Completed Annual Growth Column in the Portfolio Summary Worksheet, it is best to use statistical IF functions. If data is added to the detailed worksheet that is being summarized, the statistical IF functions will automatically include the new data in the summary worksheet. For example, suppose a row is added below Row 7 on the Investment Detail worksheet (see Figure 3.24 "Conditional Format Applied to the Range S4:T18") and another bond investment is added. The statistical IF functions will automatically pick up the new investment and include it in the Portfolio Summary worksheet. If regular statistical functions or formulas are used, the summary worksheet can easily become inaccurate if new data is added to the detailed worksheet or if the sort order is changed.

Figure 9 Defined Arguments for the COUNTIFS Function shows the completed arguments for the COUNTIFS function in the Function Arguments dialog box. Notice the scroll bar that appears on the right side of the Function Arguments dialog box. This allows you to scroll through and define multiple pairs of criteria arguments for the function.

Figure 9 Defined Arguments for the COUNTIFS Function

Figure 10 Outputs for the COUNTIFS Functions shows the output of the COUNTIFS function on the Portfolio Summary worksheet. The criteria established for the COUNTIFS function shows that there are a total of seven investments that are underperforming in the portfolio.

Figure 10 Outputs for the COUNTIFS Functions