Excel Assignment File Location: Blackboard: Course Materials The Customer Uned ted file comains a list of clients in various Perform the following tanks 1Copy file from Blackboard to Excel Fides regions with financial your desktop or Sort the data by the Annual Income coumn IA to 2) Replace 10k-25k with 10 Replace 25k-50k with 25 Replace 100ke with 100 3 Replace the entries in that column according to the following rules Sok-100k 4 Save your changes S Convert the data to a table (choose any format) 6 a. Reconvert the table to a range and remove any formatting from (4 Hint: Font coor and Fill in b. Create a new header called "Household Type" in column H next to the "Num Purchases 7 Create formulas to do the following a. Sum the Total Purchases" column. Place it in cell F495 with a label in 45 b. Average the Total Purchases" column, Place it in cell F496 with a label in E496 c. Count the number of Renters. Place it in cell F497 with a label in E497 d. Find the median for the "Num Purchases" column. Place it in cell F498 with a label in E498. e. If the Num Household is at least 5, create the text "Large" in the corresponding cell (row) in the "household Type" column. If the Num Household is at least 2create the text yur in the corresponding cell in the "Household Type" column f. Determine the quantity of large Household Type clients in the East region. Place it in cell F499 with a label in E499 g Show all East region Household Type column. h. Remove the first letter of each cell in the Region column and place them in a new colum named "Region Abbreviated i. Combime the Region cell and the Rent vs 0wn column cells with two spaces between and and small Household Type dents i. new coun net to the place them in a column named "Region Ownership 8 Save your Worksheet 9 Perform Conditional Formatting to achieve the following b. Fill the cells of the "Num Household column with a green background color for entries contacting at least 6. c. Gradient fill (blue) the "Num Purchases column. d. Format the cells in the top 10% in the-Total Purchases" column with"Yelow Fil with Dark Yellow Text a. Highlight the Rent cells with "Light Red Fill with Dark Red Text Excel Assignment File Location: Blackboard: Course Materials The Customer Uned ted file comains a list of clients in various Perform the following tanks 1Copy file from Blackboard to Excel Fides regions with financial your desktop or Sort the data by the Annual Income coumn IA to 2) Replace 10k-25k with 10 Replace 25k-50k with 25 Replace 100ke with 100 3 Replace the entries in that column according to the following rules Sok-100k 4 Save your changes S Convert the data to a table (choose any format) 6 a. Reconvert the table to a range and remove any formatting from (4 Hint: Font coor and Fill in b. Create a new header called "Household Type" in column H next to the "Num Purchases 7 Create formulas to do the following a. Sum the Total Purchases" column. Place it in cell F495 with a label in 45 b. Average the Total Purchases" column, Place it in cell F496 with a label in E496 c. Count the number of Renters. Place it in cell F497 with a label in E497 d. Find the median for the "Num Purchases" column. Place it in cell F498 with a label in E498. e. If the Num Household is at least 5, create the text "Large" in the corresponding cell (row) in the "household Type" column. If the Num Household is at least 2create the text yur in the corresponding cell in the "Household Type" column f. Determine the quantity of large Household Type clients in the East region. Place it in cell F499 with a label in E499 g Show all East region Household Type column. h. Remove the first letter of each cell in the Region column and place them in a new colum named "Region Abbreviated i. Combime the Region cell and the Rent vs 0wn column cells with two spaces between and and small Household Type dents i. new coun net to the place them in a column named "Region Ownership 8 Save your Worksheet 9 Perform Conditional Formatting to achieve the following b. Fill the cells of the "Num Household column with a green background color for entries contacting at least 6. c. Gradient fill (blue) the "Num Purchases column. d. Format the cells in the top 10% in the-Total Purchases" column with"Yelow Fil with Dark Yellow Text a. Highlight the Rent cells with "Light Red Fill with Dark Red Text
