Excel Assignment Customer Unedited Location: Blackboard Course Materials>Excel Filles File Unedited file contains a list of clients in various regions with financial characteristics Perform the following tasks: 1 Copy file from Blackboard to your desktop or flash drive 2. Replace the entries in that column according to the following rules Replace 1Ok-25k with 10 Replace 25k-50k with 25 Replace 50k-100k with 50 Replace 100k+ with 100 3. Save your workbook 4Convert the data to a table (choose any format) (a) Reconvert the table to a range and remove any formatting from (4). Hint: Font color and FlI in toble Heoder (b) Create a new header called "Purchases Type" in cell H2 (c) Create a new header called "Household Type" in cell 12 [d) Create a new header called -East Small" in cell J2 (e) Create a new header called Region Abbreviated" in cell K2 (1) Create a new header called "Region Ownership" in cell L2 6. Create formulas to do the following: (a) Sum the Total Purchases" column. Place it in cell F495 with a label in E495 (Note:a label is just text that describes the data). (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 Purchases is at least 10, create the test "Big Spender" in the corresponding cell in the "Purchases Type" column. (f) 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 2, create the text "Small" in the corresponding cell in the "Household Type" column. (g) Determine (Count) the quantity of large Household Type clients in the East region. Place it in cell F499 with a label in E499 (h) List the "Cust ld" of all the East "Region" and Small Household Type" clients in the (i) Remove the first letter of each cell in the "Region" column and place them in the (i) "East Small column (U), otherwise leave the cell blank. corresponding cells in the "Region Abbreviated" column (K) Combine the "Region" cell and the "Rent vs Own" cell for all records with two spaces between the cells and place them in corresponding cell the "Region Ownership" column (L) 8. Save your workbook 9. Perform Conditional Formatting to achieve the following: (a) Highlight the Rent cells with "Light Red Fill with Dark Red Text" (b) Fill the cells of the "Num Household" column with a green background color for entries containing 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 Yellow with Dark Yellow Text Excel Assignment Customer Unedited Location: Blackboard Course Materials>Excel Filles File Unedited file contains a list of clients in various regions with financial characteristics Perform the following tasks: 1 Copy file from Blackboard to your desktop or flash drive 2. Replace the entries in that column according to the following rules Replace 1Ok-25k with 10 Replace 25k-50k with 25 Replace 50k-100k with 50 Replace 100k+ with 100 3. Save your workbook 4Convert the data to a table (choose any format) (a) Reconvert the table to a range and remove any formatting from (4). Hint: Font color and FlI in toble Heoder (b) Create a new header called "Purchases Type" in cell H2 (c) Create a new header called "Household Type" in cell 12 [d) Create a new header called -East Small" in cell J2 (e) Create a new header called Region Abbreviated" in cell K2 (1) Create a new header called "Region Ownership" in cell L2 6. Create formulas to do the following: (a) Sum the Total Purchases" column. Place it in cell F495 with a label in E495 (Note:a label is just text that describes the data). (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 Purchases is at least 10, create the test "Big Spender" in the corresponding cell in the "Purchases Type" column. (f) 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 2, create the text "Small" in the corresponding cell in the "Household Type" column. (g) Determine (Count) the quantity of large Household Type clients in the East region. Place it in cell F499 with a label in E499 (h) List the "Cust ld" of all the East "Region" and Small Household Type" clients in the (i) Remove the first letter of each cell in the "Region" column and place them in the (i) "East Small column (U), otherwise leave the cell blank. corresponding cells in the "Region Abbreviated" column (K) Combine the "Region" cell and the "Rent vs Own" cell for all records with two spaces between the cells and place them in corresponding cell the "Region Ownership" column (L) 8. Save your workbook 9. Perform Conditional Formatting to achieve the following: (a) Highlight the Rent cells with "Light Red Fill with Dark Red Text" (b) Fill the cells of the "Num Household" column with a green background color for entries containing 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 Yellow with Dark Yellow Text