1. Apply conditional formatting to the Savings Total column to visually demonstrate the range of profits/contract (choose any bars or scales you want)
2. Create a new column of data under Checking Account to indicate customers whose checking account has less than $8,000
3. Create a new column of data under Phoenix and Rent to indicate customers whose local branch is Phoenix and rent their homes
4. Create a new column of data under Checking or Saving to indicate customers with either less than $10,000 in their checking or less than $5,000 in their savings
5. Build one PivotTable which calculates the average of checking total and minimum of savings total by branch and customer gender. You will have to combine the 2 data sets using XLOOKUP, VLOOKUP or MATCH/INDEX. Note any observations you see regarding trends in the results. These observations can be typed as a sentence directly into the Excel workbook.

IMPORTANT: The values in columns for question 2, 3, and 4 must be 0 or 1, NOT "true" or "false". An example of such column (indicating if the customers savings account is less than $1,000) is shown.

XI. 5 * HW 1 File - Excel ANALYTIC SOLVER FILE HOME INSERT PAGE LAYOUT FORMULAS DATA REVIEW VIEW ADD-INS DATA MINING Paste ? - 5x A AutoSum YA Fill - Sort & Find & Clear Filter - Select Editing 29 - Wrap Text General Merge & Center - $ - % Alignment Number & Cut Calibri - 11 - A Copy - BIU- A Format Painter Clipboard Font X fx ? 2 08 38 Insert Delete Format Conditional Format as Cell Formatting Table Styles Styles Cells K2 Formula Bar G H B C D E F 1 Loyalty ID # Local Branch Checking Total Savin's Total Membership full years Housing Type 21 1173 Phoenix $ 14,532.02 $ 3,720.26 5 Rent 3 1100 Mosa S 5,872.79 $ 4,441.84 2 Other 4 1189 Flagstaff $ 10,745.51 $ 35,711.40 6 Other 5 1161 Flagstall $ 1,374.56 $3,227.38 4 Other 6 1255 Flagstatt $ 339.99 S $ 61.03 21 Own (Mortgage 7 1146 Tucson $ 1,279.72 S 16,734,61 3 Own (No Morte) 8 1101 Tucson $ 4,293.68 $ 3,653.68 2 Other 9 1000 Flagstaff $ 5,366.53 $ 29,107.18 0 Other 1043 Tucson S 1,912.55 $ 2,148.95 1 Own (Mortgage) 1190 Phoenix $ 4,332.41 S 1,561.38 6 Rent 12 1171 Flagstaff $ 18,633.925 321.97 5 Other 13 1162 Mesa $ 15,764.68 $ 6,518.29 S 4 Rent 14 1044 Mesa S 10,963.35 S 552.45 1 Own (Mortgage) 15 1147 Mesa $ 6,051.82 5 4.810.80 3 Own (No Mortape) 16 1001 Mesa $ 8,197.35 $ 6,326.65 0 Own (No Mortgage 17 1243 Tucson $ 13,938.41 $ 2,497.00 14 Other 19 1102 Phoenix S 2,214.99 $ 3,083.23 2 Rent 19 1103 Tempe $ 1,210.75 $ 25,779.19 2 Rent 20 1104 Flagstall $ 8,756.57 $ 290.42 2 Own (Nu Mortgage 21 1247 Phoenix $ 4,031.87 S 1,789.22 15 Own (Mortgage 22 1233 Tempe $ 321.44 $ 1,990.10 9 Own (Mortgage) 23 1015 Mesa $ 6,248.68 5 241.92 1 Other 24 1046 Phoenix $ 5,728.17 $ 18,797.67 1 Rent 25 1236 Tempe S 6,002.61 $ 7,359.45 10 Own (No Mortgage) 26 104/ Tucson $ 1,320.13 $ 8,749.41 1 Rent 27 1002 Tempe $ 3,289.16 5 7,830.88 0 Rent 20 1048 Mesa $ 79.82 S $ 2,941.90 1 Own (Mortgage) 29 1049 Flagstaff $ 831.87 S 2,358.99 1 Other Sheet1 HW1_a HW1_b + Cust. savings less than $1,000 (1 = Yes, 0 = No) Checking Account Phoenix and Rent Checking or Saving Gnd VLOOKUP 0 O RENT You better watch out LESS than 5000 2 0 1 other IYou better watch out LESS than 5000? 0 O other Keep making Money! ? ? 0 1 other Account notification Less than 10000 ? 1 1 other IYou better watch out LESS than 500012 0 1 other Keep making Money! ? 2 0 1 other !You better watch out LESS than 5000!? 1 other Keep making Money! ? 0 1 other Iyou better watch out LESS than 50001? 0 1 RENI Iyou better watch out LESS than 50001? 1 0 other !You better watch out LESS than 5000.? O other Account notification Less than 10000 ? 1 0 other Iyou better watch out LESS than 5000!? 0 1 other !You better watch out LESS than 5000! ? o O other Account notification Less than 10000 ? 0 O other lYou better watch out LESS than 50001? 0 1 RENT You better watch out LESS than 5000? 0 1 other Keep making Money! ? 1 O other !You better Watch Out LESS than 5000? 0 1 RENT IYou better watch out LESS than 500017 0 1 other Iyou better watch out LESS than 50001? 1 1 other !You better watch out LESS than 5000!? 0 1 RENT Keep making Money! ? 1 other Account notification less than 10000 ? 0 1 other Account notification Less than 10000 ? 0 1 other Account notification Less than 10000 ? 0 1 other !You better watch out LESS than 50001? 0 1 other You better watch out LESS than 5000? READY FO + 20% 2:44 PM H Type here to search C 73F ENG 1/30/2022 * HW 1 File - Excel ANALYTIC SOLVER FORMULAS DATA REVIEW VIEW ADD-INS DATA MINING - - A A == XI. 5 FILE HOME INSERT PAGE LAYOUT X Cut Calibri - 11 Copy - Paste B IU-- Format Painter Clipboard Font D2 - 1 X fx AY ? - x A AutoSum- Fill Sort & Find & Clear Filter - Select Editing 9 1. Wrap Text General = Merge & Center - $ - % F Insert Delete Format 008 Conditional Format as Cell Formatting Table Styles Styles Alignment Number Cells y E F H 1 K L M M N P R S s U v w X A B c D 1 Loyalty ID# Gender Are 21 1000 Male 55. 3 1001 Female 34 4 1002 Female 59. 5 1003 Male 30, 6 1004 Female 3.9 : 7 1005 Male 44. 8 1006 Female 41 9 1007 Fernale 53 10 1000 Malc 63 11 1009 Female 27. 12 1010 Male 31 13 1011 Male 39 . 14 1012 Female 39. 15 1013 Male 58. 16 1014 Male 47 , 17 1015 Male 23 19 1016 Female 35 19 1017 Male 28. 20 1018 rernale 58 . 21 1019 Male 51 22 1020 Female 23 1021 Male 24 1022 Fernale 64 25 1023 Female 34. 26 1024 Male 28. 27 1025 Male 23 20 1026 Fernale 40 29 1027 Malo 24. Sheet1 HW1_a 23: . HW1 b READY + 20% H Type here to search 0 1 WA 730F ENG 2:45 PM 1/30/2022