Excel and Inferential Statistics

Excel is a powerful software program in the world of inferential statistics. Later in the course we will use Excel to determine if significant relationships exist between two independent variables. For example you could use Excel to see if there is a relationship between how much food a country consumes and how many people have access to the internet. But first we will use Excel so we can use the appropriate tools in order to describe a set of data.

Hold down the Ctrl. key and click on the link below:

World Geography &Culture
log in using the following information:
username: foresthill
password: trillium

  1. Look for the Compare and Rank tool located on the right side of the screen
  1. Click on the Rank radio button
  1. Select a statistic that interests you and that you understand, and then click on Go
  1. Click on
  1. Select the Save as option, and then save the file as an Excel file with an appropriate name in your home drive
  1. Open your Excel file you just saved.

Below is an example of how your file will look (the example below is showing internet users).

  1. Colum A is not needed. To remove this column right click on and select Delete.

12. We also need to remove each country’s hyperlink. To do this right click on and select Remove Hyperlinks.

  1. In the example above you will notice the attribute values include comma separators. If this is the case for the data you selected you will need to remove these. Right click on and select Format Cells. In the format Cells window under Category select Number. Make sure the Use 1000 separator box is not checked and then click OK.
  1. The video link below is a tutorial showing how to calculate the mean, median and standard deviation.
  • Calculating Mean and Standard Deviation in Excel 2010

Note: As you watch the video focus on the following

  • Listen to the presenter’s explanation of mean and standard deviation as it will help you to gain a better understanding of thee terms
  • Read the content in the tan boxes on mean and standard deviation
  • Two methods for calculating mean are presented. I suggest using the second option because it is easier to complete
  • Once you have watched the method for calculating mean and standard deviation you will simply substitute the word median for average to calculate the median.
  • You can also use this method to find the lowest and highest values. Type min to get the lowest number and max to get the highest number. Once you have these two numbers simply calculate the range.

Assignment

In this activity you will present a short analysis to the class which describes your dataset.

The following is the format and requirements of the presentation

  1. You will begin by showing your dataset in the Excel file. Your dataset must:
  • Have a title in bold, be centered and the cells must be merged.
  • Each column must have a heading in bold, and units of measurement are required.
  • The cells containing the title and the column headings must be yellow
  • The data must be in alphabetical order.
  1. You must explain what the attribute measures (this is not the same as identifying what the attribute is).
  1. Below the dataset in column A the following must be typed in bold:
  • Mean
  • Median
  • Range
  • Standard Deviation
  1. In column B below the dataset you must insert the corresponding values, and this must be done using the same methods introduced above.
  1. You will describe the set of data without reading any of the values you calculated.