1. In G14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a Toyota insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell G13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

2. In H14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle assembly plant is Toyota City insert the MSRP, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell H13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

3. In I14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle model year is 2014 insert the MSRP, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell I13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

4 .In J14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a VW Jetta, Diesel 2000 CC insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell J13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

5. In K14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a VW sold in 2015 insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell K13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

6. In K14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a VW sold in 2015 insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell K13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

7. In M14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a Toyota Corolla with a White or Blue colour insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell M13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

8. In N14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle was sold by sales rep Mark in 2016 or 2017 insert the Sales Price, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell N13 use the appropriate function to calculate the total from the results. Format comma style, 0 decimal.

9. In O14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is Red or Metallic Red and the Sales Price is more than $22,000 insert the number 1, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell O13 use the appropriate function to count the number of results. Format comma style, 0 decimal.

10. In P14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is Metallic Blue or Metallic Red and the Sales Price is less than $20,500 insert the number 1, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell P13 use the appropriate function to count the number of results. Format comma style, 0 decimal.

11. In Q14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is a VW Golf or Totota Corolla and was sold in 2016 or 2017 and has a Gas 2500 CC engine insert the number 1, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell Q13 use the appropriate function to count the number of results. Format comma style, 0 decimal.

12. In R14: Use the VIN # code breakdown provided at the top of the worksheet as a guide to create appropriate nested functions to complete the following:

If the vehicle is Model Year 2015 or 2016 and is assembled in Cambridge, Dusseldorf or Puebla insert the number 1, otherwise the cell should be blank. Copy the function down to the other cells in the column.

In cell R13 use the appropriate function to count the number of results. Format comma style, 0 decimal.

B D E G H 1 J K L M 5 T U V w x z AA AA Vehicle type TC Toyota Corolla TY Toyota Camry VG W W Golf WJ W Jetta 88 Vodel year 11 2011 12 2012 12 2013 14 2014 15 2015 16 2016 N P Vehicle Information Number VIN Breakdown CC Engine DD Colour G1 Gas 1830 OC 20 Red G2 Gas 2500 OC 21 Metalic D2 Diesel 2003 CC 22 White 23 Metalic White 24 Bue 25 Metallic Blue EE UP ON GR NOX Assembly Plant Toyota City Cambridge Duseldar Puebla Dealer Name T1 Weston Texta T2 Highland Toyota V1 City Volkswagen V2 Nather Volkswagen Sales Rep Name 501 Mark 572 Susan 5612 603 Wassim 504 Lee 910 Rasti 911 Ming 912 John 913 Allison 1 2 3 4 5 5 6 6 7 8 9 10 11 12 13 VIN # 2 WW# AABACOUDEE 3 Enter your name in cell A1 5 Enter your student in cell 8 7 B 9 10 11 12 ear Sold Dealer Sales Rep MSRP Sales Price 14 TC13G123.JP 2013T1 504 18.778 18.837 15 VJ13GZ24MX 2014 V2 913 25.057 25,021 16 VJ15DZ24VX 2014 V2 913 27,758 30,467 17 VG1462250R 2014 V2 913 22.751 25.035 15 VG160221MX 2015 V1 913 25311 22.682 19 TY13G123.JP 2012 T1 501 25052 22889 20 V.114G221MX 2014 V2 910 25. 190 25 205 21 TC13G2210N 2013 T1 504 20.452 18,417 22 TY14G2250N 2015 T2 504 27.477 22,013 23 TY15G2220N 2014 T2 503 27,841 22,323 24 TY14G2210N 2015 T1 504 27.632 22.090 25 TC16G222ON 2017 T2 502 21,044 23,185 25 TC12G120P 2012 T2 504 18.636 15.077 27 TY16G125JP 2015 T1 503 25812 25,775 25 VJ110724VX 2010 V1 910 25881 21.431 29 TY11G2210N 2011 T1 28 743 28932 30 VG120224GR 2013 V1 910 24.654 19,826 31 TC18G1210N 2016 T2 504 19.212 19,172 32 TY12G1200N 2011 T1 501 24,857 24,781 33 VG116223MX 2012 V1 913 22 117 22265 34 VJ14GZ2 GR 2014 V2 911 25,244 20.237 35 VJ110220GR 2011 V1 910 25.775 26 821 35 VJ1602250X 2016 V2 910 25 630 28.239 37 TY11622UP 2910 T2 503 25877 26.520 35 VJ14G7210X 2014 V2 910 25335 25327 39 TY11G120JP 2010 T2 502 24575 22,161 40 TC13G2220N 2013 T2 501 20513 20,551 41 TC13G124.JP 2012 T1 502 18.758 18.893 42 TY14G122JP 2015 T2 501 25,244 22,827 43 VG14D220GR 2015 V1 910 24,752 19,971 4 VG160225GR 2016 V1 913 25,307 22.750 45 VG110224GR 2012 V1 912 24,251 21.784 45 VG110225GR 2012 V1 912 24090 19,325 47 VJ12D720GR 2013 V1 913 27 121 29,693 45 VJ150222WX 2015 V2 913 27.790 22 203 49 VJ110222GR 2010 V1 910 28 882 28,873 50 VG15G225GR 2016 V1 913 22.905 25,181 51 VG16D222MX 2017 V1 912 25,167 22,693 52 VJ120221GR 2012 V1 913 27, 162 29,829 53 VJ126225GR 2011 V2 912 24,873 22.452 54 TC12G221JP 2013 T2 501 20,400 18.274 55 VG11622214X 2011 V1 913 22274 20.004