9 Logical Functions & Conditional Formatting

Logical Functions

This section reviews the use of logical functions in Excel through the construction of an investment portfolio. Although it may seem that managing investments is a specialized career choice, the reality is that almost everyone will become an investor at some point in their lives. Many companies offer employees retirement savings benefits through 401(k) or 403(b) plans. These plans allow you to deduct money from your paycheck every month, tax-free, and invest it. In addition to the tax benefits afforded by such plans, many employers match a percentage of your monthly savings or deposit money into your retirement account as an added form of compensation. When you sign up for these savings plans, your company will give you a list of options as to how your money can be invested, and you choose the type of investments you would like the company to make on your behalf. Because of this process, you become an investor. Excel can be an extremely valuable tool to help you make these investment decisions and analyze the performance of the money you have invested.

Figure 3.1 “Completed Personal Investment Portfolio Workbook” shows the completed investment portfolio workbook that we will complete in this chapter. Like the personal budget example in Chapter 2 “Mathematical Computations”, the Portfolio Summary worksheet contains a summary of the data entered or calculated in other worksheets in the workbook. This project begins by building on the Investment Detail worksheet.

Freeze Panes

Follow-along file: Excel Objective 3.00

The Investment Detail worksheet shown in Figure 3.2 “Investment Detail Worksheet” contains the majority of the information used to create the Portfolio Summary worksheet shown in Figure 3.1 “Completed Personal Investment Portfolio Workbook”. When you first open the worksheet, you will notice it is not possible to view all twenty-four columns on your computer screen. As you scroll to the right to view the rest of the columns, you will lose site of the row headings in Columns A and B. The headings in these columns show the investment that pertains to the data in Columns C through X. To solve this problem of viewing the row headings while scrolling through the remaining columns in the worksheet, we will use the Freeze Panes command.

The Freeze Panes command allows you to scroll across the Investment Detail worksheet while keeping the row headings in Columns A and B locked in place. The following steps explain how to do this:

1. Click cell C4 on the Investment Detail worksheet. We select this cell because the Freeze Panes option locks the columns to the left of the activated cell as well as the rows above the activated cell.

2. Click the View tab on the Ribbon.

3. Click the Freeze Panes button (see Figure 3.3 “Freeze Panes Command”).

4. Click the Freeze Panes option from the drop-down list of options.

Once you click the Freeze Panes option, Columns A and B are locked in place as you scroll through the columns in the worksheet. Since this is a large worksheet, you may find it easier to navigate the columns by using the arrow keys on your keyboard. However, since rows 1 and 2 contain merged cells, make sure a cell location is activated below Row 2 before you begin using the arrow keys. Figure 3.4 “Freeze Panes Command Activated on the Investment Detail Worksheet” shows the appearance of the Investment Detail worksheet after the Freeze Panes command has been activated. To deactivate the Freeze Panes command, click the Freeze Panes button again and select the Unfreeze Panes option.

Formula and Functions Review

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

We will begin developing the personal investment portfolio workbook by adding several formulas and functions. The formulas and functions we will add were illustrated in detail in Chapter 2 “Mathematical Computations”. Therefore, the steps provided in this chapter will be brief. After the formulas and functions are added to the Investment Detail worksheet, we can add the logical and lookup functions.

However, before proceeding, let’s review the investment type definitions in Table 3.1 “Investment Types in Column A of the Investment Detail Worksheet”. Table 3.1 provides a definition for each of the investment types listed in Column A of the Investment Detail worksheet. This project assumes that the personal investment portfolio comprises four types of investments. The reason we include a variety of investment types in any portfolio is to manage our total risk, or potential of losing money. When building an investment portfolio, it is important to keep in mind that investments of all types can dramatically increase or decrease in value over a short period of time. Managing risk requires that your money is not concentrated in one type of investment.

We will begin adding formulas and functions to the Investment Detail worksheet in sections. If you scroll across all the columns in the worksheet, you will notice the worksheet includes five distinct sections. Four of the five sections contain columns that need to be completed with formulas and functions before we can add the logical and lookup functions. Table 3.2 “Definitions for Columns A through G of the Investment Detail Worksheet” contains definitions for each of the columns in the Descriptive Information section (Columns A through D) and the Purchase section (Columns E through G). It will be helpful to understand the purpose of these columns as we complete this worksheet.

The Descriptive Information section of the Investment Detail worksheet (Columns A through D) contains only one blank column, which will be completed using a lookup function. Therefore, we will proceed to the Purchase section (Columns E through G) where the Cost of Purchase column is blank. The following steps explain how to enter the formula into this column:

