A, or B, or C, or D, or F)) for the first stock in row 5 (the stock whose symbol is ABC) based Insert the VLOOKUP function in cell H5 to determine the stock rating (stock rating letter, like the stock's Ranking Range in cell G5 and the information in the lookup table in cells C23:E27. Hint: The VLOOKUP function should NOT search for an exact match. There is no need for the fourth argument. Copy the inserted function in cell H5 to cells H6:H15. Insert the proper function in cell 15 to calculate the price average across all three days (Day 1 Price, Day 2 Price, and Day 3 Price) for the first stock in row 5 (the stock whose symbol is ABC). Note: Ignore the small green triangle in the upper-left corner of the cell. Stock Owyc Day 2 Pie Day 3 Price ABC P NO 31 20 Ranking Range Stock Rating Price Averaggh-Low Message 60 % 97 17 70 P 61 55 60 27 x20 92 95 4 TYS 90 30 21 on 60 4 : aws 25. 70 15 FO 90 90 14 INTR 20 14 62 70 DG " " " AT Ranking Fongy 4 The 10% 22 20% Next 40% 26 " 20 b 37 om10 IN AN Lowest Price H M M . 13 V 14 15 16 Insert the IF function in cell J5 to display the proper message (either Price Tool Too Low) based on the following rule: than or equal to 70, display the message Price Too High, otherwise, display the message If the price average in cell 15 for the first stock (the stock whose symbol is ABC) is greater Price Too Low. Copy the inserted function in cell J5 to cells J6:J15. Insert the proper function in cell K23 to find the lowest of price averages in column I (all price averages in cells 15 through 115). Insert the proper function in cell K24 to find the highest of price averages in column I (all price averages in cells 15 through 115). Create and insert a chart: a) Plot Day 1 Price, Day 2 Price, and Day 3 Price data against the Stock Symbol by inserting a 2-D Clustered Bar chart. Note: Make sure you include the column headings (like Stock Symbol, Day 1 Price, etc.) in the range for chart creation. b) Apply Style 4 to the chart. Next, apply Colorful Palette 3 style color to the chart. c) Type Daily Stock Prices for the chart title. d) Move the chart legend to Right. e) Move the whole chart and place it in cell G27 (the top left corner of the chart should be aligned with the top left border lines of cell G27). 1 2.5 2.5 A 0 17 Switch to the Buying a Boat worksheet. 18 Write your own formula (no functions) in cell G10 to calculate the Loan Amount based on the Price of Boat and Down Payment in cells G6 and G9. 2 19 Insert the PMT function in cell G11 to calculate the Quarterly Payment based on the Term, Interest Rate, and Loan Amount in cells G7, G8, and G10. 3 Auteur Recently fnd ed VLOOKUPxfx VLOOKUP . E Dene Hame Gin Formula Ta Trace Dependents A Text Date & Lokk Man Time terce 39 More funds Show Manager CreateSelection Remove Arrow Famules Checking Wind Cl Op Formula Builder Show All Functions VLOOKUP Rating Average High Low Message Lookup value wy $7567 9000 $76.33 IN Table amay under Stocks Information Day 1 Price 54000 19600 $78.00 561.00 $99.00 Day Price $71.00 500.00 SAL.CO $55.00 155.00 Day $20.00 59700 17000 560.00 179.00 4 Stock mmm 19000 54000 575.00 $10.00 $50.00 100.00 540.00 $30.00 $90.00 $90.00 545.00 580.00 $20.00 $34.00 588.00 565.00 $9000 $7000 11 1 71 Rington Te 10% No 20 24 16 27 M 2 M " " Stock Rating Lowest Price Avenge Het v Cal index num Range Jackup VLOOKUP Syntax Dure Menchus 7 named range. Insert the VLOOKUP function in cell H5 to determine the stock rating (stock rating letter, like A, or B, or C, or D, or F)) for the first stock in row 5 (the stock whose symbol is ABC) based the stock's Ranking Range in cell G5 and the information in the lookup table in cells C23:E27. Hint: The VLOOKUP function should NOT search for an exact match. There is no need for the fourth argument. 8 Copy the inserted function in cell H5 to cells H6:H15. 3 Step 12 12 Insert the IF function in cell J5 to display the proper message (either Price Too High or Price Too Low) based on the following rule: If the price average in cell 15 for the first stock (the stock whose symbol is ABC) is greater than or equal to 70, display the message Price Too High, otherwise, display the message Price Too Low. Copy the inserted function in cell J5 to cells J6:J15. Possible 3 1 13 14 Insert the proper function in cell K23 to find the lowest of price averages in column I (all price 15 averages in cells 15 through 115). Insert the proper function in cell K24 to find the highest of price averages in column I (all price averages in cells 15 through 115). 2.5 2.5 4