using this file http://www.flatworldknowledge.com/sites/all/files/ManzoExcel_1.0_Chapter_3_Skills_Test.xlsx

please show all work in excel, thank you!

1. Set the Freeze Panes command so that Rows 1 and 2 and Columns A 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 I3. 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 I3 and paste it into the range I4:I17 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 O3, 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 O3 and paste it into the range O4:O17 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 J3 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:Q17. 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 B3. 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 percentage with two decimal places. Then copy the VLOOKUP function in cell D3 and paste it into the range D4:D17 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 C4:C6 when defining the arguments.

16. Copy the AVERAGEIF function in cell C3 and paste it into the range C4:C6 using the Paste Formulas command.

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 L3: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 D4:D6 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 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 B11:B13 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 J3:J17 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 K3: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:D13 using the Paste Formulas command.

27. Save the workbook by adding your name in front of the current workbook name (i.e., your name Chapter 3 Skills Test).

28. Close the workbook and Excel.