1. Click cell G4 on the Investment Detail worksheet.

2. Type an equal sign (=).

3. Enter a formula that multiplies the Shares Purchased (cell E4) by the Purchase Price per Share (cell F4).

4. Copy the formula in cell G4.

5. Highlight the range G5:G18.

6. Click the down arrow on the Paste button in the Home tab of the Ribbon.

7. Click the Formulas button from the list of options. This is the Paste Formulas command, which pastes only the formula without any associated formats for the copied cell location.

8. While G5:G18 is highlighted format the cells for comma with no decimal places.

9. Click cell E19 on the Investment Detailworksheet.

10. Press and hold the ALT key on your keyboard, then press the equal sign (=). This is the shortcut for the Auto Sum feature.

11. Press the ENTER key on your keyboard.

12. Click cell G19 on the Investment Detail worksheet.

13. Repeat step 9.

14. Press the ENTER key on your keyboard.

Figure 3.5 “Completed Formula in the Cost of Purchase Column” shows the formula that was entered into cell G4 in the Purchase section of the Investment Detail worksheet. You can also see the results of the formula after it is pasted into the range G5:G18. The Paste Formulas option was used to paste the formula into this range so the borders would not be altered.

Table 3.3 “Definitions for Columns H through K of the Investment Detail Worksheet” shows the definitions for the Current Value section (Columns H through K) of the Investment Detail worksheet.

Before we continue with the formulas in this worksheet, we will name ranges for future use. Highlight A3:X18. On the Formulas ribbon use the Create from selection. Make sure Top Row is selected. Uncheck Left Column.

We will add a basic formula to the Current Purchase Value and Current Investment Value columns. For the Estimated Dividend Payments column, we will use the FV (future value) function to estimate the dividend payments. The rate for the dividend payments comes from the anticipated dividend yield column (D) The following explains how we add the FV function to the Estimated Dividend Payments column:

1. Click cell J4 and type an equal sign (=).

2. Type the function name FV followed by an open parenthesis (().

3. For the Rate argument, we will use the Dividend Yield divided by 12 months. From Use in Formula select Dividend Yield or start typing Dividend and double click on the range name when it appears in the dropdown selection window. type a forward slash (/) for division, and then type 12. This divides the rate in the Dividend/Yield column by 12. The length of ownership of an investment is expressed in terms of months in Column Q. Therefore, the rate for the FV function must be expressed in terms of months by dividing the annual rate by 12.

4. Type a comma.

5. For the NPER we will use the Months Owned from column Q, which contains the number of months owned or the term of the future value calculation. Type in Months_Owned

6. Type a comma followed by a zero (,0). We are not calculating an annuity or periodic investment in this example, so the PMT argument will be defined with a zero. Type a comma to advance the function to the Pv argument.

7. For the PV argument, we will use the Cost of Purchase previously calculated in column G. Type a minus sign (−) and start typing cost. Double click on the named range when it appears in the drop-down selection box This is the cost of the investment purchase

8. Type a closing parenthesis ()).

9. By itself, the FV function is calculating the total value of the investment with dividends or interest earned. To show only the amount of dividends or interest earned, we subtract the cost of the investment purchase in G4 from the result of the FV function. Type a minus sign (−) and click cell G4.

10. Press the ENTER key on your keyboard.

11. If necessary, adjust the decimal places for the output of the FV function to zero.

12. Copy the FV function in cell J4 and paste it into the range J5:J18 using the Paste Formulas command.

13. In J19, sum the column of estimated dividends.

Figure 3.6 “Completed FV Function in the Estimated Dividend Payments Column” shows the completed FV function in cell J4 of the Estimated Dividend Payments column. It is important to reduce the decimal places to zero after you enter the function into cell J4. Excel does not display the result of the function until the decimal places are removed because of the column width.

The following steps explain how to add the formulas for the Current Purchase Value and Current Investment Value columns:

1. Click cell I4 on the Investment Detail worksheet.

2. Enter a formula that multiplies the Current Price in cell H4 by the Shares Purchased in cell E4.

3. Copy the formula in cell I4 and paste it into the range I5:I18 using the Paste Formulas command. While the range is still highlighted, format for comma with zero decimal places.

4. Click cell K4 on the Investment Detail worksheet.

5. Enter a formula that adds the Current Purchase Value in cell I4 to the Estimated Dividend Payments in cell J4.

