10 Statistical IF Functions

Statistical IF Functions

This section will demonstrate the use of statistical IF functions. Statistical IF functions provide you with the ability to evaluate the contents in a cell location before including them in a mathematical calculation. This allows you to selectively include targeted cell locations when executing statistical calculations such as sum, average, count, and so on. We will use several statistical IF functions to construct the Portfolio Summary worksheet shown in Figure 3.1 “Completed Personal Investment Portfolio Workbook”, which contains two main sections. The Total Summary section (Rows 2 through 8) shows an overview for all investments in the portfolio by investment type. This will allow us to compare the growth performance among the Bond Funds, Domestic Stock Funds, and others. The Poor Performing Investments section (Rows 9 through 15) will provide an overview of poor performing investments by investment type. For the purposes of this exercise, we will define a poor performing investment as one where the growth rate is below the target growth rate by more than 1% (see Column W on the Investment Detail worksheet). The statistical IF functions will allow us to establish criteria to select targeted investments that can be included in the calculations for each section of the Portfolio Summary worksheet.

The COUNTIF Function

Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.08 if starting here.)

The COUNTIF function differs from the regular COUNT function in two ways. First, the regular COUNT function counts only the number of cells in a range that contain numeric data. The COUNTIF function counts the number of cells in a range that contain numeric or text data. Second, the COUNTIF function allows you to selectively count the cells in a range based on specific criteria.

The COUNTIF function contains two arguments: range and criteria.

• The range argument is defined with the range of cells that will be counted.

• The criteria argument is defined with the criteria that will be used to decide if a cell in the range should be included in the output of the function.

In the Portfolio Summary worksheet, we want to count the number of investments of each type without having to rearrange our source data accomplish the task. The advantage of using the COUNTIF function is that it does not matter how the source data is sorted. It will find a match for the criteria and add one to the running total it is calculating. We will also see the power of naming the ranges in the Investment Detail worksheet because it will make these functions much easier to construct.

The following steps explain how we can use the COUNTIF function to calculate the number of investments by investment type on the Portfolio Summary worksheet:

1. Click on the Portfolio Summary tab if the worksheet is not already active.

2. Click cell B4 on the Portfolio Summary worksheet.

3. Click the Formulas tab of the Ribbon.

4. Click the Insert Function button in the Function Library group of commands.

5. In the Search for a function box type COUNT, then click Go. A list of Excel functions that include the Count or an ability to count show in the Select a function box.

6. Click the scroll down arrow on the second drop-down list to find the COUNTIF function (see Figure 3.25 “Selecting the COUNTIF Function from the Function Library”).

7. Click the COUNTIF function. This will open the Function Arguments dialog box.

8. Click in the Range argument on the Function Arguments dialog box and from the Formulas ribbon in the Defined Names section click the drop-down arrow next to Use in Formula. (see Figure 3.26 “Completed Function Arguments Dialog Box for the COUNTIF Function”).

9. Select Investment Type from the drop-down list of named cells and ranges. After selecting it you will see a list of the investment types to the right of the Range box.

10. Press the TAB key on your keyboard to advance to the next argument, which is the Criteria argument. Then type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.

11. Click the OK button at the bottom of the Function Arguments dialog box. Figure 3.26 “Completed Function Arguments Dialog Box for the COUNTIF Function” shows the completed Function Arguments dialog box for the COUNTIF function. Notice the absolute references that were placed on each cell location in the range that was used to define the Range argument. The Criteria argument is defined with the cell A4, which means the function will only count cell locations in the range A4:A18 where the contents in the cell match the contents in cell A4.

12. Copy the function in cell B4 and paste it into the range B5:B7 using the Paste Formulas command. (You can copy the function because you have linked to the investment type in column A)

13. Enter a SUM function in cell B8 that sums the values in the range B4:B7.

Figure 3.27 “COUNTIF Function Output in the Portfolio Summary Worksheet” shows the results of the COUNTIF function after it is pasted into the range B5:B7. Because of relative referencing, the cell location used in the criteria argument is changed after the function is pasted into the range B5:B7. For example, in cell B6, the function is counting the cell locations in the range A4:A18 where the contents match the contents of cell A6. This allows you to use the function to count the number of investments per investment type. As shown in the figure, the range B4:B7 now shows the number of investments in this portfolio by investment type. Naming the range for the Range criteria creates an absolute range reference.

The AVERAGEIF Function

