Question

Using the Vehicle Ratings Excel file, create formulas using nested IF, AND, and OR functions to implement the three rating schemes described on the spreadsheet.

Rating 1

If the vehicle has A/C and a sunroof or it is newer than 2013, then YES, otherwise NO.

Rating 2

If the vehicle is Red and does not have high miles, then YES, otherwise if it is a Ford or Chevy, MAYBE, otherwise NO.

Rating 3

If the vehicle is older than 2013 and is priced under $15,000 or it is a Honda with a sunroof, then YES, otherwise, if the vehicle is a black Accord or black Corolla, then MAYBE, otherwise NO.

Make	Model	Year	Color	A/C	Sunroof	Mileage	High Miles	Price	Rating 1	Rating 2	Rating 3
Toyota	Corolla	2009	Silver	No	Yes	73,497	No	$10,497
Chevrolet	Malibu	2012	Blue	No	Yes	84,690	No	$11,489
Ford	Fusion	2014	Black	Yes	No	109,308	Yes	$11,815
Honda	Accord	2013	Red	No	No	85,353	No	$12,493
Ford	Focus	2014	Black	Yes	No	103,742	Yes	$12,507
Toyota	Corolla	2014	Black	No	Yes	109,295	Yes	$12,593
Honda	Civic	2012	White	Yes	Yes	119,522	Yes	$13,333
Chevrolet	Impala	2013	Blue	Yes	No	108,226	Yes	$13,630
Chevrolet	Impala	2009	Blue	Yes	Yes	111,691	Yes	$13,980
Ford	Focus	2012	Black	No	Yes	75,772	No	$14,251
Honda	Accord	2012	Silver	Yes	No	75,220	No	$14,258
Chevrolet	Malibu	2012	Blue	No	No	81,587	No	$15,246
Ford	Fusion	2010	Red	No	Yes	79,049	No	$15,790
Honda	Civic	2009	Blue	Yes	No	88,548	No	$16,036
Toyota	Camry	2013	Silver	Yes	Yes	115,050	Yes	$16,344
Honda	Accord	2013	Silver	No	No	77,072	No	$16,355
Chevrolet	Malibu	2011	Blue	No	Yes	82,792	No	$16,556
Toyota	Camry	2010	Red	Yes	Yes	88,163	No	$17,248
Chevrolet	Silverado	2009	White	No	No	100,179	Yes	$17,964
Toyota	Corolla	2013	Blue	Yes	Yes	117,039	Yes	$17,965
Honda	Civic	2012	Red	Yes	No	73,533	No	$19,722
Honda	Civic	2011	White	Yes	No	88,786	No	$19,864
Chevrolet	Impala	2011	Silver	Yes	Yes	77,060	No	$20,339
Ford	F-150	2014	Red	Yes	No	105,489	Yes	$20,380
Ford	Fusion	2013	Silver	No	No	109,223	Yes	$20,532
Ford	F-150	2012	Red	No	No	76,025	No	$20,659
Honda	Accord	2010	Blue	Yes	No	76,701	No	$21,138
Chevrolet	Silverado	2014	Silver	Yes	No	72,319	No	$21,148
Chevrolet	Malibu	2013	White	No	No	117,518	Yes	$21,183
Chevrolet	Silverado	2009	Black	No	Yes	101,839	Yes	$21,226
Chevrolet	Malibu	2014	Blue	Yes	No	80,179	No	$21,466
Toyota	Camry	2010	Blue	No	Yes	74,937	No	$21,976
Ford	F-150	2011	Black	Yes	Yes	117,249	Yes	$22,883
Ford	Focus	2014	Silver	Yes	No	77,527	No	$23,235
Ford	Fusion	2011	White	Yes	Yes	81,907	No	$23,835

Answer 1

