Assignment: Open Assignment 3 Data.xlsx Sales transactions for the first 3 months of the year are in this file. This includes: Date: data of transactions. Channel: The store or online channel the merchandise sold. North, South, East, and West stores. Company Website. link followed from an email, or link from a banner advertising. Customer: New, purchasing the first time or Member, have past transactions Category: Appliance, Camera, Computer, Phone, Toys, and TV. Brand: Manufacturer brand Type: Merchandise information Sale: Sale price COGS: Cost of merchandise for store e Create a table to calculate and format total daily sales, and total sales for each channel, each weekday. East West As a good practice you will not type any numbers or text strings as a criterion in your formulas Create a weekday column using proper formula to create the weekdays Calculate total daily sales for each day in the first column Calculate total daily sales for each day through each channel in the remaining columns of your table. Your formulas must copy down the columns to calculate sales for all weekdays. Use the following conditional formatting rules to format the Channel columns: 1. Website: 3 symbol indicators 2. Banner: data bars. Show only the bars, not numbers 3. Email: above average 4. North: color scale 5. South: 5 rating bars 6. East: 5 arrows directional 7. West: top 50% In a different part of the worksheet, calculate the average sales of 70" TV's for each weekday. Apply 3 star ratings conditional formatting. Use conditional formatting to apply a light green background color to the entire rows in data range, where the sales value is more than the median sale price of all items. (Excel formula MEDIAN is used). B C F the Safe Channel Customer Category Brand 231/01/2016 West 3 31/01/2016 West 421/91/7916 website $11/0015 North 31/0hrfois tast 11/01/2016 Wel 31/01/2016 West 31/01/2016 North 10 31/01/2016 North 11 32/01/2018 South 12 32/01/2014 Web 13 02/01/2016 North 34 32/01/2016 North 15 31/01/2016 North 16 31/01/2018 Web 17 22/01/2018 South Type Member Appliance itchenaid Food Proce Member Computer ASUS Desktop New Computer Dell Laptop Member Computer De Desktop Member Phone Motorals Android Member Phone Motorola Android Member Toys WB Games Toy Member TV Hie Member TV Visi Member Appliance Bosch New 32 10 Food Proce Appliance Kitchenaid Food Proce Member Appliance Kitchenaid free Member Computer ASUS Laptop Computer De Computer Dell Menber Laptop New Laptop Phone 18 32/01/2016 a 13 31/01/2016 a Member Member Phone Member Phone Member Desktop Motorola Android Motorola A Motorola A NE 30 31/01/2016 West Andrie 21 02/01/2016 West Phone Motors Andri WB Games Video ga 22 32/01/2018 North Toys Shake 33 32/01/2016 North Funko Vida Member Member Toys Member Toys Me 24 32/01/2016 We Disney St Video 35 32/01/2016 Wel Toys Disney Stut Video Member Toys Wb Games Toy bang 21 Member som Toys w Games Te Funko 24 33/01/2016 North 23/01/2016 South 23/01/2016 22/01/2014 30 31/01/2014 SALONIE N Tove Member Lat Dow Nando PAPER Funke 31 31/01/2016 Nintend Toy ws Games Toy 33 33/01/2016 North 11 31/01/2014 South 14 31/01/2016 Wed 33 31/01/2016 North 34 31/01/2016 South Funks Toy funks Video wames Toy w Games Video gam w Games Toy 2.Oss wes NE BE M PAN stor/tonn MURA en mundur Toys Member Member Member Tove ove Toys Member med Jon Member Tayt Member Tes Member Toys Member Teys Member Tey Member Toy Mai ww Video Toy Video gam G CICE 902 771 541,2 693.9 14032 1754 428 385.2 5369 767 664 3365 464.8 25 25 125 238 2142 430 2142 349 343 279.2 1306 1306 2180 1994 3067 12964 THA 2760.3 1453 RESPE 1453 11616 11M mw 1103 8816 3744 974.4 4167 416.7 AM 405 148.5 MRT SH 1218 451 199 530 580 355 335 783 770 16 19 M 18 23 27 27 43 19 28 32 26 www 44 2 34 20 H COGS 33 26 24 46 30 24 ars 44 SM A 19 11.2 10.8 21.5 9.5 113 14 10.4 22 17 165 14 23 12 12 T 1 I A L M N 0 P Formatting as T Styles M D 1 F Many INO, AS x fx 01/01/2016 A R D 68 03/01/2016 South 49 01/01/2016 South 10 01/01/2016 West 71 01/01/2016 East 72 03/2016 Website 13 03/01/2016 North 74 03/01/2016 South 75 03/01/2016 West 76 03/01/2018 We 77 03/01/2016 West 7 03/01/2016 W 79 03/01/2016 a 80 03/01/2016 South C Member Toys Member Toys Member Toys Member Toys New Toys Member Toys Member Toys Member Toys Member Toys Member Toys New Toys Member Toys Member TV Wb Games Toy Funko Video game Nintendo Toy Funko Video game Nintendo Toy WB Games Toy Funko Toy Funka Video gam wa Games Toy Funko Toy WB Games Tay we Games Video gam Hisense 32 81 03/01/2016 Banner New Hisense 33 TV TV 82 03/01/2016 Website Hisense 32 81 04/01/2015 Ber Appliance Frigidaire Freezer 54 04/01/2016 South Appliance Kitchenaid Food Proce 5 04/01/2016 We Appliance Kitchenaid Free 84 04/01/2016 a New New Member Member Member New Member Member Member New New Appliance Kitchenaid Freezer Computer ASUS Laptop 87 04/01/2016 Webute 04/01/2016 West 04/01/2016 South 30 04/01/2016 We 91 04/01/2016 Banner Desktop Computer Dell Computer Dell Tablet Desktop Motorola Android Motorola Android 33 04/01/2016 email 33 04/01/2018 W Member Ace Windows 94 04/01/2016 web New Motorola Android Samsung Android 95 04/01/2016 Banner New Computer Dell Phone Phone Phone Phone Phone Toys Tays Member Toys Member Toys Member Toys New WB Games Video game Member New 54 04/01/2016 South 97 04/01/2016 Website 38 04/01/2016 North 99 04/01/2016 West 100 04/01/2016 North 101 04/01/2016 Web Funks Video game DUI Video gam Dney Stur Video ga WB Games Toy Toys WB Games Toy VACAT G 12 35 24 16 48 20 54 15 34 64 38 33 184 188 183 1019 H 848 ======== 10 6 32 19 132 165.6 1493 164.7 917.1 500 3248 2923.2 1786 16074 1730 3384 1118 2006.2 1267 1076.95 1164 3047.6 729 $10.3 631 441.7 441 352.8 550 445 385 414 289.8 1 21 8.4 21 8.4 108 35 12 28 14 52 24 11 124 PA 1 1 K L M N 0 F mating at tatlles G . S T U W AS X fx 01/01/2016 B C D E 131 05/01/2016 Banner New 132 05/01/2016 Motorola Android Samsung Android WB Games Video gam 133 05/01/2014 West 134 05/01/2016 Webune 135 05/01/2016 East 135 05/01/2016 South 137 05/01/2016 Website 130 05/01/2016 Banner 139 05/01/2016 South 140 05/01/2016 North 141 05/01/2016 We 142 05/01/2016 South 143 05/01/2016 Banner 144 05/01/2016 South Phone Motorola Android New Phone Motorola Android Member Phone Acer Windows New Phone Member Phone Member Toys New Toys New Toys Member Toys Member Toys Member Toys New video gami Tay Video game Funko Funko DII WB Games Toy WB Games Toy Funko Toys Video gam New Toys WB Games Toy Funko New Toys Vide New Toys w Games Toy Norw Toys WsGames Toy 145 05/01/2018 Banner 146 01/01/2016 South 147 05/01/2016 Website 148 05/01/2016 South New Toys Funko Toy Nintendo Toy Tove Toys wa Games Video game 14 05/01/2015 Bar 150 05/01/2016 W 351 05/01/2016 West Member New Member Member Toys Funks Toy Tays WB Games Toy New TV 152 05/01/2016 Hanse 32 153 05/01/2016 TV 70 New Visio V Member TV 32 Hisense fv T 50 TV Toshiba Hisane 50 50 154 05/01/2016 W 355 01/01/2016 154 01/01/2016 North 117 05/01/2016 West 118 05/01/2016 East 159 06/01/2016 South 160 06/01/2018 W 141 06/01/2016 South 162 06/01/2018 Web 143 06/01/2018 We New TV Member Member TV Mamber TV New 60 Hivere Hisere Todize TODIES Appliance Frigidere Freezer Appliance Besch Appliance Encherait Food Proce Appliance Kitchenaid Microwave Niko OSLA Food Proce Membe Member New Member Camer ANG Ain G www. 675 576 627 581 798 10 29 65 28 27 42 33 28 19 37 55 59 45 30 45 53 220 2004 234 428 452 de 528 200 941 2452 819 1505 293 992 503 H wwww 472.5 403.2 501.6 406.7 558.6 12 114 32.5 112 13.5 21 13.2 14 76 18.5 27.5 29.5 24 12 34.5 26.3 198 1302.6 210.6 www 342.4 www 3616 www 422.4 472.7 22068 491 901 263.7 843.2 42755 1 7 4 K L M N 0 P Format Cell Formatting as Table Styles a . E het Dalmat W Format xfx 01/01/2016 0 C D ( New Toy Member Toys Funko Toy Nintendo Toy New WB Games Video gam Funks Video gam 32 WB Games Toy Hisense Visio ww 70 32 Tops 32 Toys Member Toys Member Toys Member TV Member TV Member TV Member TV Member TV Member Appliance Member Appliance Bosch Member Appliance G Member New Member Comput Member New 50 Microwave Freezer Microwave Freeper Marieu Tablet Appliance L Camers Sony Seg ASUS Computer De PROP Computer HP Computer De Desktop Dekop Desktop Tablet Member New Computer De Computer Dell Laptop Phone Phone LG 2 Android New Android Phone Windows LG Lumia P Motorola Android Yepp New Now Phone po Phone PAN Phone pa One Windows Motorala Andr New Samsung Android www Member Plone P Membe Tays TA New Tays w Games Video gam Nintendo Toy Funks fo Toys Toy Tay fucko Ws Game Tay Member Toys Member Top WS Games Video ga AS 188 06/01/2016 W 189 06/01/2016 We 100 06/01/2016 Banner 191 06/01/2016 a 131 06/01/2016 ww 293 06/01/2018 Wet 194 06/01/2016 th 195 06/01/2016 West 136 06/01/2016 West 137 06/01/2016 W 198 07/01/2016 West 199 07/01/2016 Wabute 2:00 07/01/2016 Wel 301 07/01/2016 East 303 07/01/2016 Banner 201 07/01/2018 North 204 07/01/2018 2007/01/2014 We 204 07/01/2016 Website New 207 07/01/2016 wate New 208 07/01/2016 North P 209 07/01/2016 Website 210 07/01/2010 Web 211 07/01/2016 South 111.07/1.2201 Sams 211 07/01/2016 South 113 31/01/2016 214 07/01/2016 W 213 07/01/2016 Eat CALE S 216 07/01/2016 South 217 07/01/2016 South 218 07/01/2016 tam 218 07/01/2016 Cas 220 01/01/2016 South 221 67/01/2016 ta F 0 20 34 34 17 . 212 De 18 13.6 6.8 4 2 1908 1385 300.25 180.9 201 180 162 435.2 544 417 930 3753 837 B 470 423 B 198014 4464 1733 147305 W 2204 F 558 P 1433 1289 P 527 1548 1047 1220 296 20 394 453 671 270 640 465 465 20 31 M W 66 25 31 10 16 10 474.3 MA 4332 349.95 Lengy 976 20 2013 275.8 345.6 469,7 Gaw 216 Am 476 335.5 799 8.4 33 12.5 155 S 6.4 1 1 + K Merge & Center L M N a * Conditional Format Formatting as Table a A 1 Call Styles T U Delale V Format W Assignment: Open Assignment 3 Data.xlsx Sales transactions for the first 3 months of the year are in this file. This includes: Date: data of transactions. Channel: The store or online channel the merchandise sold. North, South, East, and West stores. Company Website. link followed from an email, or link from a banner advertising. Customer: New, purchasing the first time or Member, have past transactions Category: Appliance, Camera, Computer, Phone, Toys, and TV. Brand: Manufacturer brand Type: Merchandise information Sale: Sale price COGS: Cost of merchandise for store e Create a table to calculate and format total daily sales, and total sales for each channel, each weekday. East West As a good practice you will not type any numbers or text strings as a criterion in your formulas Create a weekday column using proper formula to create the weekdays Calculate total daily sales for each day in the first column Calculate total daily sales for each day through each channel in the remaining columns of your table. Your formulas must copy down the columns to calculate sales for all weekdays. Use the following conditional formatting rules to format the Channel columns: 1. Website: 3 symbol indicators 2. Banner: data bars. Show only the bars, not numbers 3. Email: above average 4. North: color scale 5. South: 5 rating bars 6. East: 5 arrows directional 7. West: top 50% In a different part of the worksheet, calculate the average sales of 70" TV's for each weekday. Apply 3 star ratings conditional formatting. Use conditional formatting to apply a light green background color to the entire rows in data range, where the sales value is more than the median sale price of all items. (Excel formula MEDIAN is used). B C F the Safe Channel Customer Category Brand 231/01/2016 West 3 31/01/2016 West 421/91/7916 website $11/0015 North 31/0hrfois tast 11/01/2016 Wel 31/01/2016 West 31/01/2016 North 10 31/01/2016 North 11 32/01/2018 South 12 32/01/2014 Web 13 02/01/2016 North 34 32/01/2016 North 15 31/01/2016 North 16 31/01/2018 Web 17 22/01/2018 South Type Member Appliance itchenaid Food Proce Member Computer ASUS Desktop New Computer Dell Laptop Member Computer De Desktop Member Phone Motorals Android Member Phone Motorola Android Member Toys WB Games Toy Member TV Hie Member TV Visi Member Appliance Bosch New 32 10 Food Proce Appliance Kitchenaid Food Proce Member Appliance Kitchenaid free Member Computer ASUS Laptop Computer De Computer Dell Menber Laptop New Laptop Phone 18 32/01/2016 a 13 31/01/2016 a Member Member Phone Member Phone Member Desktop Motorola Android Motorola A Motorola A NE 30 31/01/2016 West Andrie 21 02/01/2016 West Phone Motors Andri WB Games Video ga 22 32/01/2018 North Toys Shake 33 32/01/2016 North Funko Vida Member Member Toys Member Toys Me 24 32/01/2016 We Disney St Video 35 32/01/2016 Wel Toys Disney Stut Video Member Toys Wb Games Toy bang 21 Member som Toys w Games Te Funko 24 33/01/2016 North 23/01/2016 South 23/01/2016 22/01/2014 30 31/01/2014 SALONIE N Tove Member Lat Dow Nando PAPER Funke 31 31/01/2016 Nintend Toy ws Games Toy 33 33/01/2016 North 11 31/01/2014 South 14 31/01/2016 Wed 33 31/01/2016 North 34 31/01/2016 South Funks Toy funks Video wames Toy w Games Video gam w Games Toy 2.Oss wes NE BE M PAN stor/tonn MURA en mundur Toys Member Member Member Tove ove Toys Member med Jon Member Tayt Member Tes Member Toys Member Teys Member Tey Member Toy Mai ww Video Toy Video gam G CICE 902 771 541,2 693.9 14032 1754 428 385.2 5369 767 664 3365 464.8 25 25 125 238 2142 430 2142 349 343 279.2 1306 1306 2180 1994 3067 12964 THA 2760.3 1453 RESPE 1453 11616 11M mw 1103 8816 3744 974.4 4167 416.7 AM 405 148.5 MRT SH 1218 451 199 530 580 355 335 783 770 16 19 M 18 23 27 27 43 19 28 32 26 www 44 2 34 20 H COGS 33 26 24 46 30 24 ars 44 SM A 19 11.2 10.8 21.5 9.5 113 14 10.4 22 17 165 14 23 12 12 T 1 I A L M N 0 P Formatting as T Styles M D 1 F Many INO, AS x fx 01/01/2016 A R D 68 03/01/2016 South 49 01/01/2016 South 10 01/01/2016 West 71 01/01/2016 East 72 03/2016 Website 13 03/01/2016 North 74 03/01/2016 South 75 03/01/2016 West 76 03/01/2018 We 77 03/01/2016 West 7 03/01/2016 W 79 03/01/2016 a 80 03/01/2016 South C Member Toys Member Toys Member Toys Member Toys New Toys Member Toys Member Toys Member Toys Member Toys Member Toys New Toys Member Toys Member TV Wb Games Toy Funko Video game Nintendo Toy Funko Video game Nintendo Toy WB Games Toy Funko Toy Funka Video gam wa Games Toy Funko Toy WB Games Tay we Games Video gam Hisense 32 81 03/01/2016 Banner New Hisense 33 TV TV 82 03/01/2016 Website Hisense 32 81 04/01/2015 Ber Appliance Frigidaire Freezer 54 04/01/2016 South Appliance Kitchenaid Food Proce 5 04/01/2016 We Appliance Kitchenaid Free 84 04/01/2016 a New New Member Member Member New Member Member Member New New Appliance Kitchenaid Freezer Computer ASUS Laptop 87 04/01/2016 Webute 04/01/2016 West 04/01/2016 South 30 04/01/2016 We 91 04/01/2016 Banner Desktop Computer Dell Computer Dell Tablet Desktop Motorola Android Motorola Android 33 04/01/2016 email 33 04/01/2018 W Member Ace Windows 94 04/01/2016 web New Motorola Android Samsung Android 95 04/01/2016 Banner New Computer Dell Phone Phone Phone Phone Phone Toys Tays Member Toys Member Toys Member Toys New WB Games Video game Member New 54 04/01/2016 South 97 04/01/2016 Website 38 04/01/2016 North 99 04/01/2016 West 100 04/01/2016 North 101 04/01/2016 Web Funks Video game DUI Video gam Dney Stur Video ga WB Games Toy Toys WB Games Toy VACAT G 12 35 24 16 48 20 54 15 34 64 38 33 184 188 183 1019 H 848 ======== 10 6 32 19 132 165.6 1493 164.7 917.1 500 3248 2923.2 1786 16074 1730 3384 1118 2006.2 1267 1076.95 1164 3047.6 729 $10.3 631 441.7 441 352.8 550 445 385 414 289.8 1 21 8.4 21 8.4 108 35 12 28 14 52 24 11 124 PA 1 1 K L M N 0 F mating at tatlles G . S T U W AS X fx 01/01/2016 B C D E 131 05/01/2016 Banner New 132 05/01/2016 Motorola Android Samsung Android WB Games Video gam 133 05/01/2014 West 134 05/01/2016 Webune 135 05/01/2016 East 135 05/01/2016 South 137 05/01/2016 Website 130 05/01/2016 Banner 139 05/01/2016 South 140 05/01/2016 North 141 05/01/2016 We 142 05/01/2016 South 143 05/01/2016 Banner 144 05/01/2016 South Phone Motorola Android New Phone Motorola Android Member Phone Acer Windows New Phone Member Phone Member Toys New Toys New Toys Member Toys Member Toys Member Toys New video gami Tay Video game Funko Funko DII WB Games Toy WB Games Toy Funko Toys Video gam New Toys WB Games Toy Funko New Toys Vide New Toys w Games Toy Norw Toys WsGames Toy 145 05/01/2018 Banner 146 01/01/2016 South 147 05/01/2016 Website 148 05/01/2016 South New Toys Funko Toy Nintendo Toy Tove Toys wa Games Video game 14 05/01/2015 Bar 150 05/01/2016 W 351 05/01/2016 West Member New Member Member Toys Funks Toy Tays WB Games Toy New TV 152 05/01/2016 Hanse 32 153 05/01/2016 TV 70 New Visio V Member TV 32 Hisense fv T 50 TV Toshiba Hisane 50 50 154 05/01/2016 W 355 01/01/2016 154 01/01/2016 North 117 05/01/2016 West 118 05/01/2016 East 159 06/01/2016 South 160 06/01/2018 W 141 06/01/2016 South 162 06/01/2018 Web 143 06/01/2018 We New TV Member Member TV Mamber TV New 60 Hivere Hisere Todize TODIES Appliance Frigidere Freezer Appliance Besch Appliance Encherait Food Proce Appliance Kitchenaid Microwave Niko OSLA Food Proce Membe Member New Member Camer ANG Ain G www. 675 576 627 581 798 10 29 65 28 27 42 33 28 19 37 55 59 45 30 45 53 220 2004 234 428 452 de 528 200 941 2452 819 1505 293 992 503 H wwww 472.5 403.2 501.6 406.7 558.6 12 114 32.5 112 13.5 21 13.2 14 76 18.5 27.5 29.5 24 12 34.5 26.3 198 1302.6 210.6 www 342.4 www 3616 www 422.4 472.7 22068 491 901 263.7 843.2 42755 1 7 4 K L M N 0 P Format Cell Formatting as Table Styles a . E het Dalmat W Format xfx 01/01/2016 0 C D ( New Toy Member Toys Funko Toy Nintendo Toy New WB Games Video gam Funks Video gam 32 WB Games Toy Hisense Visio ww 70 32 Tops 32 Toys Member Toys Member Toys Member TV Member TV Member TV Member TV Member TV Member Appliance Member Appliance Bosch Member Appliance G Member New Member Comput Member New 50 Microwave Freezer Microwave Freeper Marieu Tablet Appliance L Camers Sony Seg ASUS Computer De PROP Computer HP Computer De Desktop Dekop Desktop Tablet Member New Computer De Computer Dell Laptop Phone Phone LG 2 Android New Android Phone Windows LG Lumia P Motorola Android Yepp New Now Phone po Phone PAN Phone pa One Windows Motorala Andr New Samsung Android www Member Plone P Membe Tays TA New Tays w Games Video gam Nintendo Toy Funks fo Toys Toy Tay fucko Ws Game Tay Member Toys Member Top WS Games Video ga AS 188 06/01/2016 W 189 06/01/2016 We 100 06/01/2016 Banner 191 06/01/2016 a 131 06/01/2016 ww 293 06/01/2018 Wet 194 06/01/2016 th 195 06/01/2016 West 136 06/01/2016 West 137 06/01/2016 W 198 07/01/2016 West 199 07/01/2016 Wabute 2:00 07/01/2016 Wel 301 07/01/2016 East 303 07/01/2016 Banner 201 07/01/2018 North 204 07/01/2018 2007/01/2014 We 204 07/01/2016 Website New 207 07/01/2016 wate New 208 07/01/2016 North P 209 07/01/2016 Website 210 07/01/2010 Web 211 07/01/2016 South 111.07/1.2201 Sams 211 07/01/2016 South 113 31/01/2016 214 07/01/2016 W 213 07/01/2016 Eat CALE S 216 07/01/2016 South 217 07/01/2016 South 218 07/01/2016 tam 218 07/01/2016 Cas 220 01/01/2016 South 221 67/01/2016 ta F 0 20 34 34 17 . 212 De 18 13.6 6.8 4 2 1908 1385 300.25 180.9 201 180 162 435.2 544 417 930 3753 837 B 470 423 B 198014 4464 1733 147305 W 2204 F 558 P 1433 1289 P 527 1548 1047 1220 296 20 394 453 671 270 640 465 465 20 31 M W 66 25 31 10 16 10 474.3 MA 4332 349.95 Lengy 976 20 2013 275.8 345.6 469,7 Gaw 216 Am 476 335.5 799 8.4 33 12.5 155 S 6.4 1 1 + K Merge & Center L M N a * Conditional Format Formatting as Table a A 1 Call Styles T U Delale V Format W