Download the starting file, and complete the following commands using the data provided. (a) Indicate the following using conditional formatting commands. Fach of the following conditions should appear in a unique format Length shown in column Eis greater than 6 inches or less than 4 inches, should be on a red background. Width shown in column F is less than 2.5 inches, should be on an orange background. Inner radius shown in column Gis above average for the inner radius values, should be on a yellow background. Outer radius shown in column His below average for the outer radius values, should be on a green background. Volume shown in column I is less than 10 cubic inches or greater than 20 cubic inches, should be on a blue background. (b) For the following conditions, in column Kuse an IF statement to indicate the status: If length is less than 4 inches or width is less than 2.5 inches, list the status as "Too Small." Otherwise, if twice the inner radius is greater than the outer radius, list the status as "Off Center." . Otherwise, if the volume is greater than 20 cubic inches or the mass is greater than 3000 grams, list the status as "Too Large." Otherwise, if none of these conditions are true, leave the cell blank. (c) For the following conditions, in column L use an IF statement to indicate the action code: . If the status is "Too Small" or "Too Large," list the action code as a numerical value of 1. . If the status is "Off Center," list the action code as a numerical value of 2. If none of these conditions are met, list the action code as a numerical value of 3. (d) Use a conditional formatting icon set in column L to indicate the following: Status as green for action code 3. (c) Count the following items, showing the results above the data table. Be sure to indicate each counted item with an appropriate label. Indicate the number of items classified as each action code, such as how many items are listed as 1. Indicate number of parts when the length is greater than 6 inches. Indicate number of parts when the volume is less than 10 cubic inches or greater than 20 cubic inches. As a hint, use two COUNTIF functions and add them together. (1) Sort the worksheet in the following order: Length, increasing and simultaneously then Outer Radius, decreasing. Be careful to select only the data and not the entire worksheet. (g) Set the worksheet controls to be filtered in the header row. Filter the worksheet so only parts of length 2.80, 5.20, and 7.15 inches are shown Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the cells specified in Instructions. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas. Action Code Count Length >6 Volume6 Volume
