Question
The Excel file DataValidationQuiz.xlsx contains the following raw data gathered from a shipping company Product ID Shipment tracking number Destination ZIP code Country Validation rules
The Excel file DataValidationQuiz.xlsx contains the following raw data gathered from a shipping company
Product ID
Shipment tracking number
Destination ZIP code
Country
Validation rules for each data item are given as follows
Product ID always starts with PHX-
Shipment tracking number always starts with QXC- and ends with -2017
Zip Code: Five-digit integer
Country: USA, US, United States of America
Write an Excel formula to validate each of above item and print Valid if the data satisfies the validation rules, otherwise print Invalid. Extra column (Valid or Invalid?) is provided to write the validation formulas.
Note: Blanks are considered as invalid data.
You must use Excel formulas to validate the data.
| ProductID | Valid or Invalid? | Country | Valid or Invalid? | Tracking Number | Valid or Invalid? | Zip Code | Valid or Invalid? |
| PHX-324531 | US | Valid | QXC-3331265-2017 | 77004 | |||
| PHX-324521 | Texas | Invalid | QXs53234d65-2017 | 77054 | |||
| PX-34543 | USA | Valid | QXC-3324523-2017 | 40029 | |||
| X-324534 | United | Invalid | QXC-3454365-2017 | ||||
| PHX-541223 | USA | Valid | QXC-4545365-2017 | TX | |||
| PHX5247783 | United States of America | Valid | QXC-5463565-2017 | 50063 | |||
| PHX-324578 | US | Valid | QXC-545452132017 | 70010 | |||
| PHX-35738 | USA | Valid | QXC-543782132017 | 56102 | |||
| PHX-348759 | USA | Valid | QXC-563154632017 | 89136 | |||
| PHX-57953 | US | Valid | QXC-4654545-2017 | 56943 | |||
| PHX-357646 | 77004 | Invalid | QXC-5465455-2017 | ||||
| PHX-663255 | Texas | Invalid | QXC-5234531-2017 | 70058 | |||
| PHX-354641 | Invalid | QXC-44444345-2017 | 70058 | ||||
| HX-3641212 | Invalid | QXC-56545645-2017 | 70059 | ||||
| PHX-325765 | Texas | Invalid | 7 |
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started
Database Processing Fundamentals, Design, and Implementation
Authors: David M. Kroenke, David J. Auer
14th edition
133876705, 9781292107639, 1292107634, 978-0133876703
