i am having problems formatting the cells i keep getting error codes. after number 15. i just need to check formatting.
Form 23. On the Une equi W on the investment Detail worksheet format the range 03.017 to a percentage with two decimal places. Then copy the VLOOKUP function in cells and paste it into the range 04:017 using the Paste Formulas command. On the Portfolio Summary worksheet another bullet/age in the file), use the COUNTIF function in cell 3 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 04:06 when defining the arguments 2. Copy the COUNTIF function in cell 03 and paste it into the range 14:36 using the Paste Formulas command. 25 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C 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: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 Detall worksheet. Consider that this function will be copied and pasted into the range 04:06 when defining the arguments. 16. Copy the AVERAGEIF function in cell C3 and paste it into the range C4:C6 using the Paste Formulas 17. On the Portfolio Summary worksheet, use the SUMIF function in cell 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:117 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:06 when defining the arguments. 18. Copy the SUMIF function in cell D3 and paste it into the range 04:06 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Target 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 fupetion will be copied and pasted into the range E4:E6 when defining the arguments. 6. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. . 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. 2 2.Copy the COUNTIFS function in cell B10 and paste it into the range 111:813 using the Paste Formulas command. 23. On the Portfolio Summary worksheet, use the SUMIFS function in cell C10 to sumn 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 13: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 3:17 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. Copy the SUMIFS function in cell C10 and paste it into the range C11:013 using the Paste Formulas command. 25. On the Portfolio Summary worksheet use the AVERAGEIFS function in cell 010 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: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 3:17 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:013 using the Paste Formulas command. Required: Save your file as Your_name_Exercise, and submit the Excel file via Canvas Assignment page. (No email with attachment is allowed.) a Grading: There are the following four parts in this exercise, each one is on one page/bullet item in the Ex04 worksheet: Portfolio Summary, Investment Detail, Investment List, and Portfolio Targets. Each part is accounted for 25%, 100% total, po 3 Arial Percentage - 12 - A a A $ 191 Paste BIU $ - % -8 Conditio Formattin Clipboard Font Alignment Number C3 x & fo -AVERAGEIF("Investment Detail $E$3:$E$17, 'Investment Detail'!$A$3:$AS A B D E G Percent of Portfolio Portfolio Target 1 Portfolio Summary Number of Average Investment Type 2 Investments Yield 3 Bond Fund #SPILL! 4 Domestic Stock Fund 3 #SPILLI 5 International Stock Fund 3 #SPILL! 6 Individual Stock 51 #SPILL! 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 16 17 Portfolio Summary Portfolio Targets Investment Detail Investment List Read G Type here to search O RE FA F5 F6 F7 Clipboard Font 5 Alignment Number Styles AS Moderate E F 1 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive 8 B D Portfolio Targets Ternauta Domestic I Stock Bond Fund Stock Fund Fund 100.00% 0.00% 0.00% 70.00% 15.00% 15.00% 40.00% 20.00% 15.00% 30.00% 20.00% 20.00% 0.00% 0.00% 0.00% Individual Stock 0.00% 0.00% 25.00% 30.00% 100.00% 9 10 11 12 13 15 Portfolio Summary Portfolio Targets Type here to search O - Esc F2 F3 FS P be a 04 $ 4 1 2 5 7 3 6 8 Q W Tab E R T Y U Caps Lock A S D F . G Z Shift X B N Styles Ceils Editing Ideas Sri G Skills Test N 0 P Gain Perce salget Perce 75% 7.5 100% 100% 100 CH Growth Dividen TU Investment Type Yield Symbol Cost westen Description Lau Yad Yield Stenuh Purchase Vahe Person Bond Fund Indicator Gilas Gidas VFICK termediate Investment Grade 700 4609 Moderate 21185815 Gain Bond Fund $ 1,697 B5% VESTX Short Term investment Grade 379 320 Moderate 1801058877 S Gan Bond Fund 867 1082 IWWEHX High Yield Corporate 12375 720 53.115.955 $ Bond Fund 811 WIST Long Term Tre 100% 2020 4.00 Lograte 5.5500 55626 Gain $ 125 2291 International Stock Fund OMOX Develop Markets 10:49N 204 Moderate 73405058 No Gain Domestic Stock Fund 5 (1382 VFPXEquity income 17.10% 2 SON Moderate 511075 S 11.302 No Can 23 -3209 Domestic Stock Fund VISO Cap Growth 0.309 Low 540359 Gain 2328 Domeste Stock Fund VE Mid Cap 24.94 100% Low 15560155644 41 tematon Fund VTREX intentional Value 0745 7699 2009 Modern 570 Gan 57000 Whention stock Fund VEEX 50.815 Emerging Markets 17189 Low 13935 Gain $ 2070 3 indo Bock The Companies in 1501 TRY 190 22095 5572103 Gain $ 3,498 6179 4 IMSOCK UN Johnson and Johnson 360 15700510 Dan S2024 Indick Coca Cola 18:49 2809592691 588 6 Indice SET Micros corporation 25095 M52102 SINO 114 12 individual Stock BU Intematoniness Maching 1009 Low -1 48% 5.15 ZON 11122 SO and Rebalas a he Targeo OF Owned 25 REBAL HOLD 2. REBA HOLD 37 66 REBAL HOLD -525 REBA HOLD 90 -1995 OK HOLD 43 46. REBAL HOLD 0.19 OK HOLD 45 HOLD 29 0.89 OK HOD 25 RERA BUY 1923REBAL BUY 97 BUY 50 SAS REBA BUY 100 50% 50 ZON 5.08 201507 Ox 6899 9.995 T0.1 SON PRON Portfolio Summa Portfolio Targets Investment Detail Invest RLG PM Type here to search o HT G ADO FO F12 F11 FO FB FO F5 23 P Esc & Bar # $ % 5 8 0 9 7 6 013 fx 4 B c D G 1 Investment List 1 2 Symbol Description 3 VMMXX Prime Money Market 4 VUSTX Long Term Treasury 5 VFSTX Short Term Investment Grade 6 VWEHX 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 21 JNU Johnson and Johnson 22 KO Coca Cola 23 TRY The Travelers Companies Inc 24 25 Investment Type Money Market Bond Fund Bond Fund Bond Fund Bond Fund Bond Fund Bond Fund Domestic Stock Fund Domestic Stock Fund Domestic Stock Fund Domestic Stock Fund International Stock Fund International Stock Fund International Stock Fund Individual Stock Individual Stock Individual Stock Individual Stock Individual Stock Individual Stock IndiM dual Stock Growth Risk Level Last Year 0.07% 3 0.68% 1 3.79% 3 12.37% 2 7.889 1 1.74% 1 28596 4 17.10% 5 24.94% 5 32.5596 4 15.4996) 5 17.18% 5 10.4996 5 7.69% 5 -10.0598 5 50.9796 5 12099 5 -4.0395 5 -3.1195 51 16 249 51 14.2196 5 Year Growth 245% 3.51% 4.92% 6.74% 6.63% 4.39% 4.9896 3.5996 4.39% 59295 2.5496 10.0196 1.4196 1.9395 3.4999 324.1996 72.6595 28.6096 - 10596 58. 1795 17.649 27 28 39 Investment List Portfolio Summary Investment Detail Portfolio Targets Type here to search Form 23. On the Une equi W on the investment Detail worksheet format the range 03.017 to a percentage with two decimal places. Then copy the VLOOKUP function in cells and paste it into the range 04:017 using the Paste Formulas command. On the Portfolio Summary worksheet another bullet/age in the file), use the COUNTIF function in cell 3 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 04:06 when defining the arguments 2. Copy the COUNTIF function in cell 03 and paste it into the range 14:36 using the Paste Formulas command. 25 15. On the Portfolio Summary worksheet, use the AVERAGEIF function in cell C 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: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 Detall worksheet. Consider that this function will be copied and pasted into the range 04:06 when defining the arguments. 16. Copy the AVERAGEIF function in cell C3 and paste it into the range C4:C6 using the Paste Formulas 17. On the Portfolio Summary worksheet, use the SUMIF function in cell 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:117 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:06 when defining the arguments. 18. Copy the SUMIF function in cell D3 and paste it into the range 04:06 using the Paste Formulas command. 19. On the Portfolio Summary worksheet, use the HLOOKUP function to display the Portfolio Target 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 fupetion will be copied and pasted into the range E4:E6 when defining the arguments. 6. Copy the HLOOKUP function in cell E3 and paste it into the range E4:E6 using the Paste Formulas command. . 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. 2 2.Copy the COUNTIFS function in cell B10 and paste it into the range 111:813 using the Paste Formulas command. 23. On the Portfolio Summary worksheet, use the SUMIFS function in cell C10 to sumn 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 13: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 3:17 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. Copy the SUMIFS function in cell C10 and paste it into the range C11:013 using the Paste Formulas command. 25. On the Portfolio Summary worksheet use the AVERAGEIFS function in cell 010 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: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 3:17 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:013 using the Paste Formulas command. Required: Save your file as Your_name_Exercise, and submit the Excel file via Canvas Assignment page. (No email with attachment is allowed.) a Grading: There are the following four parts in this exercise, each one is on one page/bullet item in the Ex04 worksheet: Portfolio Summary, Investment Detail, Investment List, and Portfolio Targets. Each part is accounted for 25%, 100% total, po 3 Arial Percentage - 12 - A a A $ 191 Paste BIU $ - % -8 Conditio Formattin Clipboard Font Alignment Number C3 x & fo -AVERAGEIF("Investment Detail $E$3:$E$17, 'Investment Detail'!$A$3:$AS A B D E G Percent of Portfolio Portfolio Target 1 Portfolio Summary Number of Average Investment Type 2 Investments Yield 3 Bond Fund #SPILL! 4 Domestic Stock Fund 3 #SPILLI 5 International Stock Fund 3 #SPILL! 6 Individual Stock 51 #SPILL! 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 16 17 Portfolio Summary Portfolio Targets Investment Detail Investment List Read G Type here to search O RE FA F5 F6 F7 Clipboard Font 5 Alignment Number Styles AS Moderate E F 1 2 3 Very Conservative 4 Conservative 5 Moderate 6 Aggressive 7 Very Aggressive 8 B D Portfolio Targets Ternauta Domestic I Stock Bond Fund Stock Fund Fund 100.00% 0.00% 0.00% 70.00% 15.00% 15.00% 40.00% 20.00% 15.00% 30.00% 20.00% 20.00% 0.00% 0.00% 0.00% Individual Stock 0.00% 0.00% 25.00% 30.00% 100.00% 9 10 11 12 13 15 Portfolio Summary Portfolio Targets Type here to search O - Esc F2 F3 FS P be a 04 $ 4 1 2 5 7 3 6 8 Q W Tab E R T Y U Caps Lock A S D F . G Z Shift X B N Styles Ceils Editing Ideas Sri G Skills Test N 0 P Gain Perce salget Perce 75% 7.5 100% 100% 100 CH Growth Dividen TU Investment Type Yield Symbol Cost westen Description Lau Yad Yield Stenuh Purchase Vahe Person Bond Fund Indicator Gilas Gidas VFICK termediate Investment Grade 700 4609 Moderate 21185815 Gain Bond Fund $ 1,697 B5% VESTX Short Term investment Grade 379 320 Moderate 1801058877 S Gan Bond Fund 867 1082 IWWEHX High Yield Corporate 12375 720 53.115.955 $ Bond Fund 811 WIST Long Term Tre 100% 2020 4.00 Lograte 5.5500 55626 Gain $ 125 2291 International Stock Fund OMOX Develop Markets 10:49N 204 Moderate 73405058 No Gain Domestic Stock Fund 5 (1382 VFPXEquity income 17.10% 2 SON Moderate 511075 S 11.302 No Can 23 -3209 Domestic Stock Fund VISO Cap Growth 0.309 Low 540359 Gain 2328 Domeste Stock Fund VE Mid Cap 24.94 100% Low 15560155644 41 tematon Fund VTREX intentional Value 0745 7699 2009 Modern 570 Gan 57000 Whention stock Fund VEEX 50.815 Emerging Markets 17189 Low 13935 Gain $ 2070 3 indo Bock The Companies in 1501 TRY 190 22095 5572103 Gain $ 3,498 6179 4 IMSOCK UN Johnson and Johnson 360 15700510 Dan S2024 Indick Coca Cola 18:49 2809592691 588 6 Indice SET Micros corporation 25095 M52102 SINO 114 12 individual Stock BU Intematoniness Maching 1009 Low -1 48% 5.15 ZON 11122 SO and Rebalas a he Targeo OF Owned 25 REBAL HOLD 2. REBA HOLD 37 66 REBAL HOLD -525 REBA HOLD 90 -1995 OK HOLD 43 46. REBAL HOLD 0.19 OK HOLD 45 HOLD 29 0.89 OK HOD 25 RERA BUY 1923REBAL BUY 97 BUY 50 SAS REBA BUY 100 50% 50 ZON 5.08 201507 Ox 6899 9.995 T0.1 SON PRON Portfolio Summa Portfolio Targets Investment Detail Invest RLG PM Type here to search o HT G ADO FO F12 F11 FO FB FO F5 23 P Esc & Bar # $ % 5 8 0 9 7 6 013 fx 4 B c D G 1 Investment List 1 2 Symbol Description 3 VMMXX Prime Money Market 4 VUSTX Long Term Treasury 5 VFSTX Short Term Investment Grade 6 VWEHX 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 21 JNU Johnson and Johnson 22 KO Coca Cola 23 TRY The Travelers Companies Inc 24 25 Investment Type Money Market Bond Fund Bond Fund Bond Fund Bond Fund Bond Fund Bond Fund Domestic Stock Fund Domestic Stock Fund Domestic Stock Fund Domestic Stock Fund International Stock Fund International Stock Fund International Stock Fund Individual Stock Individual Stock Individual Stock Individual Stock Individual Stock Individual Stock IndiM dual Stock Growth Risk Level Last Year 0.07% 3 0.68% 1 3.79% 3 12.37% 2 7.889 1 1.74% 1 28596 4 17.10% 5 24.94% 5 32.5596 4 15.4996) 5 17.18% 5 10.4996 5 7.69% 5 -10.0598 5 50.9796 5 12099 5 -4.0395 5 -3.1195 51 16 249 51 14.2196 5 Year Growth 245% 3.51% 4.92% 6.74% 6.63% 4.39% 4.9896 3.5996 4.39% 59295 2.5496 10.0196 1.4196 1.9395 3.4999 324.1996 72.6595 28.6096 - 10596 58. 1795 17.649 27 28 39 Investment List Portfolio Summary Investment Detail Portfolio Targets Type here to search