The AVERAGEIF Function Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.09 if starting here.) The AVERAGEIF function performs the identical mathematical calculation as the regular AVERAGE function. However, like the COUNTIF function, it allows you to define criteria that will select cells in a range that will be used in the function output. The AVERAGEIF function differs from the COUNTIF function in that it allows you to define two cell ranges instead of one.

• The first range pertains to the criteria that will be used to select cells for the function output.

• The second range contains the values that will be used to calculate the arithmetic mean.

Table 3.8 “Arguments for the AVERAGEIF and SUMIF Functions” provides definitions for the arguments contained in the AVERAGEIF and SUMIF functions.

The AVERAGEIF function will be used in the Portfolio Summary worksheet to calculate the average length of time that investments for each investment type are held. The following steps explain how to add this function to the worksheet:

1. Go to the Portfolio Summary worksheet and click cell C4.

2. Click the Formulas tab of the Ribbon.

3. Click the Insert Function button in the Function Library group of commands.

4. In the Search for a function box type AVERAGE, then click Go. A list of Excel functions that include the AVERAGE or an ability to average show in the Select a function box.

5. Click the scroll down arrow on the second drop-down list to find the AVERAGEIF function.

6. Click in the Range argument on the Function Arguments dialog box and from the Formulas ribbon in the Defined Names section click the drop-down arrow next to Use in Formula. (see Figure 3.26 “Completed Function Arguments Dialog Box for the COUNTIF Function”).

7. Select Investment Type from the drop-down list of named cells and ranges. After selecting it you will see a list of the investment types to the right of the Range box.

8. Press the TAB key on your keyboard to advance to the Criteria argument and type the cell location A4. The criteria for the function will be the investment type entered into cell A4 on the Portfolio Summary worksheet.

9. Click in the Average_Range box and from the Formula Ribbon, Defined Names section, click on the Use in Formula drop down arrow and select Months_Owned. (see Figure 3.28 “Defined Arguments for the AVERAGEIF Function”).

10. Click the OK button at the bottom of the Function Arguments dialog box.

Figure 3.28 “Defined Arguments for the AVERAGEIF Function” shows the Function Arguments dialog box for the AVERAGEIF function that will be input into cell C4. Use of named ranges creates absolute cell references in the Range and Average_range arguments. The function will evaluate the cells in the range A4:A18 using the value that exists in cell A4 on the Portfolio Summary worksheet. When a cell in the range A4:A18 meets the criteria, the function will pull the cell location in the same row from the range Q4:Q18 and include it in the average calculation.

11. Copy the function in cell C4 and paste it into the range C5:C7 using the Paste Formulas option.

12. In cell C8 create an AVERAGE function that will average the months owned for all the investments in the portfolio by typing =AVERAGE(Months_Owned). Note: You cannot add averages together because it creates a nonsense number. A number that has no meaning because it is out of context.

Figure 3.29 “AVERAGEIF Function Output on the Portfolio Summary Worksheet” shows the output of the AVERAGEIF function in the Average Months Owned column on the Portfolio Summary worksheet.

The function calculates the average months owned in Column Q on the Investment Detail worksheet where the investment type is equal to the description entered in the range A4:A7 on the Portfolio Summary worksheet.

The SUMIF Function

Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.10 if starting here.)

The SUMIF function performs the same mathematical calculation as the regular SUM function. However, like the AVERAGEIF function, this function allows you to select specific cells from a range that will be used in the output. The arguments for the SUMIF function are identical to the AVERAGEIF function except instead of averaging the result, Excel will be adding the result. (see Table 3.8 “Arguments for the AVERAGEIF and SUMIF Functions”).

We will use the SUMIF function in two columns on the Portfolio Summary worksheet. The first column will show the total investment cost for each investment type. The second column will show the total current value for each investment type. This will allow us to calculate the total annual growth rate for each investment type. The following steps explain how we will use this function to complete the first column:

1. On the Investment Detail worksheet highlight the Cost of Purchase range G3:G18. From the Formula ribbon, Defined Names section, Click on Create from Selection. Make sure top row is selected and OK.

2. Go to the Portfolio Summary worksheet and click cell D4.

3. In cell D4 type = sum. You will see a list of functions or named cells that start with the letters sum. Double click on the SUMIF from the list of functions.

4. The first argument in the function is the range that will be used to find a match to the criteria. In this case it will be the named range Investment_Type. We will access this named range by typing the name in the function. When you see the named range appear in the drop-down list of Function and named ranges, double click the named range to insert it into your function.

5. Type a comma.

6. The next argument is the Criteria, click in cell A4 and then type another comma.

7. From the Use in Formula drop down on the Formulas ribbon, select the named range Cost_of_Purchase.