6. Copy the formula in cell K4 and paste it into the range K5:K18 using the Paste Formulas command.

7. Click cell K19 on the Investment Detail worksheet.

8. Enter a SUM function that adds the values in the rangeK4:K18.

Figure 3.7 “Completed Current Value Section of the Investment Detail Worksheet” shows the completed columns of the Current Value section in the Investment Detail worksheet. The formula used to calculate the Current Investment Value illustrates why we used the FV function to calculate the estimated dividend or interest payments for an investment. Investments that earn interest or dividends can achieve growth in two ways. The first way is through interest or dividend payments. The second way is through changes in the price paid for the investment. The formula used to calculate the Current Purchase Value is taking the number of shares purchased for each investment and multiplying it by the current market price. Therefore, the Current Investment Value takes into account any changes in the investment price by adding the purchase value at the current market price to any dividends or interest payments earned.

Table 3.4 “Definitions for Columns L through R of the Investment Detail Worksheet” provides definitions for the Percent of Portfolio section of the Investment Detail worksheet (Columns L thru R).

The Percent of Portfolio section of the Investment Detail worksheet (Columns L through R) requires two formulas and one function. The following steps explain how we add them to the worksheet:

1. Click on cell K19 and name the cell Total_Current_Value.

2. Click cell L4 in the Investment Detail worksheet.

3. Enter a formula that divides the Current Investment Value in cell K4 by the Total_Current_Value in cell K19.

4. Copy the formula and paste it into the range L5:L18 using the Paste Formulas command.

5. Click cell N4 in the Investment Detail worksheet.

6. Enter a formula that subtracts the Target Percent of Portfolio (cell M4) from the Current Percent of Portfolio (cell L4): L4−M4.

7. Copy the formula and paste it into the range N5:N18 using the Paste Formulas command.

8. Click cell Q19 in the Investment Detail worksheet.

9. Enter an AVERAGE function that calculates the average of the values in the range Q4:Q18.

Figure 3.8 “Percent of Portfolio Section of the Investment Detail Worksheet “shows the results of adding two formulas and a function to the Percent of Portfolio section of the Investment Detail worksheet. Notice the absolute reference added to the cell reference for K19 in the formula in the Current Percent of Portfolio column.

Table 3.5 “Definitions for Columns S through X of the Investment Detail Worksheet” provides definitions for the columns in the Performance Analysis section of the Investment Detail worksheet.

Most of the columns in the Performance Analysis section of the Investment Detail worksheet will be completed with formulas and functions. The following steps explain how we add them to the worksheet:

1. Click cell S4 on the Investment Detail worksheet.

2. Enter a formula that subtracts the value in the Cost of Purchase column (cell G4) from the value in the Current Investment Value column (cellK4): K4−G4.

3. Copy the formula and paste it into the range S5:S19 using the Paste Formulas command. While the range is still selected, format for comma with not decimal places. Note that this formula will be used to calculate the output for the Total row in this column. The results of the formula are showing how much money has been earned or lost for each investment. It is important to note that these gains or losses do not actually happen unless the investment is sold.

4. Click cell T4 on the Investment Detail worksheet.

5. Enter a formula that divides the Unrealized Gain/Loss (cell S4) by the Cost of Purchase (cell G4): S4/G4.

6. Copy the formula in cell T4 and paste it into the range T5:T19 using the Paste Formulas command.

7. Click cell V4 on the Investment Detail worksheet.

8. Enter a formula that divides the Percent Gain/Loss (cell T4) by the result of dividing the Months Owned (cell Q4) by 12: T4/(Q4/12). Dividing the Months Owned value by 12 expresses the amount of time an investment has been owned in terms of years. The benchmark growth rates for most investments are expressed in terms of annual return rates. Therefore, this formula must first express the amount of time an investment has been owned in terms of years. Then the total percentage gain or loss for each investment is divided by the length of ownership in years to calculate the actual annual rate of return.

9. Copy the formula in cell V4 and paste it into the range V5:V19 using the Paste Formulas command.

10. Click cell W4 on the Investment Detail worksheet.

11. Enter a formula that subtracts the Target Annual Growth Rate (cell U4) from the Actual Annual Growth Rate (cell V4): V4−U4.

12. Copy the formula in cell W4 and paste it into the range W5:W18 using the Paste Formulas command.

Figure 3.9 “Performance Analysis Section of the Investment Detail Worksheet” shows the results of the formulas added to the Performance Analysis section of the Investment Detail worksheet. This completes the required formulas and functions necessary to add before moving on to the logical and lookup functions of the chapter.

