E F D B A Percent of Portfolio Portfolio Target 1 Portfolio Summary Number of Average Investment Type 2 Investments Yield 3 Bond Fund 4 Domestic Stock Fund 5 International Stock Fund 6 Individual Stock 7 8 High Profit Investments Total Investment Type Number of Unrealized Average 9 Investments Gain Yield 10 Bond Fund 11 Domestic Stock Fund 12 International Stock Fund 13 Individual Stock 14 15 Owned 25 3 40 29 3. re U Part Percent of Currebane Gay Gains Care Pro T 51597 23 B5 79 100 -25 5 867 102 759 100 5 228.79 349 10 N 5 128 SON 6.73 -1882 SIN TON . 46 SON 0372 320 981 3 1.120 23 20 50 OTS ON 0749 SON -02 52,00 TON SEN SE2070 >501 SA GON 012 3. 01.09 VON 8 2 SON 3.676 $ BON SON (14) TON 50 -10 IN Skills Test Ger Growth costat Didiel want Gain vestment Type Desert Lal Year wenye Perhe Indicato Bond Fund VICK Intermediate Investment Grade 4 GON 3710 2015 4 Dond Fund VESTX Short Term investment Grade 3204 $80105877 5 Bond Fund IWWEHX High Yield Corporate 7 20 3.5452955 Bond Fund VUSTX Long Term Treasury 4001 6500 5.620 erwional Stock Fund VOMIX Developed Manets 260 vex Dome Cock Fund 2013 5910 cu income 150 Domestick und VIBOX Small 5130131130 IN CHO Domestick und VEX Mido 904 S44 Warwionok VTRIX orale 19703607 Weronok Fun VEREX Emerging Mars 40 1359900013 POSAOCR TRY The Trees Compare 2 No 256 Moch UN Shane Johnson 2004 093101 divo MO Coca Cola 2004 1107 nullock MOFT MIO Corporation 2001 1221022007 Windstock M Inhalia Business Machine 17604 1 19 10 13.00 45 TO B D E G Investment List NO 2 Symbol Description 3 VMMXX Prime Money Market 4 VUSTX Long Term Treasury 5 VFSTX Short Term Investment Grade 6 WWEHX High Yield Corporate 7 VFICX Intermediate Investment Grade 8 VFISX Short Term Treasury 9. VBISX Short Term Bond Index 10 VEIPX Equity Income 11 VIMSX Mid Cap 12 VISGX Small Cap Growth 13 VFINX S&P 500 Index 14 VEIEX Emerging Markets 15 VDMIX Developed Markets 16 VTRIX International Value 17 MSFT Microsoft Corporation 18 AAPL Apple Inc. 19 IBM International Business Machine 20 GOOG Google Inc. Johnson and Johnson 22 KO Coca Cola 23 TRV The Travelers Companies, Inc 24 Growth Last 5 Year Investment Type Risk Level Year Growth Money Market 1 0.07% 2.45% Bond Fund 3 0.68% 3.51% Bond Fund 1 3.79% 4.92% Bond Fund 3 12.37% 6.74% Bond Fund 2 7.88% 6.63% Bond Fund 1 1.74% 4.39% Bond Fund 1 2.85% 4.98% Domestic Stock Fund 4 17.10% 3.59% Domestic Stock Fund 5 24.94% 4.39% Domestic Stock Fund 5 32.55% 5.92% Domestic Stock Fund 4 15.49% 2.54% International Stock Fund 5 17.18% 10.01% International Stock Fund 5 10.49% 1.41% International Stock Fund 5 7.69% 1.93% Individual Stock 5 -10.05% 3.49% Individual Stock 5 50.97% 324.19% Individual Stock 5 12.09% 72.65% Individual Stock 5 -4.03% 28.6096 Individual Stock 5 -3.11% - 1.05% Individual Stock 5 16.24% 58.17% Individual Stock 5 14.21% 17.64% cu un nou 21 JNJ 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive B D E Portfolio Targets Tematoma Domestic I Stock Individual Bond Fund Stock Fund Fund Stock 100.00% 0.00% 0.00% 0.00% 70.00% 15.00% 15.00% 0.00% 40.00% 20.00% 15.00% 25.00% 30.00% 20.00% 20.00% 30.00% 0.00% 0.00% 0.00% 100.00% 8 Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copled and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. 21. On the Portfolio Summary worksheet, use the COUNTIFS function in cell B10 to count the number of investments that match the investment type in cell A10 and have an unrealized gain that is greater than or equal to 20%. The function should look for and count the investment types in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range K3:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range B11:313 when defining the arguments. 22 Copy the COUNTIFS function in cell B10 and paste it into the range B11:113 using the Paste Formulas command. 23. On the Portfolio Summary worksheet, use the SUMIFS function in cell C10 to sum the Total Unrealized Gain for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the sum using the data in the range 23:17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the Percent Gain/Loss in the range K3-K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range C11:C13 when defining the arguments. 24. Copy the SUMIFS function in cell C10 and paste it into the range C11:C13 using the Paste Formulas command. 25. On the Portfolio Summary worksheet, use the AVERAGEIFS function in cell D10 to calculate the average Dividend/Yield for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the average using the data in the range E3:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range 3.K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range D11:013 when defining the arguments. 26. Copy the AVERAGEIFS function in cell D10 and paste it into the range D11 D13 using the Paste Formulas comunand. 27. Update the Title, Author, Company, and Subject document properties 1249011451Count31 and B are locked in place while scrolling through the Investment Detail worksheet. 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the Cost of Purchase in ce G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate. 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -2%, show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell 03 and paste it into the range 04:017 using the Paste Formulas command. 8. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell 3 is greater than 0. show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 1. Set the Freeze Panes command so that Rows 1 and 2 and Columns A and Bare locked in place while scrolling through the Investment Detail worksheet 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the cost of Purchase in cell G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -29 show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell 03 and paste it into the range 04:017 using the Paste Formulas command. B. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell 3 is greater than 0, show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 10. On the Investment Detail worksheet, apply a conditional format to the range Q3.017. If the Months Owned is less than 12. change the font color to red. Otherwise, the font color should remain black, 11. On the Investment Detail worksheet, enter a VLOOKUP function in cell D3 that displays the Growth Last Year for the symbol in cell 83. The growth last year for all investments can be found in Column E on the Investment List worksheet. Your function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range D4 D17 when defining the arguments 12 On the Investment Detail worksheet format the range D3.D17 to a Avenge 33901165T Count som ats ve altor peste er at DIY WETUng eng 12. On the Investment Detail worksheet, format the range D3:17 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4D17 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:B6 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E3:E17 on the Investment Detail Worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range 04:C6 when defining the arguments 16. Copy the AVERAGEIF function in cell C3 and paste it into the range 04:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet use the SUMIF function in ce D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet The function should look for a match to the investment type in the range A3 A17 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:D6 when defining the arguments 18. Copy the SUMIF function in cell D3 and paste it into the range D4D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E-6 using the Pasto Formulas comunand Count321 Son Moderate raw (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments. 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. 21. On the Portfolio Summary worksheet, use the COUNTIFS function in cell B10 to count the number of investments that match the investment type in cell A10 and have an unrealized gain that is greater than or equal to 20%. The function should look for and count the investment types in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range K3.K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range B11:B13 when defining the arguments. 22. Copy the COUNTIFS function in cell B10 and paste it into the range 811:13 using the Paste Formulas command. 23. On the Portfolio Summary worksheet use the SUMIFS function in cell C10 to sum the Total Unrealized Gain for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the sum using the data in the range 13017 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the Percent Gain/Loss in the range K3:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range C11:C13 when defining the arguments. 24. Copy the SUMIFS function in cell C10 and paste it into the range C11:C13 using the Paste Formulas command. 25. On the Portfolio Summary worksheet, use the AVERAGEIFS function in cell D10 to calculate the average Dividend/Yield for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the average using the data in the range E3:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the investment Detail worksheet where the Percent Gain/Loss in the range KB:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range D11:D13 when defining the arguments 26. Copy the AVERAGEIFS function in cell D10 and paste it into the range D11:013 using the Paste Formulas command. 27 Update the Title Author Company, and Subject document properties. Average 12400315 Count E F D B A Percent of Portfolio Portfolio Target 1 Portfolio Summary Number of Average Investment Type 2 Investments Yield 3 Bond Fund 4 Domestic Stock Fund 5 International Stock Fund 6 Individual Stock 7 8 High Profit Investments Total Investment Type Number of Unrealized Average 9 Investments Gain Yield 10 Bond Fund 11 Domestic Stock Fund 12 International Stock Fund 13 Individual Stock 14 15 Owned 25 3 40 29 3. re U Part Percent of Currebane Gay Gains Care Pro T 51597 23 B5 79 100 -25 5 867 102 759 100 5 228.79 349 10 N 5 128 SON 6.73 -1882 SIN TON . 46 SON 0372 320 981 3 1.120 23 20 50 OTS ON 0749 SON -02 52,00 TON SEN SE2070 >501 SA GON 012 3. 01.09 VON 8 2 SON 3.676 $ BON SON (14) TON 50 -10 IN Skills Test Ger Growth costat Didiel want Gain vestment Type Desert Lal Year wenye Perhe Indicato Bond Fund VICK Intermediate Investment Grade 4 GON 3710 2015 4 Dond Fund VESTX Short Term investment Grade 3204 $80105877 5 Bond Fund IWWEHX High Yield Corporate 7 20 3.5452955 Bond Fund VUSTX Long Term Treasury 4001 6500 5.620 erwional Stock Fund VOMIX Developed Manets 260 vex Dome Cock Fund 2013 5910 cu income 150 Domestick und VIBOX Small 5130131130 IN CHO Domestick und VEX Mido 904 S44 Warwionok VTRIX orale 19703607 Weronok Fun VEREX Emerging Mars 40 1359900013 POSAOCR TRY The Trees Compare 2 No 256 Moch UN Shane Johnson 2004 093101 divo MO Coca Cola 2004 1107 nullock MOFT MIO Corporation 2001 1221022007 Windstock M Inhalia Business Machine 17604 1 19 10 13.00 45 TO B D E G Investment List NO 2 Symbol Description 3 VMMXX Prime Money Market 4 VUSTX Long Term Treasury 5 VFSTX Short Term Investment Grade 6 WWEHX High Yield Corporate 7 VFICX Intermediate Investment Grade 8 VFISX Short Term Treasury 9. VBISX Short Term Bond Index 10 VEIPX Equity Income 11 VIMSX Mid Cap 12 VISGX Small Cap Growth 13 VFINX S&P 500 Index 14 VEIEX Emerging Markets 15 VDMIX Developed Markets 16 VTRIX International Value 17 MSFT Microsoft Corporation 18 AAPL Apple Inc. 19 IBM International Business Machine 20 GOOG Google Inc. Johnson and Johnson 22 KO Coca Cola 23 TRV The Travelers Companies, Inc 24 Growth Last 5 Year Investment Type Risk Level Year Growth Money Market 1 0.07% 2.45% Bond Fund 3 0.68% 3.51% Bond Fund 1 3.79% 4.92% Bond Fund 3 12.37% 6.74% Bond Fund 2 7.88% 6.63% Bond Fund 1 1.74% 4.39% Bond Fund 1 2.85% 4.98% Domestic Stock Fund 4 17.10% 3.59% Domestic Stock Fund 5 24.94% 4.39% Domestic Stock Fund 5 32.55% 5.92% Domestic Stock Fund 4 15.49% 2.54% International Stock Fund 5 17.18% 10.01% International Stock Fund 5 10.49% 1.41% International Stock Fund 5 7.69% 1.93% Individual Stock 5 -10.05% 3.49% Individual Stock 5 50.97% 324.19% Individual Stock 5 12.09% 72.65% Individual Stock 5 -4.03% 28.6096 Individual Stock 5 -3.11% - 1.05% Individual Stock 5 16.24% 58.17% Individual Stock 5 14.21% 17.64% cu un nou 21 JNJ 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive B D E Portfolio Targets Tematoma Domestic I Stock Individual Bond Fund Stock Fund Fund Stock 100.00% 0.00% 0.00% 0.00% 70.00% 15.00% 15.00% 0.00% 40.00% 20.00% 15.00% 25.00% 30.00% 20.00% 20.00% 30.00% 0.00% 0.00% 0.00% 100.00% 8 Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copled and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. 21. On the Portfolio Summary worksheet, use the COUNTIFS function in cell B10 to count the number of investments that match the investment type in cell A10 and have an unrealized gain that is greater than or equal to 20%. The function should look for and count the investment types in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range K3:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range B11:313 when defining the arguments. 22 Copy the COUNTIFS function in cell B10 and paste it into the range B11:113 using the Paste Formulas command. 23. On the Portfolio Summary worksheet, use the SUMIFS function in cell C10 to sum the Total Unrealized Gain for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the sum using the data in the range 23:17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the Percent Gain/Loss in the range K3-K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range C11:C13 when defining the arguments. 24. Copy the SUMIFS function in cell C10 and paste it into the range C11:C13 using the Paste Formulas command. 25. On the Portfolio Summary worksheet, use the AVERAGEIFS function in cell D10 to calculate the average Dividend/Yield for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the average using the data in the range E3:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range 3.K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range D11:013 when defining the arguments. 26. Copy the AVERAGEIFS function in cell D10 and paste it into the range D11 D13 using the Paste Formulas comunand. 27. Update the Title, Author, Company, and Subject document properties 1249011451Count31 and B are locked in place while scrolling through the Investment Detail worksheet. 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the Cost of Purchase in ce G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate. 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -2%, show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell 03 and paste it into the range 04:017 using the Paste Formulas command. 8. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell 3 is greater than 0. show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 1. Set the Freeze Panes command so that Rows 1 and 2 and Columns A and Bare locked in place while scrolling through the Investment Detail worksheet 2. In the Investment Detail worksheet, enter an IF function into cell 13. The output of the function should be the word Gain if the Current Investment Value in cell H3 is greater than the cost of Purchase in cell G3. Otherwise, the output of the function should be the words No Gain. 3. Copy the IF function in cell 13 and paste it into the range 14:117 using the Paste Formulas command. 4. In the Investment Detail worksheet, enter a nested IF function into cell F3. If the Dividend/Yield value in cell E3 is less than 2%, show the word Low. If the Dividend/Yield value in cell E3 is greater than or equal to 5%, show the word High. Otherwise, show the word Moderate 5. Copy the IF function in cell F3 and paste it into the range F4:F17 using the Paste Formulas command. 6. On the Investment Detail worksheet in cell 03, use the OR function within an IF function to evaluate the Current vs. Target value. If the Current vs. Target value in cell N3 is greater than 2% or less than -29 show the word REBAL. Otherwise, show the word OK. 7. Copy the IF function in cell 03 and paste it into the range 04:017 using the Paste Formulas command. B. On the Investment Detail worksheet in cell P3, use the AND function within an IF function to evaluate the Current vs. Target value and the Unrealized Gain/Loss value. If the Current vs. Target value in cell N3 is greater than 2% and if the Unrealized Gain/Loss value in cell 3 is greater than 0, show the word BUY. Otherwise, show the word HOLD. 9. Copy the IF function in cell P3 and paste it into the range P4:P17 using the Paste Formulas command. 10. On the Investment Detail worksheet, apply a conditional format to the range Q3.017. If the Months Owned is less than 12. change the font color to red. Otherwise, the font color should remain black, 11. On the Investment Detail worksheet, enter a VLOOKUP function in cell D3 that displays the Growth Last Year for the symbol in cell 83. The growth last year for all investments can be found in Column E on the Investment List worksheet. Your function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range D4 D17 when defining the arguments 12 On the Investment Detail worksheet format the range D3.D17 to a Avenge 33901165T Count som ats ve altor peste er at DIY WETUng eng 12. On the Investment Detail worksheet, format the range D3:17 to a percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4D17 using the Paste Formulas command. 13. On the Portfolio Summary worksheet, use the COUNTIF function in cell B3 to count the number of investments that match the investment type in cell A3. The function should look for and count the number of investment types in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range B4:B6 when defining the arguments. 14. Copy the COUNTIF function in cell B3 and paste it into the range B4:B6 using the Paste Formulas command. 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C3 to calculate the average Dividend/Yield for the investment type in cell A3. The function should calculate the average using the data in the range E3:E17 on the Investment Detail Worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet. Consider that this function will be copied and pasted into the range 04:C6 when defining the arguments 16. Copy the AVERAGEIF function in cell C3 and paste it into the range 04:C6 using the Paste Formulas command. 17. On the Portfolio Summary worksheet use the SUMIF function in ce D3 to calculate the Percent of Portfolio for the investment type in cell A3. The function should calculate the sum using the data in the range 13:L17 on the Investment Detail worksheet The function should look for a match to the investment type in the range A3 A17 on the investment Detail worksheet. Consider that this function will be copied and pasted into the range D4:D6 when defining the arguments 18. Copy the SUMIF function in cell D3 and paste it into the range D4D6 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Torget in cell E3. The function should look for the Investment Type in cell A3 in Row 2 of the Portfolio Targets worksheet. The function should display the percentage in the Moderate row (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments 20. Copy the HLOOKUP function in cell E3 and paste it into the range E-6 using the Pasto Formulas comunand Count321 Son Moderate raw (Row 5) for each investment type. The function should look for an exact match to the lookup value. Consider that this function will be copied and pasted into the range E4:E6 when defining the arguments. 20. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. 21. On the Portfolio Summary worksheet, use the COUNTIFS function in cell B10 to count the number of investments that match the investment type in cell A10 and have an unrealized gain that is greater than or equal to 20%. The function should look for and count the investment types in the range A3:A17 on the Investment Detail worksheet where the percent Gain/Loss in the range K3.K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range B11:B13 when defining the arguments. 22. Copy the COUNTIFS function in cell B10 and paste it into the range 811:13 using the Paste Formulas command. 23. On the Portfolio Summary worksheet use the SUMIFS function in cell C10 to sum the Total Unrealized Gain for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the sum using the data in the range 13017 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the Investment Detail worksheet where the Percent Gain/Loss in the range K3:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range C11:C13 when defining the arguments. 24. Copy the SUMIFS function in cell C10 and paste it into the range C11:C13 using the Paste Formulas command. 25. On the Portfolio Summary worksheet, use the AVERAGEIFS function in cell D10 to calculate the average Dividend/Yield for the investment type in cell A10 where the unrealized gain is greater than or equal to 20%. The function should calculate the average using the data in the range E3:E17 on the Investment Detail worksheet. The function should look for a match to the investment type in the range A3:A17 on the investment Detail worksheet where the Percent Gain/Loss in the range KB:K17 is greater than or equal to 20%. Consider that this function will be copied and pasted into the range D11:D13 when defining the arguments 26. Copy the AVERAGEIFS function in cell D10 and paste it into the range D11:013 using the Paste Formulas command. 27 Update the Title Author Company, and Subject document properties. Average 12400315 Count