| Asia | Afghanistan | 700 |
| Europe | Albania | 4400 |
| Africa | Algeria | 5700 |
| Oceania | American Samoa | 7400 |
| Africa | Angola | 6600 |
| The Americas | Anguilla | 12500 |
| The Americas | Antigua and Barbuda | 13500 |
| The Americas | Argentina | 11300 |
| Asia | Armenia | 3400 |
| The Americas | Aruba | 23400 |
| Oceania | Australia | 65100 |
| Europe | Austria | 49100 |
| Asia | Azerbaijan | 7900 |
| The Americas | Bahamas | 26200 |
| Middle East | Bahrain | 22100 |
| Asia | Bangladesh | 900 |
| The Americas | Barbados | 14700 |
| Europe | Belarus | 7500 |
| Europe | Belgium | 45600 |
| The Americas | Belize | 4900 |
| Africa | Benin | 800 |
| Asia | Bhutan | 2900 |
| The Americas | Bolivia | 2800 |
| Europe | Bosnia and Herzegovina | 4800 |
| Africa | Botswana | 7300 |
| The Americas | Brazil | 11100 |
| Asia | Brunei | 39800 |
| Europe | Bulgaria | 7300 |
| Africa | Burkina Faso | 700 |
| Africa | Burundi | 300 |
| Asia | Cambodia | 1000 |
| Africa | Cameroon | 1200 |
| The Americas | Canada | 52100 |
| Africa | Cape Verde | 3700 |
| The Americas | Cayman Islands | 47000 |
| Africa | Central African Republic | 400 |
| Africa | Chad | 1200 |
| The Americas | Chile | 15700 |
| Asia | China | 6900 |
| The Americas | Colombia | 8100 |
| Africa | Comoros | 900 |
| Africa | Congo, Democratic Republic of the | 300 |
| Africa | Congo, Republic of the | 3100 |
| The Americas | Costa Rica | 10300 |
| Africa | Côte d'Ivoire | 1300 |
| Europe | Croatia | 13400 |
| The Americas | Cuba | 6500 |
| The Americas | Curaçao | 36800 |
| Europe | Cyprus | 25200 |
| Europe | Denmark | 58300 |
| Africa | Djibouti | 1800 |
| The Americas | Dominica | 6700 |
| The Americas | Dominican Republic | 5800 |
| Asia | East Timor | 5200 |
| The Americas | Ecuador | 5900 |
| Africa | Egypt | 3100 |
| The Americas | El Salvador | 4000 |
| Africa | Equatorial Guinea | 24200 |
| Africa | Eritrea | 500 |
| Europe | Estonia | 18300 |
| Africa | Ethiopia | 400 |
| Europe | European Union | 34000 |
| The Americas | Falkland Islands | 55400 |
| Europe | Faroe Islands | 45300 |
| Oceania | Fiji | 4700 |
| Europe | Finland | 49200 |
| Europe | France | 41700 |
| Oceania | French Polynesia | 20600 |
| Africa | Gabon | 12200 |
| Africa | Gambia, The | 500 |
| Asia | Georgia | 3600 |
| Europe | Germany | 44300 |
| Africa | Ghana | 1800 |
| Europe | Gibraltar | 38800 |
| Europe | Greece | 21800 |
| The Americas | Greenland | 37500 |
| The Americas | Grenada | 7400 |
| Oceania | Guam | 28700 |
| The Americas | Guatemala | 3700 |
| Europe | Guernsey | 43400 |
| Africa | Guinea | 600 |
| Africa | Guinea-Bissau | 500 |
| The Americas | Guyana | 5800 |
| The Americas | Haiti | 800 |
| The Americas | Honduras | 2200 |
| Asia | Hong Kong | 38100 |
| Europe | Hungary | 13100 |
| Europe | Iceland | 46200 |
| Asia | India | 1500 |
| Asia | Indonesia | 3500 |
| Middle East | Iran | 5200 |
| Middle East | Iraq | 6600 |
| Europe | Ireland | 46000 |
| Europe | Isle of Man | 47800 |
| Middle East | Israel | 35400 |
| Europe | Italy | 33600 |
| The Americas | Jamaica | 4900 |
| Asia | Japan | 38500 |
| Europe | Jersey | 57600 |
| Middle East | Jordan | 5300 |
| Asia | Kazakhstan | 12700 |
| Africa | Kenya | 1000 |
| Oceania | Kiribati | 1700 |
| Asia | Korea, North | 1200 |
| Asia | Korea, South | 24400 |
| Europe | Kosovo | 3800 |
| Middle East | Kuwait | 66500 |
| Asia | Kyrgyzstan | 1300 |
| Asia | Laos | 1500 |
| Europe | Latvia | 15400 |
| Middle East | Lebanon | 10500 |
| Africa | Lesotho | 1300 |
| Africa | Liberia | 400 |
| Africa | Libya | 11800 |
| Europe | Liechtenstein | 139300 |
| Europe | Lithuania | 15300 |
| Europe | Luxembourg | 110700 |
| Asia | Macau | 87300 |
| Europe | Macedonia, Republic of | 5000 |
| Africa | Madagascar | 400 |
| Africa | Malawi | 300 |
| Asia | Malaysia | 10500 |
| Asia | Maldives | 5800 |
| Africa | Mali | 700 |
| Europe | Malta | 22600 |
| Oceania | Marshall Islands | 2800 |
| Africa | Mauritania | 1200 |
| Africa | Mauritius | 9000 |
| The Americas | Mexico | 10600 |
| Oceania | Micronesia, Federated States of | 3200 |
| Europe | Moldova | 2200 |
| Europe | Monaco | 159400 |
| Asia | Mongolia | 3900 |
| Europe | Montenegro | 6800 |
| Africa | Morocco | 3200 |
| Africa | Mozambique | 600 |
| Africa | Namibia | 5600 |
| Asia | Nepal | 600 |
| Europe | Netherlands | 47600 |
| Oceania | New Caledonia | 35700 |
| Oceania | New Zealand | 40600 |
| The Americas | Nicaragua | 1900 |
| Africa | Niger | 400 |
| Africa | Nigeria | 1700 |
| Europe | Norway | 101400 |
| Middle East | Oman | 26000 |
| Asia | Pakistan | 1300 |
| Middle East | Palestine | 1700 |
| The Americas | Panama | 11400 |
| Oceania | Papua New Guinea | 2500 |
| The Americas | Paraguay | 4600 |
| The Americas | Peru | 6700 |
| Asia | Philippines | 2600 |
| Europe | Poland | 13300 |
| Europe | Portugal | 21000 |
| The Americas | Puerto Rico | 23500 |
| Middle East | Qatar | 104300 |
| Europe | Romania | 8900 |
| Europe | Russia | 14600 |
| Africa | Rwanda | 600 |
| The Americas | Saint Kitts and Nevis | 15000 |
| The Americas | Saint Lucia | 8400 |
| The Americas | Saint Vincent and the Grenadines | 7100 |
| Oceania | Samoa | 3600 |
| Europe | San Marino | 57900 |
| Africa | São Tomé and Príncipe | 1700 |
| Middle East | Saudi Arabia | 24800 |
| Africa | Senegal | 1200 |
| Europe | Serbia | 6000 |
| Africa | Seychelles | 14000 |
| Africa | Sierra Leone | 800 |
| Asia | Singapore | 54700 |
| The Americas | Sint Maarten | 21900 |
| Europe | Slovakia | 17700 |
| Europe | Slovenia | 22700 |
| Oceania | Solomon Islands | 1800 |
| Africa | Somalia | 600 |
| Africa | South Africa | 6600 |
| Africa | South Sudan | 1000 |
| Europe | Spain | 28600 |
| Asia | Sri Lanka | 3000 |
| Africa | Sudan | 1500 |
| The Americas | Suriname | 8800 |
| Africa | Swaziland | 2700 |
| Europe | Sweden | 57200 |
| Europe | Switzerland | 80800 |
| Middle East | Syria | 2900 |
| Asia | Taiwan | 20900 |
| Asia | Tajikistan | 1000 |
| Africa | Tanzania | 700 |
| Asia | Thailand | 5600 |
| Africa | Togo | 500 |
| Oceania | Tonga | 4500 |
| The Americas | Trinidad and Tobago | 22000 |
| Africa | Tunisia | 4400 |
| Europe | Turkey | 10700 |
| Asia | Turkmenistan | 7800 |
| Africa | Uganda | 500 |
| Europe | Ukraine | 3800 |
| Middle East | United Arab Emirates | 70900 |
| Europe | United Kingdom | 39600 |
| The Americas | United States | 52800 |
| The Americas | Uruguay | 15800 |
| Asia | Uzbekistan | 1900 |
| Oceania | Vanuatu | 3100 |
| The Americas | Venezuela | 12900 |
| Asia | Vietnam | 1900 |
| The Americas | Virgin Islands, British | 38900 |
| Middle East | Yemen | 1700 |
| Africa | Zambia | 1600 |
| Africa | Zimbabwe | 800 |
Data Set for Class 3
Per Capita by Country, as per Wikipedia
Assignment 2 of 3 and Solutions
Now it's time to put the concepts learned to use. Using data provided in Assignment 2 Data Set, try to solve the following tasks:
Below are the solutions... Now don't cheat... Try to solve first and then use the solutions to help you understand.
Below are the solutions... Now don't cheat... Try to solve first and then use the solutions to help you understand.
Class 2 of 3: Text and Lookup Functions
Here are the formulas that we looked at during class 2 of 3:
TEXT FUNCTIONS
The text functions are some of the most used in Excel when you're working with lists and database extractions. Whenever you extract data from a Database, more often than not it needs to be cleaned up. This is where the following text functions come to our help. Be aware however, these are not the comprehensive list of them. There are many other ones and I encourage you to explore the Function Wizard to learn other ones:
LOOKUP FUNCTIONS
The lookup functions are crucial to Excel. Without these the amount of manual work would be huge. Anyone who considers themselves a serious Excel student, must learn these. It is for this reason that I decided to cover them. They are not the easiest to use, but with practice, anyone can learn them.
The VLOOKUP function is the first one most people learn to use. I call it the basic lookup function because it's somewhat easy to understand and use. It definitely has its place among the heavy lifters in Excel; my issues with it is that it requires setup and if you don't follow it, it won't work. Here is its syntax and an example.
The INDEX-MATCH function is the more advanced and elegant of the lookup functions in my opinion. It's not perfect, because it also requires that the data be cleaned up of leading and trailing spaces for it to work. But outside of that, I use it almost exclusively. It's not the most intuitive of functions, but with practice, you'll get the hang of it. Here is its syntax and a example for you to use.
I know it was a lot to cover, but I feel it was necessary. Now, you must practice your skill. Use the data I've provided and look for the assignment and its solution to practice. Keep Excelling!
TEXT FUNCTIONS
The text functions are some of the most used in Excel when you're working with lists and database extractions. Whenever you extract data from a Database, more often than not it needs to be cleaned up. This is where the following text functions come to our help. Be aware however, these are not the comprehensive list of them. There are many other ones and I encourage you to explore the Function Wizard to learn other ones:
LOOKUP FUNCTIONS
The lookup functions are crucial to Excel. Without these the amount of manual work would be huge. Anyone who considers themselves a serious Excel student, must learn these. It is for this reason that I decided to cover them. They are not the easiest to use, but with practice, anyone can learn them.
The VLOOKUP function is the first one most people learn to use. I call it the basic lookup function because it's somewhat easy to understand and use. It definitely has its place among the heavy lifters in Excel; my issues with it is that it requires setup and if you don't follow it, it won't work. Here is its syntax and an example.
The INDEX-MATCH function is the more advanced and elegant of the lookup functions in my opinion. It's not perfect, because it also requires that the data be cleaned up of leading and trailing spaces for it to work. But outside of that, I use it almost exclusively. It's not the most intuitive of functions, but with practice, you'll get the hang of it. Here is its syntax and a example for you to use.
I know it was a lot to cover, but I feel it was necessary. Now, you must practice your skill. Use the data I've provided and look for the assignment and its solution to practice. Keep Excelling!
Assignment 2 Data Set
| Rep | Item | Units |
| Jardine | Binder | 94 |
| Thompson | Binder | 57 |
| Andrews | Binder | 28 |
| Gill | Binder | 46 |
| Gill | Binder | 80 |
| Howard | Binder | 29 |
| Jardine | Binder | 11 |
| Jones | Binder | 60 |
| Jones | Binder | 4 |
| Parent | Binder | 81 |
| Smith | Binder | 87 |
| Sorvino | Binder | 7 |
| Jones | Binder | 60 |
| Morgan | Binder | 28 |
| Kivell | Binder | 50 |
| Kivell | Desk | 5 |
| Smith | Desk | 2 |
| Sorvino | Desk | 3 |
| Gill | Pen | 27 |
| Howard | Pen | 96 |
| Jones | Pen | 64 |
| Parent | Pen | 15 |
| Sorvino | Pen | 76 |
| Jardine | Pen Set | 50 |
| Jones | Pen Set | 16 |
| Jones | Pen Set | 62 |
| Kivell | Pen Set | 42 |
| Morgan | Pen Set | 55 |
| Parent | Pen Set | 74 |
| Kivell | Pen Set | 96 |
| Andrews | Pencil | 75 |
| Andrews | Pencil | 66 |
| Andrews | Pencil | 14 |
| Gill | Pencil | 53 |
| Gill | Pencil | 7 |
| Jardine | Pencil | 36 |
| Jardine | Pencil | 90 |
| Sorvino | Pencil | 56 |
| Thompson | Pencil | 32 |
| Jones | Pencil | 35 |
| Jones | Pencil | 95 |
| Morgan | Pencil | 90 |
| Smith | Pencil | 67 |
REFERENCE TABLE 1
| Employee ID | Name |
| 530 | Andrews |
| 360 | Gill |
| 408 | Howard |
| 935 | Jardine |
| 989 | Jones |
| 505 | Kivell |
| 913 | Morgan |
| 869 | Parent |
| 519 | Smith |
| 522 | Sorvino |
| 752 | Thompson |
REFERENCE TABLE 2
| Product | Price |
| Binder | 1.99 |
| Desk | 125 |
| Pen | 1.99 |
| Pen Set | 4.99 |
| Pencil | 1.29 |
Class 2 Dataset 1
We will be using this data set for one of the exercises in class.
| Ali Small | 2 |
| Arden Stone | 3 |
| Ariana Tucker | 1 |
| Bethany Howell | 2 |
| Constance Maxwell | 3 |
| Damon Kidd | 4 |
| Doris Simmons | 3 |
| Drake Middleton | 4 |
| Emily Gamble | 2 |
| Gary Myers | 4 |
| Gay Donaldson | 2 |
| Gay Schneider | 3 |
| Geoffrey Logan | 3 |
| Germaine Richardson | 4 |
| Gil Daniel | 3 |
| Ginger Mckay | 3 |
| Hedley Clark | 1 |
| Heidi Guzman | 3 |
| Ian Horn | 3 |
| India Sharpe | 4 |
| Jasper Barrera | 4 |
| Karly Stokes | 4 |
| Keaton Garcia | 2 |
| Keelie Hooper | 3 |
| Lilah Shields | 4 |
| Lillith Cleveland | 4 |
| Lucian Lopez | 4 |
| Lyle Kim | 3 |
| Mia Pitts | 4 |
| Ora Glass | 3 |
| Quinn Wolfe | 2 |
| Quyn Lyons | 3 |
| Reece Christian | 4 |
| Reece Holloway | 2 |
| Rhiannon Mccarty | 1 |
| Sarah Leonard | 3 |
| Serena Watkins | 2 |
| Shana Byers | 4 |
| Shay Carlson | 4 |
| Sonia Beach | 3 |
| Trevor Bray | 4 |
| Ulysses Mann | 3 |
| Ulysses Patterson | 4 |
| Vance Whitney | 4 |
| Vincent Berry | 4 |
| Wyatt Dalton | 1 |
| Yeo Hewitt | 1 |
Assignment #1 Solutions
Here are the solutions:
Now, the formulas don't have to be in the exact sequence, you could have the > symbol < and the "Less" before the "Greater. But the answers remain.
Best to you and see you!
Now, the formulas don't have to be in the exact sequence, you could have the > symbol < and the "Less" before the "Greater. But the answers remain.
Best to you and see you!
Assignment #1
For each of the 4 cases, write the corresponding formula.
I will post the solutions on Sunday... but try to do it before then.
Subscribe to:
Posts (Atom)