8. Type your closing parenthesis ()).

9. Copy the function in cell D4 and paste it into the range D5:D7.

10. While the range D5:D7 is selected, format Comma with zero decimal places.

11. Use AutoSum to total the column in cell D8.

12. Format the top and total row with a $ and zero decimal places. Format the cells D5:D7 comma style with zero decimal places.

To complete the Current Value data on the Portfolio Summary worksheet, follow the steps above except we will use the Current Investment Value range K3:K18 from the Investment Detail worksheet and use that range in our new SUMIF.

1. On the Portfolio Summary worksheet and click cell E4.

2. In cell E4 type an = sum. You will see a list of functions or named cells that start with the letters sum. Double click on the SUMIF from the list of functions.

3. The first argument in the function is the range that will be used to find a match to the criteria. In this case it will be the named range Investment_Type. We will access this named range by typing the name in the function. When you see the named range appear in the drop-down list of Function and named ranges, double click the named range to insert it into your function.

4. Type a comma.

5. The next argument is the Criteria, click in cell A4 and then type another comma.

6. From the Use in Formula drop down on the Formulas ribbon, select the named range Current_Investment_Value.

7. Type your closing parenthesis ()).

8. Copy the function in cell E4 and paste it into the range E5:E7.

9. Use AutoSum to total the column in cell E8.

10. Format the top and total row with a $ and zero decimal places.

11. Format the cells E5:E7 comma style with zero decimal places.

Figure 3.31 “SUMIF Function Outputs in the Portfolio Summary Worksheet” shows the results of the SUMIF function in the Total Purchase Cost and Current Value columns in the Portfolio Summary worksheet.

A formula can now be added to show the annual growth for each investment category. The following steps explain how to add this formula to the Portfolio Summary worksheet:

A few things to know before you begin. When creating a percent change between an old value (in this case the Purchase Cost) and the new value (Current Value) you always subtract the old from the new, then divide by the old. The formula would look like: =(new value-old value)/old value. The (new value – old value) is the amount of change that has occurred.

Remember back to the order of operations. For this formula to work correctly Excel must subtract before it does the division. We will force our formula to do that by enclosing the subtraction inside parenthesis which will tell Excel to do the subtraction first.

The steps to accomplish this are:

1. Click cell F4 on the Portfolio Summary worksheet.

2. Type an equal sign (=) followed by two open parentheses ((().

3. Click cell E4 and type a minus sign (−).

4. Click cell D4 and type a closing parenthesis ()).

5. Type a slash (/) for division and click cellD4.

6. Type a closing parenthesis ()). This completes the first part of the formula, which is calculating the growth rate between the Total Purchase Cost (cell D4) and the Current Value (cell E4).

7. Type a slash (/) for division followed by an open parenthesis (().

8. Click cell C4, which is the Average Months Owned.

9. Type a slash (/) for division and the number 12. This part of the formula converts the number of months owned to years by dividing it by 12. This result is being divided into the growth rate, which will then show the average growth per year.

10. Type a closing parenthesis ()) and press the ENTER key on your keyboard.

11. Copy the formula in cell F4 and paste it into the range F5:F8 using the Paste Formulas command.

Figure 3.32 “Completed Annual Growth Column in the Portfolio Summary Worksheet” shows the results of the statistical IF functions that were added to the Total Summary section of the Portfolio Summary worksheet. The statistical IF functions used on this worksheet allowed us to group the details in the Investment Detail worksheet by investment type. Once this was accomplished, we added a formula to show the annual growth rate by investment type.

The COUNTIFS Function

Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.11 if starting here.)

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. These multiple sets of criteria act like an AND function. All the criteria have to be TRUE to be included in the function results. 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 criteria for determining if an investment is poor performing is if the Targeted vs. Actual Growth rate is less than -1%. The following steps explain how to add this function to the worksheet:

1. First, we will establish our assumption table. In cell A17 type Poor Performing Investment Criteria

2. In cell A18 type “Target vs Actual Growth <”.

3. In cell B18 type -1% (Note: This is for informational purposes only).

4. Click cell B11 on the Portfolio Summary worksheet.

5. Click the Formulas tab of the Ribbon.

6. Click the More Functions button in the Function Library group of commands.

7. Place the mouse pointer over the Statistical option from the drop-down list.

8. Click the scroll down arrow on the second drop-down list to find the COUNTIFS function.

9. Click the COUNTIFS function. This will open the Function Arguments dialog box.

10. Click in the Criteria_range1 argument on the Function Arguments dialog box and then from the Use in Formulas option on the Formulas ribbon select the named range Investment_Type.