The Logical Test

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

A key component for the logical functions that will be demonstrated in this section is the logical test. A logical test is used in logical functions to evaluate the contents of a cell location. The results of the logical test can be either true or false. For example, the logical test C7 = 25 (read as “if the value in cell C7 is equal to 25”) can be either true or false depending on the value that is entered into cell C7. A logical test can be constructed with a variety of comparison operators, as shown in Table 3.6 “Comparison Operator Symbols and Definitions”. These comparison operators will be used in the logical test arguments for the logical functions demonstrated in this chapter.

A logical test will be used to evaluate the contents of a cell location in the Investment Detail worksheet. We will first demonstrate how the logical test is used to evaluate the contents of a cell location. Then we will use this logical test in the IF function, which will be demonstrated next. The following steps explain how the logical test is constructed:

1. Click cell R4 on the Investment Detail worksheet.

2. Type an equal sign (=).

3. Click cell Q4.

4. Type the greater than sign (>) followed by an equal sign(=).

5. Type the number 12. This completes the logical test, which is shown in Figure 3.10 “Logical Test Entered into the Investment Detail Worksheet”. The logical test would be stated as: “If the value in cell Q4 is greater than or equal to 12.”

6. Press the ENTER key on your keyboard. Notice that the output of the logical test is the word TRUE. This is because the value in cell Q4 is 48, which is greater than 12. 7. Copy the logical test in cell R4 and paste it into the range R5:R18 using the Paste Formulas command.

Figure 3.11 “Output of the Logical Test” shows the results of the logical test after it is pasted into the range R5:R18. Notice that for any values that are less than 12 in the range Q4:Q18, the logical test produces an output of FALSE.

IF Function

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

The IF function is used to produce a custom output based on the results of a logical test. If the results of the logical test are TRUE, the IF function will display a specific number or text, or perform a calculation. If the results of the logical test are FALSE, the IF function will display a different number or text, or perform a different calculation.

=IF(Logical_Test, Value_if_true, Value_if_false)

The arguments of the IF function are defined in Table 3.7 “Arguments for the IF Function”.

We will use the IF function in the Percent of Portfolio section of the Investment Detail worksheet. We will use the logical test that was previously demonstrated within the IF function to determine if an investment has been held for a short or long period of time. For tax purposes, an investment is considered short-term if it is held less than twelve months. This requires the investor to pay a higher tax percentage for any profit earned on the investment. An investment held twelve months or longer is considered a long- term investment. The following explains how the IF function is used to identify which investments are long term or short term:

1. Highlight the range R4:R18 on the Investment Detail worksheet and press the DELETE key on your keyboard. This will remove the logical test and allow us to replace it with an IF function.

2. Click cell R4 on the Investment Detail worksheet.

3. Click the Formulas tab on the Ribbon.

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

5. Click the IF function from the list of functions (see Figure 3.12 “Selecting the IF Function from the Function Library”). This opens the Function Arguments dialog box.

6. Click in the Logical_test argument (see Figure 3.13 “Logical_Test Argument Defined”).

7. From the Use in Formula select the range Months_Owned.

8. Type the greater than sign (>) followed by an equal sign(=).

9. Type the number 12.

Figure 3.13 “Logical_Test Argument Defined” shows the appearance of the IF Function Arguments dialog box after defining the Logical_test argument. Notice that next to the Logical_test input box, Excel shows that the results of the test are true. This makes sense given that the value in cell Q4 is 48, which is greater than 12.

10. Press the TAB key on your keyboard to advance to the next argument, which is Value_if_true.

11. Type the word Long in quotation marks. If you forget to put words or text in quotation marks using the Function Arguments dialog box, Excel will insert the quotation marks for you.

12. Press the TAB key on your keyboard to advance to the next argument, which is Value_if_false.

13. Type the word Short in quotation marks.

14. Click the OK button on the Function Arguments dialog box to complete the function.

15. Copy the IF function in cell R4 and paste it into the range R5:R18.

Figure 3.14 “Completed Function Arguments Dialog Box for the IF Function “shows the completed Function Arguments dialog box for the IF function. Notice that the results of the function are displayed in the dialog box. Since the value in cell Q4 is greater than 12, the word Long will be displayed in cell R4.

Figure 3.15 “IF Function Output” shows the completed Long/Short Indicator column on the Investment Detail worksheet. Notice the word Short is displayed for any investment held less than twelve months.

