powerpoint presentation
Johnnetwork 
Data_Analysis_Exercise_1_v5.xlsxStart Here
| Data Analysis Exercise 1 | |||||||||||
| Please read the following carefully and then answer the below 8 questions. Every part fitted to a vehicle needs to have a part number to enable correct part ordering (amongst many other important reasons). Table 1 below contains data relating to 10 part numbers. These part numbers are allocated to different variants of car seat. The sheet called "Definitions & Functions" contains: - definitions that you will need to know in order to answer the below questions. - 6 basic Excel Functions. Several of these Functions (possibly combined) will be needed to answer questions 1-5. Important Notes: - Questions 1-5 will require formulas as answers. These formulas should be created in the first row and dragged down to all rows. ie.must be the same generic formula in each row - Questions 6-8 will require text answers only. No formulas or functions required. - To be fair to people with varying Excel backgrounds, you're only allowed to use the functions listed in the 'Definitions & Functions' sheet | |||||||||||
| Available points | 0.5 | 0.5 | 1 | 2 | 2 | 1 | 1 | 2 | |||
| Table 1 | |||||||||||
| PART NUMBER | DESCRIPTION | DRIVE | MINOR FEATURE CODES | 1 PREFIX | 2 BASE | 3 SUFFIX | 4 Base | 5 RHS/LHS | 6 SEAT DRV/PASS | 7 | 8 |
| 9N62-B711M15-ADX | FRONT SEAT ASY RHS COMP 4WAY | P | BBHBU BBHBZ CV7BC CWSBD CXSBB CZQBE CZ2BB DQNBB HDEBD 3YW11 | ||||||||
| 6N62-B711M16-BBFX | FRONT SEAT ASY COMP LHS 4WAY | Q | BBHDG CV7BC CWSBD CXSBC CZQBE CZ2BB DQNBC HDEBD 3D311 | ||||||||
| 9N62-B711M15-BWDX | FRONT SEAT ASY COMP RHS 4WAY | Q | CV8BC CWSBD CXTBC CZRBE CZ3BB CZ6BK DQOBB JCQBE 3ZB11 | ||||||||
| 9N62-B711M17-BBDX | FRONT SEAT 4WAY ASY COMP RHS | P | CV7BC CWSBD CXSBB CZQBE CZ2BC DQNBB HDEBD 3YW11 | ||||||||
| 8N62-B711M18-ACX | 4WAY FRONT LHS SEAT ASY COMP | Q | BBHDG CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBB HDEBD 3K011 | ||||||||
| 9N62-B711M18-CGCX | 4WAY FRONT SEAT LHS ASY COMP | Q | BBHBU BBHBZ CV7BC CWSBD CXSBC CZQBE CZ2BC DQNBC HDEBC 3K011 | ||||||||
| 9N62-B711L15-ADX | FRONT SEAT ASY COMP RHS 2WAY | Q | BBHBU BBHBZ CV8BC CWSBD CXTBB CZRBC CZ3BB CZ6BK DQOBC JCQBE 3YW11 | ||||||||
| 9N62-B711N15-BKDX | RHS FRONT SEAT ASY COMP 6WAY | P | CV7BC CWSBD CXSBC CZRBT CZ2BB DQNBC HDEBD 3ZB11 | ||||||||
| 9N62-B711M18-WDX | 4WAY LHS FRONT SEAT ASY COMP | Q | BBHBU BBHBZ CV7BB CWSBD CXSBB CZQBE CZ2BC DQNBC HDEBC 3YW11 | ||||||||
| 8N62-B711N15-BIDX | RHS FRONT SEAT ASY 2WAY COMP | P | CV7BC CWSBD CXSBC CZQBH CZ2BB DQNBC HDEBC 3ZB11 | ||||||||
| Questions | |||||||||||
| Question 1 | Create a formula in column <E> that extracts the PREFIX only from column A | ||||||||||
| Question 2 | Create a formula in column <F> that extracts the BASE only from column A | ||||||||||
| Question 3 | Create a formula in column <G> that extracts the SUFFIX only from column A | ||||||||||
| Question 4 | YOU HAVE BEEN MISINFORMED! Apparently the BASE section of a part number can vary between 6 and 8 digits. (and is not always 7 digits as mentioned in the Definitions & Functions sheet). Even though the above part numbers all contain 7 digit BASES, create a formula in column <H> that shows the BASE, but could also handle varying lengths. (You can re-use your formula from Question 2, if it already handles this scenario). | ||||||||||
| Question 5 | The Vehicle Seat part numbers are defined as LHS or RHS. This information is shown within the Description in column <B>. Create a formula in column <I> that extracts the LHS or RHS text only. | ||||||||||
| Question 6 | Using only the information in columns <B> & <C>, confirm in column <J> whether each seat is a DRIVER or PASSENGER seat. (Note, no formulas required for this question) | ||||||||||
| Question 7 | The string of MINOR FEATURE CODES shown in column <D> helps to define the specifc usage of the relevant part number. If the string contains the MINOR FEATURE CODE [CZQ**] eg. CZQBD, CZQBE,CZQBH, CZQBM or CZQBU, then the part number is a driver seat If the string contains the MINOR FEATURE CODE [CZR**] eg. CZRBC, CZRBE, CZRBL, CZRBT or CZRBV, then the part number is a passenger seat One Seat part number has a clear data error in Table 1. Highlight which part this is, in the appropriate cell in column <K>. | ||||||||||
| Question 8 | Now to fix the problem part number found in Question 7: Firstly, it's been confirmed that the data in column <B> and column <C> in Table 1 is definitely correct. Secondly, the sheet "Part Number Base Definition" shows how part number BASES are initially defined according to seat features. (Note, this is not always a 1 to 1 relationship) Using the above information, determine the correction that is required to fix the data error. Show this in column <L>. |
Definitions & Functions
| Excel Functions | |||||
| Function | Syntax | Example Input | Formula | Example Output | Comments |
| LEFT | LEFT(text,num_chars) | formulas can be useful | LEFT(C3,8) | formulas | |
| RIGHT | RIGHT(text,num_chars) | formulas can be useful | RIGHT(C4,6) | useful | |
| MID | MID(text,start_num,num_chars) | formulas can be useful | MID(C5,10,6) | can be | |
| LEN | LEN(text) | formulas can be useful | LEN(C6) | 22 | |
| FIND | FIND(find_text,within_text,start_num) | formulas can be useful | FIND("u",C7,1) | 5 | - start_num indicates the digit from where the search should start Note, this formula is case sensitive |
| formulas can be useful | FIND("u",C8,6) | 17 | |||
| TRIM | TRIM(text) | formulas can be useful | TRIM(C9) | formulas can be useful | Removes any leading or trailing spaces |
| Useful Definitions | |||||
| Part Number | A part number is used to identify all vehicle parts. It consists of 3 elements: Prefix, Base & Suffix. The format of the part number is always: "Prefix-Base-Suffix" The Prefix is always 4 digits The Base is always 7 digits The Suffix is always 3 or 4 digits | ||||
| LHS | Left Hand Side | ||||
| RHS | Right Hand Side | ||||
| DRIVE | The code "DRIVE" indicates whether the part number is used on LHD or RHD vehicles. DRIVE code "Q" = LHD DRIVE code "P" = RHD | ||||
| LHD | Left Hand Drive | ||||
| RHD | Right Hand Drive | ||||
| BS | Body Style eg. 3 door, 4 door or 5 door | ||||
| VS | Vehicle Series (low spec, medium spec, high spec) | ||||
| DRV | Driver | ||||
| PASS | Passenger | ||||
| Minor Feature Code | A minor feature code is a 5 digit code. This code defines a specific feature on a vehicle. Multiple minor feature codes can be combined into a string to form a specific vehicle usage for the relevant part | ||||
| CZQBD | Minor Feature Code defining an 6 Way Driver Seat | ||||
| CZQBE | Minor Feature Code defining a 4 Way Driver Seat | ||||
| CZQBH | Minor Feature Code defining an 2 Way Driver Seat | ||||
| CZQBM | Minor Feature Code defining an 8 Way Driver Seat | ||||
| CZQBU | Minor Feature Code defining an 10 Way Driver Seat | ||||
| CZRBC | Minor Feature Code defining a 2 Way Passenger Seat | ||||
| CZRBE | Minor Feature Code defining a 4 Way Passenger Seat | ||||
| CZRBL | Minor Feature Code defining a 2 Way Sports Passenger Seat | ||||
| CZRBT | Minor Feature Code defining an 8 Way Passenger Seat | ||||
| CZRBV | Minor Feature Code defining a 6 Way Passenger Seat |
Part Number Base Definition
| Part Number Base | VEHICLE LINE | BS | VS | DRIVE | SEAT TYPE | HEATED SEATS | ||||||||
| B711L15 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBC | CZ3BB |
| B711L15 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBL | CZ3BB |
| B711L16 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBL | CZ3BB |
| B711L16 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBC | CZ3BB |
| B711L17 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBL | CZ3BC |
| B711L17 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBC | CZ3BC |
| B711L18 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBC | CZ3BC |
| B711L18 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBL | CZ3BC |
| B711M15 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBE | CZ3BB |
| B711M15 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBE | CZ2BB |
| B711M16 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBE | CZ3BB |
| B711M16 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBE | CZ2BB |
| B711M17 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZRBE | CZ3BC |
| B711M17 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBE | CZ2BC |
| B711M18 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZRBE | CZ3BC |
| B711M18 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBE | CZ2BC |
| B711N15 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBD | CZ2BB |
| B711N15 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBH | CZ2BB |
| B711N15 | DC5 | ID | -- | - | - | - | - | - | - | P | - | - | CZQBM | CZ2BB |
| B711N15 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZRBT | CZ3BB |
| B711N16 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBD | CZ2BB |
| B711N16 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBH | CZ2BB |
| B711N16 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZQBM | CZ2BB |
| B711N16 | DC5 | ID | D0 | - | - | - | - | - | - | P | - | - | CZRBT | CZ3BB |
| B711N17 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBD | CZ2BC |
| B711N17 | DC5 | 10 | -- | - | - | - | - | - | - | P | - | - | CZQBH | CZ2BC |
| B711N17 | DC5 | ID | D0 | - | - | - | - | - | - | P | - | - | CZQBM | CZ2BC |
| B711N17 | DC5 | ID | D0 | - | - | - | - | - | - | P | - | - | CZQBU | CZ2BC |
| B711N17 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZRBT | CZ3BC |
| B711N17 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZRBV | CZ3BC |
| B711N18 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBD | CZ2BC |
| B711N18 | DC5 | 10 | -- | - | - | - | - | - | - | Q | - | - | CZQBH | CZ2BC |
| B711N18 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZQBM | CZ2BC |
| B711N18 | DC5 | ID | D0 | - | - | - | - | - | - | Q | - | - | CZQBU | CZ2BC |
| B711N18 | DC5 | ID | D0 | - | - | - | - | - | - | P | - | - | CZRBT | CZ3BC |
| B711N18 | DC5 | ID | D0 | - | - | - | - | - | - | P | - | - | CZRBV | CZ3BC |
| C711L15 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBL | CZ3BB |
| C711L15 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBC | CZ3BB |
| C711L16 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBC | CZ3BB |
| C711L16 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBL | CZ3BB |
| C711L17 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBC | CZ3BC |
| C711L17 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBL | CZ3BC |
| C711L18 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBC | CZ3BC |
| C711L18 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBL | CZ3BC |
| C711M15 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBE | CZ3BB |
| C711M15 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZQBE | CZ2BB |
| C711M16 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZQBE | CZ2BB |
| C711M16 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBE | CZ3BB |
| C711M17 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBE | CZ3BC |
| C711M17 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZQBE | CZ2BC |
| C711M18 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZQBE | CZ2BC |
| C711M18 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBE | CZ3BC |
| C711N15 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZQBH | CZ2BB |
| C711N15 | DC5 | EB | D0 | - | - | - | - | - | - | P | - | - | CZQBM | CZ2BB |
| C711N15 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBT | CZ3BB |
| C711N16 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZQBH | CZ2BB |
| C711N16 | DC5 | EB | D0 | - | - | - | - | - | - | Q | - | - | CZQBM | CZ2BB |
| C711N16 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBT | CZ3BB |
| C711N17 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZQBH | CZ2BC |
| C711N17 | DC5 | EB | D0 | - | - | - | - | - | - | P | - | - | CZQBM | CZ2BC |
| C711N17 | DC5 | EB | D0 | - | - | - | - | - | - | P | - | - | CZQBU | CZ2BC |
| C711N17 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBV | CZ3BC |
| C711N17 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZRBT | CZ3BC |
| C711N18 | DC5 | EB | -- | - | - | - | - | - | - | Q | - | - | CZQBH | CZ2BC |
| C711N18 | DC5 | EB | D0 | - | - | - | - | - | - | Q | - | - | CZQBM | CZ2BC |
| C711N18 | DC5 | EB | D0 | - | - | - | - | - | - | Q | - | - | CZQBU | CZ2BC |
| C711N18 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBV | CZ3BC |
| C711N18 | DC5 | EB | -- | - | - | - | - | - | - | P | - | - | CZRBT | CZ3BC |
