|Introducing CensusAtSchool||Former Australian Statistician, Dennis Trewin, talks about the aims of CensusAtSchool and the structure of the project.||02:12|
|Tailoring the Data to Suit Your Needs|
|Making Multiple Worksheets in a Workbook||Insert a new sheet into an Excel workbook.||00:54|
|Simplify the Data||Copy and paste data from an existing worksheet into a new worksheet.||04:12|
|Working with Data in Microsoft Excel|
|Create a Summary Table||Format a table used to summarise data.|
|COUNTIF Function||Use COUNTIF to tally the frequency of a variable.|
|Draw a Graph||Insert and format a graph using categorical data.|
|Using the Sort Function||Sort data according to topic and value.|
|Mean and Median Function||Find the mean and median of numerical data.|
|Using CensusAtSchool in the Classroom||Construct a scatterplot, remove outliers, add a trend line and remove outliers; and identify possible outliers using conditional formatting.
Using CensusAtSchool in the Classroom
An effective strategy for using CensusAtSchool data in the primary classroom is to get a class set of results and then use either the Random Sampler or the datasets and information tables to compare your students to other students in your state or in other parts of Australia. To gain a class set of information, you can download a copy of the questionnaire and get your students to responses to a particular question or series of questions.
CensusAtSchool data allows for explorations of various mathematical concepts and ideas in the secondary classroom. One of these is the golden ratio. Using the data from the questions pertaining to the students height and bellybutton height, questions such as 'can you estimate a persons height from their bellybutton height?' can be explored in the classroom.
The first step is to establish if a relationship exists between height and bellybutton height. If a relationship does exist we can estimate a persons height if we know their bellybutton height.
We could draw a xy scatter plot of height vs bellybutton height. I'd like the height to be the y-axis or dependant variable. Excel draws the first column as the x-axis or independent variable. This means I have to have the bellybutton height first. Click on the worksheet xy scatter. I'll cut the height from column A, and paste it into column C. To draw the graph, select the data by clicking on the header 'Bellybutton height.' Hold the button down and drag across to include height and then all the way down to the last data item.
Click on the 'insert' menu and select 'chart.' Click on 'xy scatter' and press next. Press next. We can now enter a title and label the axis. Height vs Bellybutton height for students is the title. The x-axis is bellybutton cm. And the y-axis is height in cm. Press next. We want the graph to appear on the existing sheet so press finish.
Our scatter plot is to small to be conclusive that a relationship exists but it looks promising. Notice the outliers; this person is very tall and has a bellybutton height that's greater than their height. This can't happen so we should remove the data. Notice that the graph changes immediately when the data is removed. We could proceed to remove other impossible pieces of data. We could continue to identify such outliers.
Let's look at another way of seeing if a relationship exists between height and bellybutton height. We could use the ratio of the height to the bellybutton height. Enter a heading in D2, Height divided by bellybutton height. In D3 enter a formula to calculate the ratio. Start with an equal sign, height divided by, which is a forward slash, bellybutton height. Copy the formula down the table by moving the cursor to the bottom corner of the cell until it becomes a small solid black cross and double click. This will copy to the end of the data. If a relationship exists we would expect the ratio to be constant. There seems to be quite a range of values, although, at 1.6 is very common. Logically, our bellybutton is just over half our height so it is reasonable to expect that the ratio will be less than 2. Any ratios greater than 2 are not possible and they can be removed.
Notice that the graph changes immediately when the data is removed. Also, if the ratio is 1 it means that the height and bellybutton height are the same. If the ratio is less than 1 it means that the bellybutton height is greater than the height. Clearly not possible. So any ratios that are less than 1 can be deleted. We could continue to search through the ratios to remove those that are less than 1 or greater than 2. We are using ratio to objectively remove outliers. We are working mathematically, however, it is still a laborious process. One way to make this simpler is by using conditional formatting.
Select the ratio column by clicking on the letter D at the top of the column. Click on the 'format' menu and select 'conditional formatting.' We can identify the ratios between 1 and 2. Click on format and colour them red. Click OK. Click OK again. You can easily see that the values within the range that we wish to keep are now red. The black values are outside our range and are easily identified for deletion.
Now let's return to our scatter plot. There are still some outliers we could remove. There now appears to be a clear trend. It would be good to zoom into the area that most of the points are. One way to do this is to change the scale on the axis. For the x-axis, the bellybutton height, we could start at 75 and go to 130. Move the cursor over the x-axis and from the 'format' menu select 'select axis' and then select 'scale' and change the minimum value to 75 and the maximum to 130. Click OK.
We could change the scale on the y-axis, the height, from 130 through to 190. Move the cursor over the y-axis and from the 'format' menu select 'select axis' and then select 'scale' and change the minimum value to 130 and the maximum to 190. Click OK. There's a definite trend here. We can even add an equation from the 'options' menu. Click on any point, go to the 'chart' menu and trend line, select 'chart options' and check the display equation on chart box. Click OK.
Is there a relationship between height and bellybutton height? I'd say yes. A closer look at the remaining outer points may improve the relationship ever further. We could use this graph to make a reasonable estimate of the height of a person if we knew their bellybutton height. We really are working mathematically when the ratio of the height and bellybutton height is used to determine if a point is an outlier.
To finish off our investigation, we could now find the average ratio. Remember the insert function process. For real data entered by students, that is very close to the golden ratio. Notice that you can see a trend on the graph. However, it is harder to see a trend with the ratio.
|Using ABS Census Data Products|
|Introductory Video for Census Topics||Introduction to the Census topics as well as what is not included in the Census product videos.|
|Census Product - QuickStats||This video demonstrates how you can search for key summary Census data using QuickStats.|
|Census Product - Community Profiles||This video demonstrates how to find Census data for your area of interest using Community Profiles.|
|Census Product - Census Tables||This video demonstrates how to find detailed Census data for your area of interest and how to download a Census table.|
|Key ABS Products for Teachers|
|Australian Social Trends||A description of some of the main features of the Australian Social Trends publication and where to find it on the ABS website.|
|Measures of Australia's Progress||How to find the Measures of Australia's Progress publication and a brief demonstration of key features.|
|Yearbook Australia||How to find the Yearbook Australia series and a brief demonstration of key features.|
|Consumer Price Index||How to find Consumer Price Index (CPI) on the website and a demonstration of how to access CPI education resources from the ABS Education pages.|
|Key National Indicators||How to find Key National Indicators on the ABS website and a demonstration of how to access source publications.|
|National Accounts||A demonstration of the National Accounts themes page and how to find quarterly and annual GDP data.|