Note: Please explain step by step how to replicate this information in Excel, no
ID: 3043992 • Letter: N
Question
Note: Please explain step by step how to replicate this information in Excel, not just the output (graphs), thanks!
Data:
Travel Characteristics for Indiana and the United States: 2006 and 2016
Indiana
United States
2006
2016
Percent change
2006
2016
Percent change
People who live in area
Percent traveling households
73.8
86.1
16.7%
81.7
94.3
15.4%
Trips per household
5
5.3
6.0%
4.6
6.5
41.3%
Trips per traveling household
7
7.3
4.3%
6.5
8.2
26.2%
Trips per capita
2.5
4.6
84.0%
2.8
4.7
67.9%
Trips per traveler
2.7
4.9
81.5%
4.5
5.7
26.7%
Miles per traveler
1722.4
3,441
99.8%
3,218
4,649
44.5%
Miles per trip
478.9
807.7
68.7%
804.5
975.2
21.2%
People who visit area
Trips per traveler
4.3
5.8
34.9%
4.5
5.7
26.7%
Miles per traveler
2,226
3,300
48.2%
3,218
4,649
44.5%
Miles per trip
591.2
675.7
14.3%
804.5
975.2
21.2%
Origin States of People Who Visit Indiana and Destination States of People Who Live in Indiana (In thousands): 2016
State
Visiting Indiana
From Indiana
Illinois
3,423
2,781
Ohio
2,596
2,331
Kentucky
1,576
2,015
Michigan
1,143
2,416
Wisconsin
520
315
Missouri
434
692
Tennessee
339
831
Pennsylvania
332
349
Florida
286
1176
California
281
202
Iowa
213
147
Georgia
212
171
Texas
198
247
New York
164
106
North Carolina
160
139
New Jersey
117
41
Minnesota
116
117
Virginia
109
128
Maryland
102
48
West Virginia
97
120
South Carolina
90
186
Kansas
87
49
Alabama
86
230
Oklahoma
77
65
Nebraska
74
24
Arizona
51
130
Massachusetts
47
83
Arkansas
45
118
Colorado
45
178
Mississippi
43
40
Connecticut
34
40
Washington
26
57
Louisiana
24
58
New Mexico
22
20
Oregon
16
28
Nevada
14
209
Delaware
13
8
District of Columbia
13
179
South Dakota
13
21
Utah
13
41
North Dakota
12
10
Maine
11
15
New Hampshire
11
11
Hawaii
10
27
Wyoming
8
27
Idaho
6
28
Montana
5
10
Vermont
5
4
Rhode Island
3
15
Alaska
2
21
Travel Rates by Trip and Traveler Characteristics for Indiana: 2016
Person trips (in thousands)
Number of travelers (in thousands)
Trips per traveler
Miles (in millions)
Miles per traveler
Miles per trip
PEOPLE WHO LIVE IN AREA
23,715
5,732
4.5
16,869
3,066
748
Means of transportation
Personal use vehicle
20,508
5,364
3.2
11,335
2,220
581
Commercial air
2,614
1,455
2
5,120
3,695
2,059
Intercity bus
28
29
1
16
592
615
Train
32
24
1.4
29
1,239
920
Other
533
357
1.6
370
1,091
729
23,714
7,229
9
16,870
8,837
4,904
Main purpose
Business
4,523
1,149
4.3
3,355
3,069
779
Visit friends or relatives
9,519
3,430
3.1
5,780
1,771
638
Leisure
6,136
3,015
2.2
5,783
2,016
990
Other
3,538
1,785
2.2
1,951
1,149
580
Income
Less than $25,000
2,866
1006
2.1
1,843
1,925
676
$25,000 to $49,999
7,842
2,248
3.8
6,032
2,818
808
$50,000 or greater
13,007
2,508
5.7
8,993
3,768
726
Age
Less than 24
5,413
1,792
3.3
3,682
2,159
716
25 to 64
16,350
3,484
5.1
11,382
3,432
731
65 and over
1,953
494
4.3
1,805
3,839
971
Gender
Male
13,445
2,950
4.9
9,076
3,232
710
Female
10,270
2,795
4
7,792
2,929
797
PEOPLE WHO VISIT AREA
21,043
4,463
5.1
12,540
2,941
625
Means of transportation
Personal use vehicle
18,812
3,708
5.6
8,641
2,449
482
Commercial air
1,734
567
3.4
3,374
6,260
2,045
Intercity bus
14
6
2.3
10
1,529
732
Train
12
5
2.3
20
3,468
1,663
Other
470
176
3
485
2,896
1085
Main purpose
Business
5,064
1000
5.6
3,215
3,380
667
Visit friends or relatives
8,263
1,840
4.9
5,205
2,972
661
Leisure
4,029
1,278
3.5
2,022
1,662
527
Other
3,686
1004
4
2,087
2,186
595
Income
Less than $25,000
2,808
630
4.8
1,810
3,017
677
$25,000 to $49,999
6,940
1,526
4.9
3,844
2,646
582
$50,000 or greater
11,292
2,229
5.6
6,876
3,241
640
Age
Less than 24
5,183
1,208
4.7
2,917
2,535
591
25 to 64
14,412
2,837
5.6
8,720
3,229
635
65 and over
1,448
340
4.6
894
2,755
648
Gender
Male
11,661
2,308
5.6
7,025
3,198
633
Female
9,382
2,073
4.9
5,506
2,790
617
Travel Characteristics for Indiana and the United States: 2006 and 2016
Indiana
United States
2006
2016
Percent change
2006
2016
Percent change
People who live in area
Percent traveling households
73.8
86.1
16.7%
81.7
94.3
15.4%
Trips per household
5
5.3
6.0%
4.6
6.5
41.3%
Trips per traveling household
7
7.3
4.3%
6.5
8.2
26.2%
Trips per capita
2.5
4.6
84.0%
2.8
4.7
67.9%
Trips per traveler
2.7
4.9
81.5%
4.5
5.7
26.7%
Miles per traveler
1722.4
3,441
99.8%
3,218
4,649
44.5%
Miles per trip
478.9
807.7
68.7%
804.5
975.2
21.2%
People who visit area
Trips per traveler
4.3
5.8
34.9%
4.5
5.7
26.7%
Miles per traveler
2,226
3,300
48.2%
3,218
4,649
44.5%
Miles per trip
591.2
675.7
14.3%
804.5
975.2
21.2%
ase Problems Illustrating Travel Data for the Indiana Department of Tourism ayel industry consultant, you have been working for the state of Indiana Department presented. You will also include some information from a survey that your firm has evel 1- Tourism. The department manager has asked you to present a report on where Indiana ents travel to and where Indiana visitors come from. The modes of transportation must resi also dly completed. The travel survey is located in the workbook named Indiana.xlsx. Complete the following n the workbook named Indiana.xlsx in the Chapter 3 folder, and then save the Mo file as Indiana Travel.xlsx. 2. Create two bar charts to illustrate the differences in Trips per Traveler and Miles per Traveler of the average Indiana resident, the average U.S. resident, and the verage Indiana visitor. (Hint: Create the charts for Indiana and Indiana Visitors first, and then add the data for United States using the Select Data Source dialog box. (Hint: Delete the "- " value in the Series values box before selecting the United States data.) 3. Create a bar chart to illustrate a state-by-state comparison of the number of residents from the rest of the nation who visit Indiana, and the number of Indiana residents who visit other states. Rank the information in this chart by the states with the most visitors to Indiana . The Indiana Department of Tourism is very interested in learning more about how people travel in Indiana. They want to use this information to better target advertis- ing to specific demographic groups and methods of travel. Use the appropriate chart or series of charts to illustrate how people travel; the purpose of their travel; and their ncome levels, age, and gender. This information should be presented for people who ive within the state and those visiting the state with a comparison between the two groups ave and close the Indiana Travel.xlsx workbooExplanation / Answer
1. Select the Indiana and Indiana visitors data > Go to the Insert option and click on it > Click on Column > Select one of the simple 2D column. Then you can find:
Now using the same procedure by deleting Indiana Data you get:
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.