Figure 3.15 IF Function Output

Assumption Tables

Assumption tables are set up and used in Excel worksheets to avoid entering actual numbers into a function. Instead, they provide a convenient area to enter the variables in your data that might change. For instance, payments on a mortgage could be 12 (monthly), 26 (every two weeks), 4 (quarterly), or 2 (semi-annually). By providing a cell to link to in your functions, you only need to change that one cell to change the results of your formulas.

We will set up assumption tables in our Excel file. The fields in the file will be named so that the function arguments are easy to understand. Remember that naming a cell will make it an absolute cell reference. It is the equivalent of $A$1 where both the row and column reference are anchored.

The OR Function

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

The OR function is like the IF function in that it uses a logical test to evaluate the contents of a cell location. However, the OR function allows you to define several logical tests as opposed to just one.

If one of the logical tests is true, the output of the function will be the word TRUE. If all the logical tests are false, the output of the function will be the word FALSE. This differs from the IF function because the output of the function is only the word TRUE or the word FALSE. As a result, the OR function is commonly used within the IF function to enable specific outputs to be defined.

The purpose of this column is to identify any investment where either the Unrealized Gain/Loss is less than zero or the Target vs. Actual Growth Rate is less than –1%. We will use the function in the logical test of an IF function so we can define a specific output based on the results of the OR function. We will set up the assumption table and then demonstrate how the OR function works by itself, which is outlined in the following steps:

1. Click cell A21 in the Investment Detail worksheet.

2. Type Assumption Table and press Enter

3. In cell A22 type Unrealized Gain Loss <

4. In cell B22 type 0

5. In cell A23 type Actual Growth Rate <

6. In cell B23 type ( -1%) (minus 1 percent)

7. Highlight cells A22:B23, on the Formula Ribbon, Defined Names section, Create from Selection. Make sure column is checked and click OK

8. Click cell X4 and type an equal sign (=).

9. Type the function name OR and double click on the function when it appears in the function list.

10. Click cell S4 on the Investment Detail worksheet. (This is the first cell we are testing in our logical test.)

11. Type the less than symbol (<) then click on cell B22. This completes the first logical test, which is evaluating if the value in cell S4 is less than zero. The named cell Unrealized_Gain_Loss will appear in your function.

12. Type a comma. This advances the function to a second logical test.

13. Click cell W4 on the Investment Detail worksheet. (This is the second cell we are testing in our logical test.)

14. Type the less than symbol (<) then click on cell B23. (The named cell Actual_Growth_Rate will appear in your function.) This completes the second logical test, which is evaluating if the value in cell W4 is less than –1%.

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

16. Copy the OR function in cell X4 and paste it into the range X5:X18 using the Paste Formulas command.

Figure 3.16 “Completed OR Function by Itself” shows the construction and result of the OR function by itself. Notice that the only output of the function is the word TRUE or the word FALSE. If either the Unrealized Gain/Loss is less than zero or the Target vs. Actual Growth Rate is less than −1%, the function shows the word TRUE. However, these descriptions will not be helpful for the person using this worksheet. Displaying the words OK or Warning would be far more helpful in identifying investments that need to be evaluated. The only way we can return a text response to the result of the OR function is to nest it in an IF function. We can do this if we use the OR function as the logical test argument of the IF function.

The following steps explain how to accomplish this:

1. Highlight the range X4:X18 on the Investment Detail worksheet and press the DELETE key on your keyboard. We are going to start over by creating an IF function.

2. Click cell X4 on the Investment Detail worksheet.

3. Type an equal sign (=).