11. Press the TAB key on your keyboard to advance to the Criteria1argument and click on cell A11 (Bond Fund). The criteria for the function will be the investment type that is entered into cell A11 on the Portfolio Summary worksheet. Notice that when you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.

12. Click in the Criteria_range2 argument onthe Function Arguments dialog box.

13. From the Use in Formula drop down select the named range Target_vs_Actual_Growth_Rate. You should see a string of numbers to the right of the range box.

14. Press the TAB key on your keyboard to advance to the Criteria2 argument.

15. Type an open quotation mark followed by the logical test.

16. Click the OK button at the bottom of the Function Arguments dialog box.

17. Copy the function in cell B11 into the range B12:B14.

18. Enter a SUM function in cell B15 on the Portfolio Summary worksheet that sums the values in the range B11:B14.

The AVERAGEIFS Function

Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.12 if starting here.)

The AVERAGEIFS function is like the COUNTIFS function in that multiple sets of criteria can be defined instead of one. However, the arguments for the AVERAGEIFS function are slightly different from those for the COUNTIFS function. Table 3.9 “Arguments for the AVERAGEIFS and SUMIFS Functions” provides definitions for the arguments of the AVERAGEIFS and the SUMIFS functions.

The AVERAGEIFS function will be used to calculate the average months of ownership for poor performing investments in the portfolio. The following steps explain how to add this function to the Portfolio Summary worksheet:

1. Click cell C11 on the Portfolio Summary worksheet.

2. Click the Formulas tab on 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 AVERAGEIFS function near the top of the list of functions. This will open the Function Arguments dialog box.

6. Click in the Average_range argument on the Function Arguments dialog box.

7. From the Use in Function on the Functions Ribbon select the named range Months_Owned. Selected cells from this range will be averaged by the function based on the defined criteria in ensuing arguments.

8. Click in the Criteria_range1 argument on the Function Arguments dialog.

9. From the Use in Function on the Functions Ribbon select the named range Investment_Type.

10. Press the TAB key on your keyboard to advance to the Criteria1 argument and click on cell A11. The criteria for the function will be the investment type entered into cell A11 on the Portfolio Summary worksheet. You will notice that as you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.

11. Click in the Criteria_range2 argument on the Function Arguments dialog box.

12. From the Use in Function on the Functions Ribbon select the named range Target_vs_Actual_Growth_Rate.

13. Press the TAB key on your keyboard to advance to theCriteria2argument.

14. 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 than1%.

15. Click the OK button at the bottom of the Function Arguments dialog box.

16. Copy the function in cell C11 and paste it into the range C12:C14 using the Paste Formulas command. Figure 3.35 “Completed Arguments for the AVERAGEIFS Function” shows the defined arguments for the AVERAGEIFS function. Notice that two sets of criteria arguments are defined. Values that meet all criteria defined in the function will be selected from the named range Months_Owned.

17. Enter an AVERAGEIF function in cell C15 by typing an equal sign (=) followed by the function name AVERAGEIF followed by an open parenthesis (().

18. Use the named range Target_vs_Actual_Growth_Rate to define the Range argument. Type a comma.

19. Type the following to define the criteria argument: “<–1%”. Type a comma.

20. Use the named range Months_Owned to define the [Average_range] argument. Then type a closing parenthesis ()) and press the ENTER key on your keyboard. Figure 3.36 “Results of the AVERAGEIFS Function” shows the results of the AVERAGEIFS function in the Portfolio Summary worksheet. The function shows the average months of ownership for the poor performing investments by investment type.

The SUMIFS Function

Follow-along file: Continue with Excel Objective 3.00. (Use file Excel Objective 3.13 if starting here.)

The SUMIFS function is like the AVERAGEIFS function in that multiple criteria arguments can be defined to select cells from a targeted range. The function will use the selected cells from this targeted range to calculate a total or sum.

The SUMIFS function will be used to complete the Total Purchase Cost and Current Value columns in the Poor Performing Investments section of the Portfolio Summary worksheet. The following steps explain how to construct this function for the Total Purchase Cost column:

1. Click cell D11 on the Portfolio Summary worksheet.

2. Click the Formulas tab on the Ribbon.

3. From the Insert Function select SUMIFS. This will open the Function Arguments dialog box.

4. In the Sum_range argument on the Function Arguments dialog box use the Use in Formulas to select the named range Cost_of_Purchase. Selected cells from this range will be summed based on the defined criteria in ensuing arguments.

