How to write the formulas of these exercises in ms excel?? C-13 C-78 D-66 B-82 B-92 B-47

How to write the formulas of these exercises in ms excel??

Transcribed Image Text:

C-13 C-78 D-66 B-82 B-92 B-47 B-48 D-35 B-52 C-88 B-38 C-31 C-79 D-44 B-43 B-53 B-41 C-42 B-75 C-36 D-74 B-86 B-17 B-14 B-39 C-51 B-27 D-12 C-77 C-73 D-34 D-18 C-87 B-91 B-37 C-24 Identifier Cylinder capacity 1249 cm3 1195 cm3 1599 cm3 2 995 cm3 1298 cm3 1 195 cm3 2995 cm3 2 995 cm3 2 490 cm3 980 cm3 1 298 cm3 1599 cm3 1995 cm3 1795 cm3 2 995 cm3 1298 cm3 1249 cm3 1599 cm3 2995 cm3 980 cm3 2 490 cm3 1249 cm3 1195 cm3 2 995 cm3 1 795 cm3 1995 cm3 980 cm3 1795 cm3 2 490 cm3 2995 cm3 1795 cm3 1795 cm3 1195 cm3 2 490 cm3 1599 cm3 1195 cm3 Year of manufacture Mileage 45 234 km 34 308 km 116 953 km Condition 2011 2011 2008 2012 8515 km novel 2008 238 450 km to be replaced 2009 192 795 km 2011 56 145 km 2006 268 943 km 2011 52 722 km 2006 278 123 km to be replaced 9 936 km novel 2012 2009 163 553 km 2009 170 791 km 2010 57 623 km to be replaced 2009 134 985 km 2006 218 072 km 2012 9998 km novel 2008 175 273 km 2006 199 817 km 50 047 km 2011 2008 209 712 km - 2011 55 163 km 46 645 km 2011 2010 83 178 km 2010 131 748 km 2009 189 761 km 2012 18 309 km 2011 47 711 km 2012 8 683 km novel 87971 km 2010 2006 163 940 km 2012 14 717 km 2006 359 114 km to be replaced 2010 77 590 km 2010 130 724 km 2006 139 813 km 5. In cell B46 display the consumption of the vehicle whose identifier is in cell A46! The connection between the cylinder capacity and the consumption can be found in auxiliary table 01:P5 which contains lower limits. However, if the given identifier cannot be found in the main table, show message "WRONG IDENTIFIER"! (3 points) 6. Set italic font style and lightgreen background color for whole rows in which the cylinder capacity is below 2000 cm3 and the year of manufacture is greater than 2009! Format by using your own formula! (3 points) 7. In cell A48 show the identifier of the oldest vehicle! However, if there are more vehicles with the lowest year of manufacture, display "MORE"! (4 points) 8. In cell A50 determine the maximum year of manufacture of the vehicles that meet ANY of the following conditions: (1) the cylinder capacity is between 1000 and 2000 (including the limits), (2) the mileage is higher than the average. If there is no proper vehicle, show "NONE" instead! (5 points) 9. In cell A57 calculate the average mileage of vehicles that meet BOTH of the following conditions: (1) the year of manufacture was before 2008 or after 2010 (excluding these two dates) (2) the cylinder capacity is NOT higher than the average cylinder capacity of the vehicles manufactured in 2011. (5 points) C-13 C-78 D-66 B-82 B-92 B-47 B-48 D-35 B-52 C-88 B-38 C-31 C-79 D-44 B-43 B-53 B-41 C-42 B-75 C-36 D-74 B-86 B-17 B-14 B-39 C-51 B-27 D-12 C-77 C-73 D-34 D-18 C-87 B-91 B-37 C-24 Identifier Cylinder capacity 1249 cm3 1195 cm3 1599 cm3 2 995 cm3 1298 cm3 1 195 cm3 2995 cm3 2 995 cm3 2 490 cm3 980 cm3 1 298 cm3 1599 cm3 1995 cm3 1795 cm3 2 995 cm3 1298 cm3 1249 cm3 1599 cm3 2995 cm3 980 cm3 2 490 cm3 1249 cm3 1195 cm3 2 995 cm3 1 795 cm3 1995 cm3 980 cm3 1795 cm3 2 490 cm3 2995 cm3 1795 cm3 1795 cm3 1195 cm3 2 490 cm3 1599 cm3 1195 cm3 Year of manufacture Mileage 45 234 km 34 308 km 116 953 km Condition 2011 2011 2008 2012 8515 km novel 2008 238 450 km to be replaced 2009 192 795 km 2011 56 145 km 2006 268 943 km 2011 52 722 km 2006 278 123 km to be replaced 9 936 km novel 2012 2009 163 553 km 2009 170 791 km 2010 57 623 km to be replaced 2009 134 985 km 2006 218 072 km 2012 9998 km novel 2008 175 273 km 2006 199 817 km 50 047 km 2011 2008 209 712 km - 2011 55 163 km 46 645 km 2011 2010 83 178 km 2010 131 748 km 2009 189 761 km 2012 18 309 km 2011 47 711 km 2012 8 683 km novel 87971 km 2010 2006 163 940 km 2012 14 717 km 2006 359 114 km to be replaced 2010 77 590 km 2010 130 724 km 2006 139 813 km 5. In cell B46 display the consumption of the vehicle whose identifier is in cell A46! The connection between the cylinder capacity and the consumption can be found in auxiliary table 01:P5 which contains lower limits. However, if the given identifier cannot be found in the main table, show message "WRONG IDENTIFIER"! (3 points) 6. Set italic font style and lightgreen background color for whole rows in which the cylinder capacity is below 2000 cm3 and the year of manufacture is greater than 2009! Format by using your own formula! (3 points) 7. In cell A48 show the identifier of the oldest vehicle! However, if there are more vehicles with the lowest year of manufacture, display "MORE"! (4 points) 8. In cell A50 determine the maximum year of manufacture of the vehicles that meet ANY of the following conditions: (1) the cylinder capacity is between 1000 and 2000 (including the limits), (2) the mileage is higher than the average. If there is no proper vehicle, show "NONE" instead! (5 points) 9. In cell A57 calculate the average mileage of vehicles that meet BOTH of the following conditions: (1) the year of manufacture was before 2008 or after 2010 (excluding these two dates) (2) the cylinder capacity is NOT higher than the average cylinder capacity of the vehicles manufactured in 2011. (5 points)