Need help with (3-9). Having trouble with the formulas.

Paste 5 B E2 fx A B C D Condition Identifier C-13 C-78 D-66 B-82 B-92 B-47 B-48 D-35 B-52 C-88 2 B-38 3 C-31 4 C-79 5 D-44 6 B-43 7 B-53 8 B-41 9 C-42 0 B-75 1 C-36 22 D-74 23 B-86 24 B-17 25 B-14 26 B-39 27 C-51 28 B-27 29 D-12 30 C-77 31 C-73 32 D-34 33 D-18 34 C-87 35 B-91 36 B-37 37 C-24 38 B-24 39 C-68 40 41 Year of manufacture: 42 Maximum cylinder capacity: Cylinder capacity 1 249 cm3 1 195 cm3 1 599 cm3 2 995 cm3 1 298 cm3 1 195 cm3 2 995 cm3 2 995 cm3 2490 cm3 980 cm3 1 298 cm3 1 599 cm3 1 995 cm3 1 795 cm3 2 995 cm3 1 298 cm3 1 249 cm3 1 599 cm3 2 995 cm3 980 cm3 2490 cm3 1 249 cm3 1 195 cm3 2 995 cm3 1 795 cm3 1 995 cm3 980 cm3 1 795 cm3 2 490 cm 3 2 995 cm3 1 795 cm3 1 795 cm3 1 195 cm 3 2 490 cm3 1 599 cm3 1 195 cm3 1 249 cm3 2 995 cm3 Year of manufacture 2011 2011 2008 2012 2008 2009 2011 2006 2011 2006 2012 2009 2009 2010 2009 2006 2012 2008 2006 2011 2008 2011 2011 2010 2010 2009 2012 2011 2012 2010 2006 2012 2006 2010 2010 2006 2011 2010 Mileage 45 234 km 34 308 km 116 953 km 8 515 km 238 450 km 192 795 km 56 145 km 268 943 km 52 712 km 278 123 km 9 936 km 163 553 km 170 791 km 57 623 km 134 985 km 218 072 km 9 998 km 175 273 km 199 817 km 50 047 km 209 712 km 55 163 km 46 645 km 83 178 km 131 748 km 189 761 km 18 309 km 47 711 km 8 683 km 87 971 km 163 940 km 14 717 km 359 114 km 77 590 km 130 724 km 139 813 km 73 042 km 79 236 km 2010 2000 F G 1 J K L M N on A shipping company keeps record of the vehicles used. They record the identifier of the vehicle (column A), the cylinder capacity (column B), the year of manufacture (column C) and the mileage (total distance traveled) of the vehicle (column D). 3. A vehicle is considered to be in novel state if it has run at most 15000 km. A vehicle should be replaced if it has run at least 250000 km. In column Condition determine which category ("novel" or "to be replaced") the vehicles belong into using a function that can be copied flawlessly. If the vehicle does not belong into either of the given categories, then nothing should be displayed in the cell by the formula. 4. Determine the average mileage in cell B43 for vehicles that fulfil the two conditions in range A41:B42! If cell B41 or B42 is edited, the answer should be updated automatically! If there is no vehicle that meets the two conditions, display "NONE" instead! 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"! 5 6. Set italic font style and lightgreen background color for whole rows in which the mileage is below the 75% of the averagel Format by using your own formula! 7. In cell A48 show the identifier of the vehicle whose mileage is the highest! However, if there are more vehicles with the highest mileage, display "MORE"! 8. In cell A50 determine the minimum year of manufacture of the vehicles that meet ANY of the following conditions: (1) the cylinder capacity is between 1500 and 2500 (including the limits), (2) the mileage is NOT higher than the 120% of the average. If there is no proper vehicle, show "NONE" instead! 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 higher than the average cylinder capacity of the vehicles manufactured in 2010. CS v % 9 40 .00 O 20 Conditio Formatti N o P Q R Cylinder capacity lower limits Consumption 0 cm3 7,5 l/100 km 1 001 cm3 8,21 / 100 km 1 501 cm3 9,11 / 100 km 2001 cm3 11,8 l/100 km Paste 5 B E2 fx A B C D Condition Identifier C-13 C-78 D-66 B-82 B-92 B-47 B-48 D-35 B-52 C-88 2 B-38 3 C-31 4 C-79 5 D-44 6 B-43 7 B-53 8 B-41 9 C-42 0 B-75 1 C-36 22 D-74 23 B-86 24 B-17 25 B-14 26 B-39 27 C-51 28 B-27 29 D-12 30 C-77 31 C-73 32 D-34 33 D-18 34 C-87 35 B-91 36 B-37 37 C-24 38 B-24 39 C-68 40 41 Year of manufacture: 42 Maximum cylinder capacity: Cylinder capacity 1 249 cm3 1 195 cm3 1 599 cm3 2 995 cm3 1 298 cm3 1 195 cm3 2 995 cm3 2 995 cm3 2490 cm3 980 cm3 1 298 cm3 1 599 cm3 1 995 cm3 1 795 cm3 2 995 cm3 1 298 cm3 1 249 cm3 1 599 cm3 2 995 cm3 980 cm3 2490 cm3 1 249 cm3 1 195 cm3 2 995 cm3 1 795 cm3 1 995 cm3 980 cm3 1 795 cm3 2 490 cm 3 2 995 cm3 1 795 cm3 1 795 cm3 1 195 cm 3 2 490 cm3 1 599 cm3 1 195 cm3 1 249 cm3 2 995 cm3 Year of manufacture 2011 2011 2008 2012 2008 2009 2011 2006 2011 2006 2012 2009 2009 2010 2009 2006 2012 2008 2006 2011 2008 2011 2011 2010 2010 2009 2012 2011 2012 2010 2006 2012 2006 2010 2010 2006 2011 2010 Mileage 45 234 km 34 308 km 116 953 km 8 515 km 238 450 km 192 795 km 56 145 km 268 943 km 52 712 km 278 123 km 9 936 km 163 553 km 170 791 km 57 623 km 134 985 km 218 072 km 9 998 km 175 273 km 199 817 km 50 047 km 209 712 km 55 163 km 46 645 km 83 178 km 131 748 km 189 761 km 18 309 km 47 711 km 8 683 km 87 971 km 163 940 km 14 717 km 359 114 km 77 590 km 130 724 km 139 813 km 73 042 km 79 236 km 2010 2000 F G 1 J K L M N on A shipping company keeps record of the vehicles used. They record the identifier of the vehicle (column A), the cylinder capacity (column B), the year of manufacture (column C) and the mileage (total distance traveled) of the vehicle (column D). 3. A vehicle is considered to be in novel state if it has run at most 15000 km. A vehicle should be replaced if it has run at least 250000 km. In column Condition determine which category ("novel" or "to be replaced") the vehicles belong into using a function that can be copied flawlessly. If the vehicle does not belong into either of the given categories, then nothing should be displayed in the cell by the formula. 4. Determine the average mileage in cell B43 for vehicles that fulfil the two conditions in range A41:B42! If cell B41 or B42 is edited, the answer should be updated automatically! If there is no vehicle that meets the two conditions, display "NONE" instead! 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"! 5 6. Set italic font style and lightgreen background color for whole rows in which the mileage is below the 75% of the averagel Format by using your own formula! 7. In cell A48 show the identifier of the vehicle whose mileage is the highest! However, if there are more vehicles with the highest mileage, display "MORE"! 8. In cell A50 determine the minimum year of manufacture of the vehicles that meet ANY of the following conditions: (1) the cylinder capacity is between 1500 and 2500 (including the limits), (2) the mileage is NOT higher than the 120% of the average. If there is no proper vehicle, show "NONE" instead! 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 higher than the average cylinder capacity of the vehicles manufactured in 2010. CS v % 9 40 .00 O 20 Conditio Formatti N o P Q R Cylinder capacity lower limits Consumption 0 cm3 7,5 l/100 km 1 001 cm3 8,21 / 100 km 1 501 cm3 9,11 / 100 km 2001 cm3 11,8 l/100 km