Stat1250_prac_2 worked

docx

School

Macquarie University *

*We aren’t endorsed by this school

Course

1250

Subject

Statistics

Date

Apr 3, 2024

Type

docx

Pages

Uploaded by HighnessFlower10420

Week 2 Practical Welcome to STAT1250 Practical classes! These classes complement and extend the concepts you learn in lectures. Make sure to discuss your thoughts with the students in your class and ask questions to clarify your learning. Today you will begin to learn about how to use the computer package Excel. Practical participation is compulsory (it is a Hurdle requirement) and participation will be recorded during your class time. You must attend at least 10 of the 12 practicals or you will receive a fail in the unit. You must attend the practical that you are registered in according to e-student. It is your responsibility to complete the participation quiz during your practical. Each practical class will contain: revision of material covered in lectures and tutorials problem solving using Excel The data files that are used in these practicals will be made available on iLearn in the Data Sets section. When completing the practicals having your lecture notes with you is useful for reminding you of definitions and concepts. Today we will complete: opening a data file and editing data in Excel saving the work you have completed using EXCEL cutting and pasting from Excel to Word Hurdle Quiz 1 If you are not familiar with Excel at all there are many, many sites and YouTube videos that can help you. Type something like ‘Excel beginners’ in a browser then select from the options available. Recommended textbooks have resources for Excel. For example, recommended text book by Black et al (published by Wiley) has how to videos that you might benefit from watching. The add-in will usually be available on Lab computers already. More information on Analysis Tool Pack in Excel is at https://support.microsoft.com/en-us/office/load-the-analysis-toolpakin-excel-6a63e598- cd6d-42e3-9317-6b40ba1a66b4 1 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Introduction What will we cover today?

You need Excel with data analysis Tool to successfully complete STAT1250 (see iLearn for how to obtain a free copy for your computer/laptop from MQ). Remember: If you have difficulty with any parts of the Practicals ask your practical demonstrator for help during your class. Practicals can be completed on a lab computer or on your own computer/laptop, but not on tablets such as iPad, and certainly not on your smart phone. After you have logged onto the lab computer, find Excel and open it. Excel should appear with a blank worksheet as in the picture below. Excel stores data in columns labelled A,B,C, ........... and in rows numbered 1,2,3,…… Data are stored in cells that are referenced by their column label and row number. For example, the cell that is located in column C and in row 9 is referred to as cell C9. 2 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Opening Excel

At the top of an Excel spreadsheet are menus including • Home – which is generally used to format cells • Insert – which is used, for example, to add charts • Formulas – which is used to add appropriate formulas to the worksheet and so on. Some of these menus will be used in later Practicals. You are expected to be able to use Excel with data analysis and to be able to do your own analysis and interpret the Excel output that is presented in lectures, in the class tests and in the final examination. Excel most basic use is as a calculator. For example, in cell A1 type =123*456 and then press enter. The “=” specifies that we are using a function in this cell that we want an answer for. Did you get 56088? __________________ Excel can be used to add/subtract/multiply/divide (and many other functions) two or more columns together. Type the data in the image below into the same columns of your spreadsheet. In cell C1 type =SUM(A1:B1) . The SUM(A1:B1) command says add the cells specified (A1 and B1) together. Now we want to use the same function for cells C2 to C10. The easiest way to do this is to click in cell C1. At the bottom right hand corner of the cell you can see a little square. Hover over that square until the Excel curser becomes a thin +, using the left hand mouse button click and hold on the + and drag the curser 3 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University The Excel menus Excel as a calculator

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

down to C10. The formula should copy to cells C2 through C10 and give you the remaining row sums. Ask your demonstrator if you have any problems during your class time! You can also watch the pre-recorded Practical Video if you missed your class and submit your solution(s). Now we are going to import some data into Excel. Open an internet browser such as Google Chrome and log into iLearn (ilearn.mq.edu.au). Scroll down to the Data Sets section and you will see the names of the data sets that we will use this semester. Excel data files have the extension . xlsx or . xls . Double click on GAPMinder Data Sets.xlsx to download the file. Using Google Chrome, a link to the data file should appear in the lower left hand side of the browser window. To open the file you can either Click on the link at the bottom of the Chrome webpage to open the file OR Save the file to a directory, go to that directory and double click on the file name OR Having saved the file to a directory, from within Excel go to the menus at the top of the screen and click File > Open . Then navigate to the file location in the window that pops up, click on the file name and it will open. The variables in the GAPMinder Data Sets.xlsx file, _ Data_No_Missing _ tab are described below. Variable Name Description Country Country Name Country_id Country ID Region_id Region ID Region Geographical Region of the Country CO2_emissions_tonnes_per_perso n CO2 emissions, tonnes per person in 2018 Life_Expectancy_Years Life Expectancy in Years in 2018 birth_rate_per_1000 Birth rate births per 1000 in 2015 death_rate_per_1000 Death rate per 1000 in 2018 Population(with text) Population in 2018 from the original data with text 4 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Download the data from iLearn Opening an Excel data file The GAPMinder Data Sets.xlsx data