Answer
Make	Model	Year	Color	A/C	Sunroof	Mileage	High Miles	Price	Rating 1	Rating 2	Rating 3
Toyota	Corolla	2009	Silver	No	Yes	73,497	No	$10,497	NO	NO	YES
Chevrolet	Malibu	2012	Blue	No	Yes	84,690	No	$11,489	NO	MAYBE	YES
Ford	Fusion	2014	Black	Yes	No	109,308	Yes	$11,815	NO	MAYBE	NO
Honda	Accord	2013	Red	No	No	85,353	No	$12,493	NO	YES	MAYBE
Ford	Focus	2014	Black	Yes	No	103,742	Yes	$12,507	NO	MAYBE	NO
Toyota	Corolla	2014	Black	No	Yes	109,295	Yes	$12,593	NO	NO	MAYBE
Honda	Civic	2012	White	Yes	Yes	119,522	Yes	$13,333	NO	NO	YES
Chevrolet	Impala	2013	Blue	Yes	No	108,226	Yes	$13,630	NO	MAYBE	NO
Chevrolet	Impala	2009	Blue	Yes	Yes	111,691	Yes	$13,980	NO	MAYBE	YES
Ford	Focus	2012	Black	No	Yes	75,772	No	$14,251	NO	MAYBE	YES
Honda	Accord	2012	Silver	Yes	No	75,220	No	$14,258	NO	NO	YES
Chevrolet	Malibu	2012	Blue	No	No	81,587	No	$15,246	NO	MAYBE	NO
Ford	Fusion	2010	Red	No	Yes	79,049	No	$15,790	NO	YES	NO
Honda	Civic	2009	Blue	Yes	No	88,548	No	$16,036	NO	NO	NO
Toyota	Camry	2013	Silver	Yes	Yes	115,050	Yes	$16,344	YES	NO	NO
Honda	Accord	2013	Silver	No	No	77,072	No	$16,355	NO	NO	MAYBE
Chevrolet	Malibu	2011	Blue	No	Yes	82,792	No	$16,556	NO	MAYBE	NO
Toyota	Camry	2010	Red	Yes	Yes	88,163	No	$17,248	NO	YES	NO
Chevrolet	Silverado	2009	White	No	No	100,179	Yes	$17,964	NO	MAYBE	NO
Toyota	Corolla	2013	Blue	Yes	Yes	117,039	Yes	$17,965	YES	NO	MAYBE
Honda	Civic	2012	Red	Yes	No	73,533	No	$19,722	NO	YES	NO
Honda	Civic	2011	White	Yes	No	88,786	No	$19,864	NO	NO	NO
Chevrolet	Impala	2011	Silver	Yes	Yes	77,060	No	$20,339	NO	MAYBE	NO
Ford	F-150	2014	Red	Yes	No	105,489	Yes	$20,380	NO	MAYBE	NO
Ford	Fusion	2013	Silver	No	No	109,223	Yes	$20,532	NO	MAYBE	NO
Ford	F-150	2012	Red	No	No	76,025	No	$20,659	NO	YES	NO
Honda	Accord	2010	Blue	Yes	No	76,701	No	$21,138	NO	NO	MAYBE
Chevrolet	Silverado	2014	Silver	Yes	No	72,319	No	$21,148	NO	MAYBE	NO
Chevrolet	Malibu	2013	White	No	No	117,518	Yes	$21,183	NO	MAYBE	NO
Chevrolet	Silverado	2009	Black	No	Yes	101,839	Yes	$21,226	NO	MAYBE	NO
Chevrolet	Malibu	2014	Blue	Yes	No	80,179	No	$21,466	NO	MAYBE	NO
Toyota	Camry	2010	Blue	No	Yes	74,937	No	$21,976	NO	NO	NO
Ford	F-150	2011	Black	Yes	Yes	117,249	Yes	$22,883	NO	MAYBE	NO
Ford	Focus	2014	Silver	Yes	No	77,527	No	$23,235	NO	MAYBE	NO
Ford	Fusion	2011	White	Yes	Yes	81,907	No	$23,835	NO	MAYBE	NO

Thanks & Regards,

Devendra agarwal