ENGR 1550 Louisiana State University Upcycled Car Design Data Analysis Sheet I have put the full explanation in the pictures, please follow the steps and r

ENGR 1550 Louisiana State University Upcycled Car Design Data Analysis Sheet I have put the full explanation in the pictures, please follow the steps and requirements in the pictures Team ID
1700_01
1700_01
1700_01
1700_02
1700_02
1700_02
1700_03
1700_03
1700_03
1700_04
1700_04
1700_04
1700_05
1700_05
1700_05
1700_06
1700_06
1700_06
1700_07
1700_07
1700_07
1700_08
1700_08
1700_08
1700_09
1700_09
1700_09
1700_10
1700_10
1700_10
1700_11
1700_11
1700_11
1700_12
1700_12
1700_12
1700_13
1700_13
1700_13
Trial #
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
1
2
3
Distance [ft]
40.3
57
57
21.4
20.5
63.2
9.6
9
8.5
29.8
39
29
34.4
26.1
31.5
34.5
34.2
39.3
19.2
25.8
27
39.2
38.1
30.1
34.6
29.7
39
35
36.5
36.5
18.1
13.9
11.7
33.5
33.2
33
14
25
0
ENGR 1550 Spring 2020 – Individual Project 1 Data Analysis (Replaces Exam 2)
Due April 9th at 23:59
You may ask questions about this assignment from Dr. Ekoniak, Kristen, or Sagar. It is an honor code violation
to work together or ask questions about this assignment from other students in the class!
For this assignment, you will analyze data from the Upcycled Car Design Project using an Excel workbook.
Download the file from Blackboard and save it as LastName FirstName Exam2.xlsx
The Excel file contains UCDP trial data in the Data worksheet, organized as follows:
Column A: Team ID
Column B: Trial #
Column C: Trial Distance in feet
.
.
** Note that this file contains a fictional Team 13 that did not complete all trials (indicated by a zero)
Your Excel workbook should have the following features:
A. In Column D of the Data worksheet, add a formula that converts the Trial Distance in Column C from
feet to inches using the CONVERT function
B. Select columns A-D, and select PivotTable from the Insert Ribbon to create a PivotTable in a new
worksheet.
A1
Team ID
2 1700 01
3 1700_01
4 1700 01
51700_02
6 1700_02
7 1700_02
8 1700 03
9 1700_03
10 1700_03
11 (170004
12 170004
13 1700_04
141700_05
15 (1700 Os
16 1700 Os
17 1700 06
18 1700 06
19 1700 06
20 1700 07
21 1700_07
22 1700_07
231700_08
24 1700_08
251700_08
x fx Team ID
B
D
F
G
H
Trial Distance [ft] Distance in
40.3 483.6
2 57 684
3
Create Pivot Table
1
2
3 Choose the data that you want to analyze.
1
2
Select a table or range
3
1
Table/Range: Data!$A:$D
2
Use an external data source
3
1
Choose Connection… No data fields have been retrieved.
3
1
2
Choose where to place the Pivot Table,
New worksheet
Existing worksheet
Table/Range:
1
2
3
1
2
Cancel
C. Rename the new worksheet “Pivot.” On the new worksheet, you will see a blank Pivot Table as shown
below:
D
Pivot Table Fields
FIELD NAME
Q Searches
Platable
Team ID
To build a report, choose fields from the table
Field List
Trial
Distance [1]
Distance (in)
11
Filters
III Columns
16
17
18
19
21
22
23
24
25
26
27
28
29
30
31
Rows
? Values
34
D. You can use the PivotTable to “pivot” the data to summarize the trial data by team:
1. Drag the Team ID field to the Rows area
2. Drag the Trial # field to the Columns area
3. Drag the Distance [in] field to the Values area (this is the column you calculated in Part A)
D
Pivot Table Fields
FIELD NAME
Q Search fields
Team ID
Trial
Distance [ft]
Distance [in
9 Filters
III Columns
Trial
3 Sum of Distance in Column Labels
4 Row Labels
1 2 3 (blank) Grand Total
5 1700_01
483.6 684 684
18516
6 1700_02
256.8
246 7584
12612
7 1700 03
115.2 108 102
3252
8 1700_04
357.6 468 348
11736
9 1700_05
412.8 313.2 378
1104
10 1700_06
414 4104 471.6
1296
11 1700_07
230.4 309.6 324
864
12 1700_08
470.4 4572 3612
1288.8
13 1700_09
4152 356.4 468
1239.6
141700_10
420 438 438
1296
151700_11
217.2 166.8 140.4
524.4
16 1700 12
402 398.4 396
1196.4
17 1700 13
168 300 ol
468
18 (blank)
19 Grand Total
4363.2 4656 4869.6
13888.8
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Rows
? Values
Sum of Distanc.
Team ID
0
E. Copy the pivoted data to a new worksheet called Analysis
The Analysis worksheet should contain the following:
F. Descriptive Statistics (All Trials): Include an area (indicated with a beige background) where you
calculate using a formula and display statistics for all trials shown. Display distances to one decimal
place and standard deviation to three:
Descriptive Statistics
N
Min
Max
Mean
Median
Std Dev
39
0
758.4
356.1
378.0
158.950
G. Constraint Specifications: include areas (indicated with a blue background) for the user to specify the
project constraints:
1. the user will enter the minimum qualifying average trial distance in inches
2. the user will enter the minimum qualifying number of trials
Data
Data
Success Analysis
420.4
115.2
Team ID
1700_01
1700_02
1700_03
1700 04
1700 05
1700 06
co
1700_07
bur
1700 08
1700 09
1700 10
1700 11
1700 12
1700_13
Trial 1 Distance [in] Trial 2 Distance (in) Trial 3 Distance [in]
483.6
684
684
256.8
246
758.4
108
102
357.6
468
7.0
348
412.8
313.2
DIE
378
wo
414
410.4
HUNY
471.6
21.0
230.4
309.6
.
324
03.0
22
470.4
70.
457.2
131.2
361.2
415.2
356.4
468
420
438
438
217.2
166,8
140.4
402
398.4
396
168
300
Constraint Analysis
Distance Constraint (in) 120 Completed Trials Constraint
Team Average Distance [in] Constraint Met? Completed Trials Constraint Met?
617.2
TRUE
TRUE
TRUE
TRUE
108.4
FALSE
TRUE
391.2
TRUE
9
TRUE
368
TRUE
mo
TRUE
432
TRUE
hes
TRUE
289
TRUE
nos
who
TRUE
429.6
TRUE
mos
INUE
TRUE
mo
413.
TRUE
TRUE
432
TRUE
TRUE
174.8
TRUE
TRUE
398.8
TRUE
TRUE
156
TRUE
FALSE
Team ID
Result
1700_01 All Constraints Met
1700_02 All Constraints Met
1700 03 All Constraints NOT Met
1700 04
All Constraints Met
1700 05 All Constraints Met
namics VIEL
1700 06 All Constraints Met
arvo
mais vel
1700 07 All Constraints Met
–
noramic viet
1700 08 All Constraints Met
170 00 obami viet
1700 09 All Constraints Met
1700_10 All Constraints Met
1700 11 All Constraints Met
1700 12 All Constraints Met
1700 13 All Constraints NOT Met
H. Team Average Distance:
1. Add a column that uses a formula to calculate the team average trial distance
2. Add a column that uses a formula to determine whether each team met the distance constraint
specified by the user (i.e. the formula should reference the value in blue and not just have 120
inches specified in the formula itself)
3. Use conditional formatting to highlight average distances meeting the constraint in green in the
Team Average Distance column
4. Use conditional formatting to highlight teams that did not meet the constraint in red in the
Constraint Met column
5. conditional formatting must reference the constraint entered
1. Completed Trials:
1. Add additional columns to analyze the Completed Trials constraint in the same way
J. Success Analysis:
1. Add a column that uses a formula that displays “All Constraints Met” when both the distance
and completed trials constraints are met and otherwise displays “All Constraints NOT Met”
2. Use conditional formatting on the results columns, such that All Constraints Met is formatted
green, and All Constraints NOT Met is formatted red, and SUCCESS is formatted purple.
The figure below shows how the values will change if the constraints are changed to 30 feet (360 inches) and
two completed trials – your sheet should change similarly by only modifying the fields in blue:
Data
Success Analysis
Il Constraints
Team ID
1700_01
1700_02
1700 03
1700 03
1700
1700_04
1700 05
1700 06
170006
1700_07
1700_08
1700 09
1700 10
1700_11
1700_12
1700 13
Trial 1 Distance [in] Trial 2 Distance [in] Trial 3 Distance [in]
483.6
684
684
256.8
246
758.4
115.2
108
2576
102
357.6
468
468
412.8
348
313.2
12.0
378
414
3.13.2
378
410.4
29
* 10.4
471.6
230.4
471.6
230.4
309.6
309.6
324
324
470.4
470.4
457.2
361.2
415.2
356.4
468
420
438
438
217.2
166.8
140.4
402
398,4
396
168
300
0
Constraint Analysis
Distance Constraint (in) 360 Completed Trials Constraint
Team Average Distance (in) Constraint Met? Completed Trials Constraint Met?
617.2
TRUE
TRUE
420.4
TRUE
TRUE
108.4
108 4
FALSE
FALSE
291)
TRUE
TRUE
391.2
TRUE
3
368
TRUE
TRUE
2.
300
TRUE
3
TRUE
432
TRUE
TRUE
3.
24
TRUE
TRUE
288
TRUE
FALSE
3
FALSE
200
3
TRUE
429.
TRUE
429.6
3
TRUE
3
TRUE
413.2
TRUE
3
TRUE
432
TRUE
3
TRUE
174.8
FALSE
3
TRUE
398.8
TRUE
TRUE
156
FALSE
2
TRUE
Team ID
Result
1700_01 All Constraints Met
1700 02 All Constraints Met
1700 03 All
S NOT Met
1700_04
17000
All Constraints Met
1700 og All Constraints Met
hu All constraints Met
All Constraints Met
1700 06
170000 A constraints ivet
1700 07 |
1000
i constraints NOT Met
1700_08 All Constraints Met
1700 09 All Constraints Met
1700 10 All Constraints Met
1700 11 All Constraints NOT Met
All Constraints Met
1700 13 All Constraints NOT Met
1700_12
K. Team Performance Comparison Tool: Include an area (indicated with an orange background) where the
user can compare the performance of two teams:
Performance Comparison Tool
Result:
Constraints:
All Constraints Met
All Constraints Met
Average Distance:
432
617.2
Better
Team:
1700_06
1700 01
1700_01
1700_02
1700 03
1. Use data validation to restrict the user to entering a Team # from the list of teams using a
drop-down menu
2. Use VLOOKUP formuls to retrieve the Result and Average Distance values for the user-selected
teams
3. Write formulas that display the word “better” next to the team with better performance and
blank next to the other team. Better means that the team had a larger average distance and
also met all constraints.
The figures below show what should be displayed for a couple additional team pairs:
Performance Comparison Tool
Result:
Team:
1700_01
1700 01
Constraints:
All Constraints Met
All Constraints Met
Average Distance:
617.2
617.2
Performance Comparison Tool
Team:
1700_02
1700_07
Constraints:
All Constraints Met
All Constraints Met
Average Distance:
420.4
Result:
Better
288
L. Finally, all headings should be bold, gridlines should be added as shown, all data should be centered
Upload your workbook to Blackboard by midnight on April 9th

Purchase answer to see full
attachment