Population Population in 2018 as numbers corruption_perception_index_cpi Transparency International’s score of perceptions of corruption. Higher values indicate less corruption. 2017 data is included here. Corruption_Cat Low, Middle, High Corruption Categories Source: Various data files from GapMinder https://www.gapminder.org/data/ are merged. Examine the data in the spreadsheet and answer the following questions (hint: using sort function will be helpful to answer the following questions): Question Answer Which column of the Countries data Data_No_Missing worksheet contains the Region data? D Which country has the highest population? United States Which country has the lowest corruption perception index? Somalia Which country has the highest life expectancy? What is the life expectancy in years for that country? Japan, 84.8 years Which country has the highest birth rate per 1000? What is the birth rate per 1000? Which region is this country located? Niger, 49.2 per 1000 , Africa Which country has the lowest death rate per 1000? What is the death rate per 1000? Which region is this country located? Qatar, 1.2 per 1000, Middle East It can be useful to read information about individual countries, but what we really want to do is to summarise and display the data. Let’s begin by classifying the variables in the data set we have. Variable Variable Classification (circle the correct answer) Country numerical | categorical | none of these Country_id numerical | categorical | none of these Region_id numerical | categorical | none of these Region numerical | categorical | none of these CO2_emissions_tonnes_per_pers on numerical | categorical | none of these Life_Expectancy_Years numerical | categorical | none of these birth_rate_per_1000 numerical | categorical | none of these death_rate_per_1000 numerical | categorical | none of these Population(with text) numerical | categorical | none of these Population numerical | categorical | none of these corruption_perception_index_cpi numerical | categorical | none of these Corruption_Cat numerical | categorical | none of these The reason it is important to classify variables is because we do different things with different types of variables. For example, to display: • categorical variables we would draw bar charts or pie charts 5 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Summarising and displaying data

• numerical variables we would draw histograms or • to compare a numerical variable between groups of a categorical variable, boxplots would be used. We will see how to create these plots next week. Excel is useful for quickly summarising data by typing the appropriate commands or formulas. For example, suppose we were interested in knowing how many countries from Africa are included in the data set. If the worksheet was small, it would have been easy to count how many yourself – but counting yourself takes time and it is possible to make a mistake while counting. We don’t want to have to count by hand so we go to the cell where there is no data (i.e. N2) and type =COUNTIF(D2:D162, "Africa") then press enter. The COUNTIF command says – look at cells D2 to D162 and count the cell only if the cell says "Africa" . You can see that there are 49 countries from Africa in this data set. Try it yourself. Did you get 49? Yes Now double click in that cell to edit the command and change “Africa” to be “Central America”. How many countries were in Central America? 13 Now try using the COUNTIF command in the Corruption Category column to determine how many of the countries were in high corruption category. Answer: 41 Now suppose we were interested in the mean life expectancy for the all countries in the data set. In cell N3 (which is empty) type =AVERAGE(F2:F162) . Note that rather than typing F2:F162 you can select the column of cells of interest by clicking into cell F2, holding down the left mouse button down and selecting all of the cells that you are interested in. What is the average life expectancy for the all countries in the data set in two decimals? Answer: 72.88 Use the AVERAGE command again in cell N4 to determine the average CO2 emissions tonnes per person in two decimals. Answer 4.78 We might also be interested in the minimum and maximum birth rate per 1000. In cell N5 type =MIN(G2:G162) . The lowest birth rate per 1000 was 0 . This is possibly wrong, possibly the data is missing for that country . Similarly to find the highest birth rate per 1000 type =MAX(G2:G162) into cell N6. The highest birth rate per 1000 was 49.2 . Sorting the data Sometimes we need to rearrange the data so that the data are grouped by a feature of interest. Let us sort the data by Region . Highlight all of the data A1:L162. While on the Home tab find Sort (see screen shot on the right) and choose Custom Sort . Choose “Region” from the list on the box next to “Sort by”. You can also choose the order (either A to Z or Z to A). Then click OK. The data will now be sorted by Region . This will be useful later in the unit when we need to compare groups. How many countries are in Ocenia? 4 6 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Excel Formulas

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help

More on Excel later! While using these formulas is quite easy and can be the most efficient way of using Excel when there are many rows or columns in Excel that need the same formula applied to it there are easier ways to get a several summary statistics all at once. We will see some of those ways in coming weeks! Now save your spreadsheet if you completed this exercise in the university lab, copy it to a memory stick or email it to yourself so that you have a record of what you did. You should save your work for each week, that will help you to keep a record of what you have learnt throughout the semester. Now that you know how to use Excel as a calculator, use it to complete Hurdle Quiz 1 . Hurdle quizzes must be completed to pass the unit. If you do not complete and pass a Hurdle Quiz by the due date you will fail the unit. Open Hurdle Quiz 1 and look at the information for it. How many attempts are you allowed at this Hurdle Quiz? 10 What is the pass mark required for this quiz? 80% When is the quiz due? Now complete the quiz. Hints: =SQRT(cell reference) gives the square root. =(cell reference)^2 will square your number. You can use your calculator to complete the quiz also. What mark did you get in the Hurdle quiz? 100% Did you pass? Yes If you didn’t pass (i.e. get a mark equal to or higher than the required pass mark then you need to attempt the quiz again. The hurdle quizzes are designed to help you learn the material required in the unit, but Hurdle Quiz 1 tests your existing knowledge. If you cannot get full marks in this test you should go to the Numeracy Centre or talk to one of the lecturers for this unit in their office hours. 7 | Stat1250 Week 2 Practical: Introduction to Excel © Copyright Macquarie University Complete Hurdle Quiz 1

Stat1250_prac_2 worked

Related Documents