how do I get the Rank - Average Median Wage column a single number (like the 2nd picture). and then how to make the total section at the bottom only showing certain totals. also how to do the steps in question 2

Write a paragraph above the table to analyze any patterns that you see in the PivotTable. Cite numbers from the Pivot Table in your analysis. Name this worksheet Pivottable. See the following screenshot. Pay attention to the details below: Data are presented by education and experience. Median wage and employment diff are displayed to have two decimal places. Education is sorted in this order: no formal educational credential, high school diploma, postsecondary nondegree, and bachelor's (See the pointer below the Pivot Table screenshot for more.) iv. An Icon set is displayed for Average of Median Wage by showing a green light for any number in 70% percentile or above. A red light is used for the lowest 33% percentile. Note it is percentile, not percentage. iv. Data bars are displayed for average employment difference using the color shown. The larger the number is, the darker color shade it will be. Column Labels Less than 5 years None 5 years or more Grand Total $23,100.00 $23,101.11 $23,101.00 159.00 230.86 223.67 $41,485.00 $37,982.86 $38,761.11 104.85 116.76 114.11 Row Labels No formal educational credential Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff High school diploma or equivalent Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Postsecondary nondegree award Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Bachelor's degree Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Total Average of Median Wage Total Rank - Average of Median Wage Total Average of Employment Diff Total Sum of Employment Diff $34,932.50 $34,932.50 154.23 154.23 $81,320.00 $76,178.00 $97,730.00 $79,991.43 103.40 185.56 151.10 168.90 $46,847.50 $39,776.40 $97,730.00 $42,651.00 118.03 177.59 151.10 168.76 b. Q2: Copy the data from Cleansed Data worksheet and paste it to a new worksheet. Paste only values, not formulas. Name this new worksheet Excel Table. Do the following: i. Filter out any occupations that end with the word aides, and occupations that contains the word labor anywhere in the name. This requires a custom filter. II. Starting from row 34 answer the questions shown in the screenshot below. Produce your answers in column E for those rows using Excel functions. Use COUNTA, COUNTIF, AVERAGEIF and AVERAGEIFS in your answers. 34 (1) Total number of occupations: 35 (2) Total occupations that do not require prior experience 36 (3) Average salary of occupations that do not require prior experience 37 (4) Average salary of occupations for bachelor's degree with no prior experience. C. Re-order your tabs this way: Most Job Growth, Worker Characteristics, Cleansed Data, Pivot Table, and Excel Table. Submission You will submit only one Excel file called Assignment3_XXX.xlsx, where XXX is your last name. Experience Education Level TY 5 years or more None Less than 5 years Grand Total No formal educational credential Average of Median Wage $26,882.86 $26,530.00 $26,838.75 Rank - Average of Median Wage 26883 2 6530! 26839 Average of Employment Diff. 205.87 299.00 217.51 Sum of Employment Diff 1441.10 299.00 1740.10 High school diploma or equivalent Average of Median Wage $39,801.43 $32,450.00 $38,882.50 Rank - Average of Median Wage 39801 32450 38883 Average of Employment Diff. 228.30 107.20 213.16 Sum of Employment Diff 1598.10 107.201705.30 Postsecondary nondegree award Average of Median Wage $38,017.50 $38,017.50 Rank - Average of Median Wage 38018 38018 Average of Employment Diff. 117.03 117.03 Sum of Employment Diff 468.10 468.10 Bachelor's degree Average of Median Wage $114,460.00 $75,900.00 $91,670.00 $87,973.33 Rank - Average of Median Wage 114460 75900 91670 87973 Average of Employment Diff. 134.85 182.84 94.90 152.63 Sum of Employment Diff 269.70 914.20 189.80 1373.70 5 Total Average of Median Wage $114,460.00 $43,406.96 $60,580.00 $50,675.86 6 Total Rank - Average of Median Wage 114460 43407 60580 50676 Total Average of Employment Diff. 134.85 192.24 149.00 182.32 Total Sum of Employment Diff 269.70 4421.50 596.00 5287.20 Write a paragraph above the table to analyze any patterns that you see in the PivotTable. Cite numbers from the Pivot Table in your analysis. Name this worksheet Pivottable. See the following screenshot. Pay attention to the details below: Data are presented by education and experience. Median wage and employment diff are displayed to have two decimal places. Education is sorted in this order: no formal educational credential, high school diploma, postsecondary nondegree, and bachelor's (See the pointer below the Pivot Table screenshot for more.) iv. An Icon set is displayed for Average of Median Wage by showing a green light for any number in 70% percentile or above. A red light is used for the lowest 33% percentile. Note it is percentile, not percentage. iv. Data bars are displayed for average employment difference using the color shown. The larger the number is, the darker color shade it will be. Column Labels Less than 5 years None 5 years or more Grand Total $23,100.00 $23,101.11 $23,101.00 159.00 230.86 223.67 $41,485.00 $37,982.86 $38,761.11 104.85 116.76 114.11 Row Labels No formal educational credential Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff High school diploma or equivalent Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Postsecondary nondegree award Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Bachelor's degree Average of Median Wage Rank - Average of Median Wage Average of Employment Diff Sum of Employment Diff Total Average of Median Wage Total Rank - Average of Median Wage Total Average of Employment Diff Total Sum of Employment Diff $34,932.50 $34,932.50 154.23 154.23 $81,320.00 $76,178.00 $97,730.00 $79,991.43 103.40 185.56 151.10 168.90 $46,847.50 $39,776.40 $97,730.00 $42,651.00 118.03 177.59 151.10 168.76 b. Q2: Copy the data from Cleansed Data worksheet and paste it to a new worksheet. Paste only values, not formulas. Name this new worksheet Excel Table. Do the following: i. Filter out any occupations that end with the word aides, and occupations that contains the word labor anywhere in the name. This requires a custom filter. II. Starting from row 34 answer the questions shown in the screenshot below. Produce your answers in column E for those rows using Excel functions. Use COUNTA, COUNTIF, AVERAGEIF and AVERAGEIFS in your answers. 34 (1) Total number of occupations: 35 (2) Total occupations that do not require prior experience 36 (3) Average salary of occupations that do not require prior experience 37 (4) Average salary of occupations for bachelor's degree with no prior experience. C. Re-order your tabs this way: Most Job Growth, Worker Characteristics, Cleansed Data, Pivot Table, and Excel Table. Submission You will submit only one Excel file called Assignment3_XXX.xlsx, where XXX is your last name. Experience Education Level TY 5 years or more None Less than 5 years Grand Total No formal educational credential Average of Median Wage $26,882.86 $26,530.00 $26,838.75 Rank - Average of Median Wage 26883 2 6530! 26839 Average of Employment Diff. 205.87 299.00 217.51 Sum of Employment Diff 1441.10 299.00 1740.10 High school diploma or equivalent Average of Median Wage $39,801.43 $32,450.00 $38,882.50 Rank - Average of Median Wage 39801 32450 38883 Average of Employment Diff. 228.30 107.20 213.16 Sum of Employment Diff 1598.10 107.201705.30 Postsecondary nondegree award Average of Median Wage $38,017.50 $38,017.50 Rank - Average of Median Wage 38018 38018 Average of Employment Diff. 117.03 117.03 Sum of Employment Diff 468.10 468.10 Bachelor's degree Average of Median Wage $114,460.00 $75,900.00 $91,670.00 $87,973.33 Rank - Average of Median Wage 114460 75900 91670 87973 Average of Employment Diff. 134.85 182.84 94.90 152.63 Sum of Employment Diff 269.70 914.20 189.80 1373.70 5 Total Average of Median Wage $114,460.00 $43,406.96 $60,580.00 $50,675.86 6 Total Rank - Average of Median Wage 114460 43407 60580 50676 Total Average of Employment Diff. 134.85 192.24 149.00 182.32 Total Sum of Employment Diff 269.70 4421.50 596.00 5287.20