AutoSave OFF gyjfhgfhjhf - Saved to my Mac Page Layout Formulas Data Review View Tell me ? A e Define Name Trace Precedents ... Text Date & Time Lookup & Math & Reference Trig More Functions Name Manager Use in Formula Create from Selection Trace Dependents Remove Arrows Calculate Now 160 Show Error Watch Formulas Checking Window Calculation Options Calculate Sheet Home Insert Draw fx Insert Function AutoSum Recently Financial Logical Used H4 A fx B D E F G Comments Share H | J K L M N O P Q R S T U V W 1 2 Complete the below steps for 2 points each. Be sure to use the correct formula as formulas need to be visible in the Formula Bar... hard-coded numbers will be marked incorrect. 1) In the "Phone Verify" column, flag any incomplete phone number with a red "ERROR" message, otherwise a green "OK" message using an IF Statement and Conditional Formatting. 3 2) In the "Right (Zip)" column, pull all five digits of the zip code. 4 3) In the "Left (Name)" column, pull the left three characters of the customer's name. 5 4) In the "Mid (Phone)" column, pull the three digits of the prefix (the prefix is the middle three numbers, after the area code, and before the last four numbers). 6 5) In the "Right (Tran)" column, pull the right four characters of the transaction number. 7 6) In the "OrderID" column, combine the characters in the "Right (Zip)," "Left (Name)," Mid (Phone)," amd "Right (Tran)" columns. 8 7) In the "Item" and "Order Value" columns, pull the item and order value from the Veg-Lookup tab. 9 8) In the "Days Ordered" column, calculate the amount of time the order has been processing. 10 9) In the "Flag?" column, identify those orders over 40 days with a "LATE" message. 11 10) Sort by transaction date then by name. Also, make sure all columns are sized appropriately. 12 13 Transaction D Transaction Number Name 3227462286 SHANNON, HALLIE 3227725706 CASTANEDA, KELVIN 3227115537 HERMAN, JAQUAN 3227570862 HUNTER, SIENA 3227859878 COOK, CORY 3227159886 HERNANDEZ, MARIAM 3227652452 SERRANO, EVIE 3227979433 POLLARD, LUKA 3227468226 POTTS, ESTEBAN 3227892733 NUNEZ, KAYLYNN 3227610903 HULL, CELIA 3227926737 GUTIERREZ, ROWAN 3227327963 WEST, PATRICIA 3227744715 CLAYTON, KENYON 3227404772 VALENTINE, REY 3227811834 BENNETT, JASE 3227191918 DUARTE, ZACK 3227859879 HUNTER, DEANDRE 3227578414 MARQUEZ, SULLIVAN 14 2/18/22 15 2/10/22 16 1/3/22 17 2/22/22 18 19 3/1/22 1/20/22 20 1/1/22 21 1/9/22 22 1/8/22 23 1/4/22 24 2/13/22 25 1/15/22 26 27 2/7/22 2/9/22 28 2/14/22 29 1/11/22 30 2/25/22 31 1/2/22 32 1/6/22 33 2/5/22 34 1/12/22 35 2/2/22 36 1/13/22 37 1/5/22 38 1/22/22 39 1/30/22 40 1/26/22 41 2/8/22 3227236771 HARDIN, LUIS 42 2/15/22 3227100410 LAM, STERLING 43 2/23/22 44 2/1/22 45 1/21/22 46 1/17/22 47 1/16/22 48 1/29/22 49 1/23/22 50 2/11/22 51 2/19/22 52 1/7/22 53 1/25/22 54 2/17/22 55 1/24/22 56 1/19/22 Ready 3227123418 CONWAY, NIKO 3227481611 ROJAS, ALMA 3227551637 ROTH, JAKE 3227829581 FROST, DERRICK 3227180007 MEZA, DEBORAH 3227885051 RHODES, DEEGAN 3227266658 BRADFORD, DORIAN 3227464644 JOHNS, ALISSA 3227885876 VILLA, ISABEL 3227008182 CARR, AMIYAH 3227997822 FOSTER, RYLEE 3227546345 HANNA, JESSE 3227359210 WALTERS, ELSIE 3227614718 HALE, KONNOR 3227864523 FROST, MARYJANE 3227611679 OBRIEN, MAGGIE 3227169961 MEDINA, KOEN 3227201920 CISNEROS, SAM 3227020880 ROJAS, SHEA 3227613327 DELGADO, TATE 3227678553 GUTIERREZ, LEONEL 3227954791 ROMERO, DAISY Veg-Lookup Veg-Table (20) Accessibility: Investigate Street Address 8159 North 8th St 2 Jefferson Avenue 28 Taylor Street City, State, ZIP Hammonton, NJ 08037 Findlay, OH 45840 241 Fifth Court 487 Glenridge Dr 68 Orchard Street Houston, TX 77016 Severna Park, MD 21146 Seattle, WA 98144 54 Pendergast Ave 46 Brandywine St 489 Newport Dr 8785 Union St 9768 North Carson St 957 Harvard Street 53 Grove Lane Shirley, NY 11967 Fenton, MI 48430 Phone Phone Verify Right (Zip) Left (Name) Mid (Phone) Right (Tran) OrderID Item Order Value Days Ordered Flag? (447) 037-9426 (102) 285-6546 Wantagh, NY 11793 Venice, FL 34293 (264) 859-9097 (790) 560-5579 (690) 560-829 (808) 343-1492 302 Halifax Ave (513) 850-1563 73 School Dr Glenview, IL 60025 (765) 078-6066 7540 Hickory Road Chicopee, MA 01020 (335) 273-9042 7031 Magnolia Ave Phoenix, AZ 85021 (840) 875-6720 64 Greystone St Harrisburg, PA 17109 (689) 331-3007 Muskogee, OK 74403 (556) 939-7145 3 Oklahoma Lane Springboro, OH 45066 (881) 478-5335 5 Rosewood Ave Irvington, NJ 07111 (754) 973-490 682 Sulphur Springs Drive (720) 483-8932 Falls Church, VA 22041 (886) 965-9656 (155) 727-0123 Ocoee, FL 34761 Suffolk, VA 23434 (255) 934-1651 (791) 836-6582 The Villages, FL 32162 (438) 853-8765 Goose Creek, SC 29445 (516) 532-1530 Sun Prairie, WI 53590 (317) 899-5546 Madison, AL 35758 Erie, PA 16506 (265) 565-7073 (304) 214-0098 (620) 949-8083 (836) 571-6953 (977) 170-0068 (486) 443-00524 (494) 311-5802 (828) 217-2887 (100) 841-5219 (773) 976-4637 (481) 310-0299 (419) 986-0211 (330) 392-1007 (210) 763-6028 (248) 126-0282 (537) 766-5438 Hackettstown, NJ 07840 (779) 501-7525 (795) 886-6361 (988) 507-4511 (461) 120-21456 (499) 941-1565 846 Beacon St 500 Cardinal St 553 Willow St 298 San Juan Lane 112 E Wood Drive 8 South Paris Hill Street 9596 West Evergreen St 73 E Squaw Creek St 9383 Rosewood Ave 8 Manor Station Drive 59 Court Street 6 Johnson Rd 1 Chapel St 370 Edgefield St 4 Glen Eagles Dr 573 South San Juan Street 869 Bayberry Street 13 Glenwood Street 384 W Race St 822 Galvin Lane 15 Rockaway Ave 55 Ashley Court Bakery (15) Let's Eat (15) Canandaigua, NY 14424 Powhatan, VA 23139 Terre Haute, IN 47802 Hinesville, GA 31313 Whitestone, NY 11357 Kingston, NY 12401 Christiansburg, VA 24073 Nampa, ID 83651 Port Richey, FL 34668 Williamstown, NJ 08094 Gloucester, MA 01930 Grove City, OH 43123 Plattsburgh, NY 12901 Raleigh, NC 27603 New Albany, IN 47150 South Lyon, MI 48178 Vienna, VA 22180 Franklin Square, NY 11010 Payroll (10) Payroll Pivot Chart + 100%