Manipulating U.S. Import Data

The U.S. import data by country need to be categorized into these 17 world regions.
Here is how this can be done electronically. You can always do it by hand, if this is easier for you.

Procedure:
1) Each country was given its regional number, using the following codes:

Code   Region     
1   North America      8   North Africa        15  Southeast Asia  
2   Central America    9   West Africa         16  East Asia 
3   South America      10  Central Africa     17  Oceania  
4   Northern Europe   11  South Africa
5   Eastern Europe     12  East Africa 
6   Western Europe   13  West Asia
7   Southern Europe  14  South Asia 
  
I have already categorized each country by its region and sorted the data. So, just copy the table below:
place the cursor at the top left-hand corner of the table, click and hold down while you drag all the way down to the bottom of the table; then copy the highlighted information.

Number       Country                  Dollars (millions) 
1	     Canada...................	110,922
2	     Antigua..................	15
2	     Aruba....................	457
2	     Bahamas, The.............	348
2	     Barbados ................	34
2	     Belize ..................	54
2	     Bermuda .................	15
2	     British Virgin Islands...	14
2	     Cayman Islands...........	35
2	     Costa Rica...............	1,542
2	     Cuba.....................	 (-)        
2	     Dominica.................	6
2	     Dominican Republic.......	2,672
2	     El Salvador..............	488
2	     Grenada..................	8
2	     Guatemala................	1,195
2	     Haiti....................	154
2	     Honduras.................	915
2	     Jamaica .................	720
2	     Martinique...............	1
2	     Mexico...................	39,930
2	     Montserrat...............	2
2	     Netherlands Antilles ....	397
2	     Nicaragua................	126
2	     Panama...................	281
2	     St. Christopher-Nevis....	24
2	     St. Lucia................  31	
2	     St. Pierre and Miquelon .	 (Z)      
2	     St. Vincent..............    5	
2	     Trinidad and Tobago .....	803
2	     Turks and Caicos Islands.	4
3	     Argentina................	1,206
3	     Bolivia..................	191
3	     Brazil...................	7,466
3	     Chile....................	1,462
3	     Colombia.................	3,030
3	     Ecuador..................	1,399
3	     Falkland Islands.........	 (Z)        
3	     French Guiana............	3
3	     Guadeloupe ..............	5
3	     Guyana ..................	91
3	     Paraguay.................	50
3	     Peru.....................	754
3	     Suriname ................	58
3	     Uruguay..................	266
3	     Venezuela................	8,140
4	     Czech Republic \5........	278
4	     Czechoslovakia...........	    (X)     
4	     Denmark .................	1,664
4	     Faeroe Island............	9
4	     Finland .................	1,609
4	     Greenland ...............	13
4	     Iceland .................	233
4	     Norway ..................	1,938
4	     Sweden ..................	4,532
5	     Albania..................	8
5	     Armenia \3...............	1
5	     Azerbaijan \3............	    (Z)
5	     Belarus \3...............	34
5	     Bosnia-Herzegovina \4....	7
5	     Bulgaria.................	159
5	     Croatia \4...............	106
5	     East Germany \6..........	    (X)      
5	     Estonia \3...............	20
5	     Georgia \3...............	21
5	     Hungary..................	401
5	     Latvia \3................	23
5	     Lithuania \3.............	16
5	     Macedonia \4.............	111
5	     Moldova..................	    (Z)     
5	     Poland...................	454
5	     Romania..................	69
5	     Russia \3................	1,744
5	     Slovakia \5..............	65
5	     Slovenia \3..............	230
5	     U.S.S.R	(former)......
5	     Ukraine \3...............	172
5	     Yugoslavia (former) \5...     (X)     
5	     Yugoslavia \5............	   (Z)      
6	     Austria .................	1,411
6	     Belgium  ................	5,177
6	     France ..................	15,244
6	     Germany \6...............	28,605
6	     Ireland .................	2,520
6	     Liechtenstein............	100
6	     Luxembourg...............	253
6	     Netherlands .............	5,451
6	     Switzerland .............	5,979
6	     United Kingdom ..........	21,736
7	     Andorra..................	   (Z)       
7	     Cyprus...................	16
7	     Gibraltar................	4
7	     Greece ..................	348
7	     Italy ...................	13,223
7	     Malta....................	104
7	     Monaco...................	16
7	     Portugal ................	790
7	     San Marino...............	   (Z)        
7	     Spain ...................	2,813
7	     Vatican City.............	1
8	     Algeria .................	1,583
8	     Egypt ...................	613
8	     Libya ...................	0
8	     Morocco..................	185
8	     Sudan....................	12
8	     Tunisia..................	41
9	     Benin....................	16
9	     Burkina..................	   (Z)      
9	     Cape Verde...............	   (Z)     
9	     Chad.....................	   (Z)    
9	     Gambia (The).............	9
9	     Ghana ...................	209
9	     Guinea...................	118
9	     Guinea-Bissau............	   (Z)     
9	     Ivory Coast .............	178
9	     Liberia .................	3
9	     Mali.....................	1
9	     Mauritania...............	7
9	     Niger....................	6
9	     Nigeria..................	5,301
9	     Senegal..................	8
9	     Sierra Leone.............	47
9	     Togo.....................	3
10	     Cameroon ................	101
10	     Central African Republic.  1	
10	     Congo (Brazzaville)......	500
10	     Equatorial Guinea........	4
10	     Gabon ...................	941
10	     Sao Tome and Principe....	1
11	     Angola ..................	2,090
11	     Botswana.................	9
11	     Burundi..................	3
11	     Lesotho..................	56
11	     Malawi...................	60
11	     Mozambique...............	9
11	     Namibia..................	22
11	     Rwanda...................	4
11	     South Africa.............	1,847
11	     Swaziland................	21
11	     Zaire....................	238
11	     Zambia...................	41
11	     Zimbabwe.................	111
12	     Comoros..................	10
12	     Djibouti.................	      (Z)       
12	     Eritrea..................	0
12	     Ethiopia ................	22
12	     Kenya ...................	92
12	     Madagascar ..............	43
12	     Mauritius................	197
12	     Reunion..................	      (Z)       
12	     Seychelles...............	4
12	     Somalia..................	      (Z)      
12	     Tanzania.................	11
12	     Uganda...................	10
13	     Afghanistan..............	3
13	     Bahrain..................	97
13	     Iran.....................	      (Z)       
13	     Iraq.....................	      (-)   
13	     Israel...................	4,426
13	     Jordan...................	19
13	     Kazakhstan \3............	39
13	     Kuwait...................	1,819
13	     Kyrgyzstan \3............	2
13	     Lebanon..................	27
13	     Oman.....................	277
13	     Qatar....................	65
13	     Saudi Arabia.............	7,710
13	     Syria....................	130
13	     Tajikistan \3............	18
13	     Turkey ..................	1,196
13	     Turkmenistan \3..........	2
13	     United Arab Emirates.....	727
13	     Uzbekistan \3............	7
13	     Yemen, Republic of ......	98
14	     Bangladesh...............	886
14	     Bhutan...................	      (Z)     
14	     India....................	4,551
14	     Maldives Islands.........	24
14	     Nepal....................	91
14	     Pakistan.................	898
14	     Sri Lanka ...............	1,002
15	     Brunei...................	30
15	     Burma (Myanmar)..........	46
15	     Cambodia.................	1
15	     French Polynesia.........	9
15	     Indonesia................	5,439
15	     Laos.....................	9
15	     Malaysia.................	10,568
15	     Papua New Guinea.........	98
15	     Philippines .............	4,896
15	     Singapore ...............	12,796
15	     Solomon Islands..........	3
15	     Thailand ................	8,542
15	     Vietnam..................	0
16	     China: People's republic.  31,535
16	     China: Taiwan............	25,105
16	     Hong Kong................	9,558
16	     Japan....................	107,268
16	     Macao....................	669
16	     Mongolia.................	34
16	     North Korea..............	0
16	     South Korea .............	17,123
17	     Australia ...............	3,294
17	     Cook Islands.............	1
17	     Fed States of Micronesia. 	14
17	     Fiji.....................	69
17	     Kiribati.................	2
17	     Marshall Islands.........	12
17	     New Caledonia............	23
17	     New Zealand .............	1,208
17	     Tokelau Islands..........	6
17	     Tonga....................	4
17	     Vanuatu..................	4
17	     Western Samoa............	1

Ignore the letters (e.g., Z) which are associated with some countries.

2) Open a new EXCEL file and under Edit, pick Special Paste, then select Text and click OK. Be sure that you have three separate columns. Verify this by clicking in the first cell of the pasted information and see what kinds of information it contains. It should look like this:

3) At the bottom of column C, calculate the total U.S. imports. Click on the first empty cell at the bottom and type =SUM(  and then click and drag from the bottom to the top all the numbers in column C and type ) until the complete formula looks like =SUM(C2:C32) -- where SUM adds numbers from cell C2 to C32.
4) In column D, add all the imports for EACH region. Only two regional totals are shown here:

5) Now create Table 3 U.S. Imports . . . , somewhere on the same worksheet. Like this:

6) Calculate the percentage for each region. Click on an empty cell next to one regional total and type =( and then click on the total dollar amount/and click on the grand total for all regions and type ). The formula should look something like this: =(G4/G$21)*100 -- where cell G4 is divided (/) by cell G21 (the $ assures that when you paste this formula into other cells, G21 will always be used) and multiplied (*) by 100.
If you want to check your answer, complete a form.

Now, use this classified information to construct Table 3 for Project 1.
Congratulations on your achievement. Be sure to use your new skills in other courses and for the rest of your life!?