5. Click in the Criteria_range1 argument on the Function Arguments dialog and use the named range Investment_Type from the Use in Formulas on the Formulas ribbon. Once the Criteria_range1argument is defined, the Criteria1 argument will appear in the Function Arguments dialogbox.

6. Press the TAB key on your keyboard to advance to the Criteria1argument 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 as soon as you define this argument, theCriteria_range2 argument will appear on the Function Arguments dialog box.

7. In the Criteria_range2 argument onthe Function Arguments dialog box use the named range Target_vs_Actual_Growth_Rate

8. Press the TAB key on your keyboard to advance to theCriteria2argument.

9. 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%.

10. Click the OK button at the bottom of the Function Arguments dialog box.

11. Copy the function in cell D11 into the range D12:D14. Format the cells D12LD14 with a comma style and no decimal places.

12. Enter a regular SUM function in cell D15 on the Portfolio Summary worksheet that sums the values in the range D11:D14.

13. Figure 3.37 “Completed Arguments for the SUMIFS Function” shows the defined arguments for the SUMIFS function. Values that meet all criteria defined in the function will be selected from the named range Cost_of_Purchase which is the range G4:G18. From the Investment Detail worksheet. Notice that the results for each argument of the function are shown along the right side of the Collapse Dialog buttons.

Figure 3.38 “SUMIFS Function Output for the Total Purchase Cost Column” shows the results of the SUMIFS function used to complete the Total Purchase Cost column. In total, over $45,000 was invested in funds and stocks that are not meeting the performance goals of the portfolio.

In addition to the Total Purchase Cost column, the SUMIFS function will also be used to complete the Current Value column for the Poor Performing Investments section on the Portfolio Summary worksheet. Notice that the Sum ranges change, but the criteria ranges and the criteria remain the same as the other functions looking at the poor performance based on the criteria outlined in cell A18.

The following steps explain how to add the function to the worksheet to complete this column:

1. Click cell D11 on the Portfolio Summary worksheet.

2. Click the Formulas tab on the Ribbon.

3. From the Insert Function select SUMIFS. This will open the Function Arguments dialog box.

4. In the Sum_range argument on the Function Arguments dialog box use the Use in Formulas to select the named range Current_Investment_Value. Selected cells from this range will be summed based on the defined criteria in ensuing arguments.

5. Click in the Criteria_range1 argument on the Function Arguments dialog and use the named range Investment_Type from the Use in Formulas on the Formulas ribbon. Once the Criteria_range1argument is defined, the Criteria1 argument will appear in the Function Arguments dialog box.

6. Press the TAB key on your keyboard to advance to the Criteria1argument 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 as soon as you define this argument, the Criteria_range2 argument will appear on the Function Arguments dialog box.

7. In the Criteria_range2 argument onthe Function Arguments dialog box use the named range Target_vs_Actual_Growth_Rate.

8. Press the TAB key on your keyboard to advance to theCriteria2 argument.

9. 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%.

10. Click the OK button at the bottom of the Function Arguments dialog box.

11. Copy the function in cell D11 into the range D12:D14. Format the cells D12LD14 with a comma style and no decimal places.

12. Enter a regular SUM function in cell D15 on the Portfolio Summary worksheet that sums the values in the range D11:D14.

Now that the Total Purchase Cost and Current Value columns are completed for the Poor Performing Investments section on the Portfolio Summary worksheet, we can add a formula to show the annual growth rate by investment type. Since this is the same formula that was used in the Total Summary section of the Portfolio Summary worksheet, we can just copy and paste it. The following steps explain how to do this:

1. Copy the formula in cell F4 on the Portfolio Summary worksheet.

2. Highlight the range F11:F15 on the Portfolio Summary worksheet.

3. Paste the formula using the Paste Formulas command.

Figure 3.41 “Completed Poor Performing Investments Section of the Portfolio Summary Worksheet” shows the completed Poor Performing Investments section on the Portfolio Summary worksheet. Notice that even though an investment is considered “poor performing” it does not mean that money is lost on the investment. As shown in the figure, only one investment in the International Stock Fund category is losing money, with an annual growth rate of −5.4%. However, the total annual growth rate for all investments in the Poor Performing section is 2.2% (see cell F15). This is less than half the growth rate for the overall portfolio, which is 6.2% (see cell F8). A final observation is that all the investments in the Domestic Stock Fund category are “poor performing.” The same number of investments in the Total Summary section appears in the Poor Performing Investments section. This completes the Portfolio Summary worksheet except for one column. We will complete this column in the next section with a lookup function.