4. Type the function name IF followed by an open parenthesis (().

5. Type the function name OR followed by an open parenthesis ((). The OR function is being placed into the logical_test argument of this IF function.

6. Click cell S4 on the Investment Detail worksheet.

7. Type the less than symbol (<) click cell B22.

8. Type a comma. This advances the function to a second logical test.

9. Click cell W4 on the Investment Detail worksheet.

10.Type the less than symbol (<) then click cell B23.

11. Type a closing parenthesis ()).

12. Type an equal sign (=).

13. Type the word TRUE. Do not put the word inside quotation marks.

14. Type a comma. This completes the logical_test argument of the IF function. We can now go on to define the value_if_true and the value_if_false arguments. This will allow us to specify what the output of the function should be instead, using the OR function outputs of either TRUE or FALSE.

15. Type the word Warning. Be sure to enclose the word in quotation marks.

16. Type a comma. This will advance the function to the value_if_false argument.

17. Type the word OK. Be sure to enclose the word in quotation marks.

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

19. Copy the IF function in cell X4 and paste it into the range X5:X18 using the Paste Formulas command.

Figure 3.17 “OR Function in the Logical Test of the IF Function” shows the OR function within the logical_test argument of the IF function. The logical test of the IF function is now evaluating if the results of the OR function are true.

The AND Function

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

The portfolio manager wants to create a function that will help him make decisions about buying, holding, or selling different investments in the portfolio. The criteria around the decision will be:

• Buy when the fund’s Current vs Target percent of portfolio value is less than 1% AND the fund’s Unrealized Gain/Loss is greater than 0.

• Otherwise sell or hold the fund in the portfolio.

He will do that by using the AND as the logical test to decide to buy, sell or hold the fund.

The AND function is almost identical to the OR function in that it is composed of only logical tests and produces one of two possible outputs: TRUE or FALSE. However, all logical tests defined for the AND function must be true to produce a TRUE output. If one logical test is false, the function will produce a FALSE output. We will use the AND function to complete the Buy/Sell Indicator column on the Investment Detail worksheet. This column will show either the word Buy or the words Hold or Sell based on the results of the logical test argument of an IF function. We will expand our assumption table to incorporate these new parameters and then use the AND function to define the logical test argument of the IF function. The following steps explain how to accomplish this:

1. In cell A25 Type: Hold/Sell Criteria:

2. In cell A26 type Buy Current vs Target<

3. In cell B26 enter (-1%)

4. In cell A27 type Long Term >

5. In cell B27 enter (12)

6. In cell A28 enter Sell Target vs Growth >

7. In cell B28 enter 1%

8. In cell A29 enter Unrealized Gain >=

9. In cell B29 enter (0).

10. We need to name the new cells. Name the new assumption cells by highlighting A26:B29 and on the Formula ribbon, Create from selection. Make sure left column is checked and click OK.

11. Click cell P4 on the Investment Detail worksheet.

12. Type an equal sign (=).

13. Type the function name IF followed by an open parenthesis ((). The logical test we will use is the criteria given above. We will test to see if the Current vs Target percent of portfolio value is less than 1% AND the fund’s Unrealized Gain/Loss is greater than 0.

14. Type the function name AND followed by an open parenthesis ((). The AND function is being placed into the logical_test argument of this IF function.

15.Click cell N4(the fund’s current vs. target value) and then type the less than symbol (<).

16. Click cell B26. Buy Current_vs_Target named cell will appear.

17. Type a comma. This advances the AND function to the second argument in the AND function.

18. TypeS4. (Unrealized gain/loss) (Your function maybe overlapping S4 and you may not be able to click on it.)

19. Type a greater than symbol (>) followed by an equal sign (=). These symbols are used to evaluate if the value in a cell location is greater than or equal to a target value.

20. Click on cell B22. This will place your named cell Unrealized_Gain into the AND.

21. Close the AND function by closing parenthesis ())

22. Type an equal sign (=) followed by the word TRUE. Do not enclose the word in quotation marks.

23. Type a comma. This advances the IF function to the value_if_true argument.

24. Type the word Buy enclosed in quotation marks as shown in Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”. If the Current vs. Target value is less than −1% and the Unrealized Gain/Loss is greater than or equal to zero, the function will show the word Buy. In other words, if the investment is less than the desired percentage for the total portfolio and it is currently not losing money, we will buy more of that investment, so it is in line with the target percentage of the portfolio.

25. Type a comma.

26. Type the words “Hold or Sell” enclosed in quotation marks. For all other investments that are not designated with a Buy indicator, the function will show the words Hold or Sell. This indicates that an investment could either be held or sold.

27. Type the closing parenthesis ()). Then press the ENTER key on your keyboard. You should see the result “Buy” in cell P4.

28. Copy the IF function in cell P4 and paste it into the range P5:P18 using the Paste Formulas command or the Auto fill.

29. Increase the width of Column P to 12 points.

Figure 3.18 “AND Function Placed in the Logical Test of an IF Function” shows the appearance of the AND functions that has been added to the logical test of the IF functions.

Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function” shows the results of the completed AND function within an IF function after it is copied and pasted into the range P5:P18.

Simple Nested IF Functions

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

When constructing the IF function, the logical test can produce only two potential outcomes when evaluating the data in a cell. In addition, the function can produce only two possible outputs, which are defined in the value_if_true and value_if_false arguments. However, there may be situations when you need to test for several possible outcomes, which may require more than two possible outputs. To accomplish this, you need to create a nested IF function. A nested IF function is when either the value_if_true or value_if_false arguments are defined with another IF function.

For the Personal Investment workbook, a nested IF function is required to complete the Rebalance Indicator column (Column O) on the Investment Detail worksheet (see Figure 3.19 “Results of the AND Function in the Logical Test Argument of an IF Function”). The purpose of this column is to indicate where the portfolio needs to be rebalanced.

Looking at the Current vs. Target column (Column N) you can see that several investments have a significant negative number where the investment value has fallen below the target percentage for the portfolio. Other investments have a significant positive number where the investment has exceeded the target percentage for the portfolio. For this portfolio, a number greater than 1% or less than –1% will be considered significant. Therefore, we will need to assess three possible outcomes when creating a logical test that evaluates the values in Column N. Those are:

• The first test will be if the Current vs. Target value is greater than 1% we will Rebalance the portfolio.

• The second test will be if the Current vs. Target value is less than –1% we will Rebalance the portfolio.

• The third test will be if both the first test and the second test are false, we are OK with the portfolio.

Because this problem involves three tests, we need to construct a nested IF function to produce the outputs in the Rebalance Indicator column. The first thing we will do is add to our assumption table.

1. In cell A31 type Rebalance Portfolio

2. In cell A32 type Low Current vs. Target

3. In cell B32 type -1% (It is important to use the percent symbol (%) after the number 1. If you omit the percent symbol, Excel will test if the value in cell N4 is greater than 100%.)

4. In cell B33 type High Current vs. Target

5. In cell C33 type 1%

6. Name the new assumptions by highlighting cells A32:B33 and Create from selection on the Formula ribbon. Make sure Left Column only is checked.

Now that the assumption table has been created we can use the following steps to create the Nested IF:

1. Click cell O4 on the Investment Detail worksheet.

2. Type an equal sign (=).

3. Type the function name IF followed by an open parenthesis (().

Now the logical test:

4. Click cell N4.

5. Type the greater than symbol (>) then click on cell B33 or find the named cell (High_Current_vs_Target), in the Use in Formula drop-down from the Formulas ribbon.

6. Type a comma.

Now the Value_if_true:

7. Type the word Rebalance inside quotation marks. When using text data to define any of the arguments for the IF function, the text must be placed inside quotation marks.

8. Type a comma.

The Value_if_false argument will be another IF function.

9. Start another IF function by typing the function name IF followed by an open parenthesis (().

Second IF function’s logical test.

10. Click cell N4.

11. Type the less than symbol (<) then click on cell B32 or find the named cell

(Low_Current_vs_Target), in the Use in Formula drop-down from the Formulas ribbon.

12. Type a comma.

Second IF’s Value_if_true argument.

13. Type the word Rebalance inside quotation marks.

14. Type a comma.

Second IF’s Value_if_false argument.

15. Type the word OK inside quotation marks.

16. Type two closing parentheses ())). Since two IF functions were started, there are two open parentheses in the function. As a result, we need to add two closing parentheses; otherwise, Excel will produce an error message stating that a closing parenthesis is missing. Excel color codes the parenthesis so you know which function you are closing with each colored parenthesis.

17. Press the ENTER key on your keyboard.

18. Copy the nested IF function in cell O4 and paste it into the range O5:O18 using the Paste Formulas command.

Figure 3.20 “Completed Nested IF Function” shows how the completed nested IF function should appear in cell O4 of the Investment Detail worksheet. In addition, we see the results of the function after it was pasted into the range O5:O18. Notice that for any investment where the Current vs. Target value is between plus or minus 1%, the word OK appears.

Nested Logical Functions

Let’s revisit the IF(AND) function we created earlier. We will change the IF function so that it will nest a second IF and give us a Hold or Sell decision. The additional condition we are adding to our previous function is:

• Sell when

o the Current vs Target percent is greater than 1,

o the Unrealized Gain/Loss is greater than or equal to zero,

o AND the Months Owned is greater than 12.

• Otherwise Hold

Change the existing IF(AND) by:

1. Return to the Buy/Sell Indicator Column

2. Click the F2 function key to enter the edit mode. (You can also double-click in the cell.)

3. Position your cursor after the “Buy”, Make sure you are on the right side of the comma.

Now we will test to see if we should Hold or Sell by inserting a second IF function as the false result for our first IF function.

4. Type the function name IF followed by an open parenthesis (().

5. Type the function name AND followed by an open parenthesis ((). The AND function is being placed into the logical_test argument of this IF function.

6. Click cell N4 (the fund’s current vs. target value) and then type the less than symbol(=).

7. Click cell B26. Your Current_vs_Target named cell will appear.

8. Type a comma. This advances the AND function to the second logical test.

9. Click cell S4. (Unrealized gain/loss)

10. Type a greater than equal to (>=). These symbols are used to evaluate if the value in a cell location is greater than or equal to a target value.

11. Click on cell B22. This will place your named cell Unrealized_Gain_Loss into the AND.

12. Type a comma.

13. Last, we will test to see if the Months Owned is greater than 12 which will put us into the long-term investment category. Click on cell R4.

14. Type a greater than (>)

15. Click on cell B27. Long Term will show in your formula.

16. Close the parenthesis for the AND function.

17. Type an equal sign (=) followed by the word TRUE. Do not enclose the word in quotation marks. Type a comma.

18. Type the word “Sell” enclosed in quotation marks.

19. Type a comma

20. Type the word “Hold” enclosed in quotation marks. For all other investments that are not designated with a Buy indicator, the function will show the words Hold or Sell. This indicates that an investment could either be held or sold.

21. Type two closing parenthesis ())) one to close the second IF function and the second to close the first IF function.

22. Then press the ENTER key on your keyboard.

Basic Conditional Formats

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

A feature related to the skills used to create logical functions is conditional formatting. Conditional formats allow you to apply a variety of formatting treatments based on the contents of a cell location. A logical test like the ones used in the IF, AND, and OR functions is used to evaluate the contents of a cell and apply a designated formatting treatment. For example, looking at Figure 3.20 “Completed Nested IF Function”, you will notice that the Unrealized Gain/Loss column is formatted using the accounting number format. Negative numbers are enclosed in parentheses. However, to make these numbers stand out, we can use conditional formatting to change the font color to red. We will do this for the Unrealized Gain/Loss and Percent Gain/Loss columns. The following steps explain how conditional formats are applied to the cell locations in these columns:

1. Highlight the range S4:T18 on the Investment Detail worksheet.

2. Click the Conditional Formatting button in the Styles group of commands on the Home tab of the Ribbon.

3. Click the New Rule command from the list of options. This will open the New Formatting Rule dialog box.

4. At the top of the New Formatting Rule dialog box, you will find a list of options under the Select a Rule Type heading. Click the second option that states “Format only cells that contain.”

5. In the lower portions of the New Formatting Rule dialog box, you will see several dropdown boxes under the heading Edit the Rule Description. Make sure the first drop-down box is set to Cell Value.

6. Click the second drop-down box in the Edit the Rule Description section of the New Formatting Rule dialog box and select the “less than” option.

7. Click in the input box, which is next to the drop-down box that was set in the previous step and type a zero. This completes the logical test of the conditional format, which is going to evaluate if the value in any of the cells in the range S4:T18 is less than zero.

8. Click the Format button, which is near the bottom of the New Formatting Rule dialog box. This will open the Format Cells dialog box.

9. Click the drop-down box in the Color section of the Format Cells dialog box and select the red square from the color palette (see Figure 3.22 “Format Cells Dialog Box”).

10. Click the OK button at the bottom of the Format Cells dialog box.

11. Click the OK button at the bottom of the New Formatting Rule dialog box. This completes the Conditional Formatting rule that will be applied to cells in the range S4:T18.

12. Figure 3.22 “Format Cells Dialog Box” shows the Format Cells dialog box. This opens when the Format button is clicked on the New Formatting Rule dialog box. Notice the tabs running across the top of the dialog box. All formatting features in Excel are grouped by category, which can be accessed by clicking the related tab on the Format Cells dialog box. You will see some of the formatting commands in light grey.

This indicates that these commands cannot be used with the Conditional Formatting feature. You can use the Format Cells dialog box to apply any formatting features by clicking the Format Cells dialog button on the Home tab of the Ribbon.

Figure 3.23 “New Formatting Rule Dialog Box” shows the final settings for the New Formatting Rule dialog box. It is important to note that the “Format only cells that contain” option was selected in the New Formatting Rule dialog box to set a basic logical test that can be used to apply formatting commands automatically based on the values in cell locations.

Figure 3.24 “Conditional Format Applied to the Range S4:T18” shows the results of the conditional formatting rule that was applied to the range S4:T18. Notice the font color is automatically changed to red for negative numbers.