# Australian Bureau of Statistics

 CensusAtSchool #search{vertical-align:-3.5px; }
 CensusAtSchool Australia

Education Services home page

 ABS Video Tutorials

These video tutorials will familiarise you with a range of ABS products that are useful in the classroom. You can view video transcripts by clicking on the triangle labelled 'Video Transcripts' below the video description.

On this page:
 Link Description Duration (mins) General CensusAtSchool Introducing CensusAtSchool Former Australian Statistician, Dennis Trewin, talks about the aims of CensusAtSchool and the structure of the project. 02:12 Back to top 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 Back to top Working with Data in Microsoft Excel Create a Summary Table Format a table used to summarise data. Video Transcript 01:55 COUNTIF Function Use COUNTIF to tally the frequency of a variable. Video Transcript 02:20 Draw a Graph Insert and format a graph using categorical data. Video Transcript 03:38 Using the Sort Function Sort data according to topic and value. Video Transcript 01:30 Mean and Median Function Find the mean and median of numerical data. Video Transcript 04:31 Using CensusAtSchool in the Classroom Construct a scatterplot, remove outliers, add a trend line and remove outliers; and identify possible outliers using conditional formatting. Video Transcript 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. 09:14 Back to top 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. Video Transcript 00:33 Census Product - QuickStats This video demonstrates how you can search for key summary Census data using QuickStats. Video Transcript 03:34 Census Product - Community Profiles This video demonstrates how to find Census data for your area of interest using Community Profiles. Video Transcript 02:45 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. Video Transcript 04:56 Back to top 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. Video Transcript 03:09 Measures of Australia's Progress How to find the Measures of Australia's Progress publication and a brief demonstration of key features. Video Transcript 03:02 Yearbook Australia How to find the Yearbook Australia series and a brief demonstration of key features. Video Transcript 01:39 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. Video Transcript 02:41 Key National Indicators How to find Key National Indicators on the ABS website and a demonstration of how to access source publications. Video Transcript 02:25 National Accounts A demonstration of the National Accounts themes page and how to find quarterly and annual GDP data. Video Transcript 02:46

 Return to Top
 Privacy | Disclaimer | Feedback | | © Copyright| Sitemap| Online Security

© Commonwealth of Australia 2008

Unless otherwise noted, content on this website is licensed under a Creative Commons Attribution 2.5 Australia Licence together with any terms, conditions and exclusions as set out in the website Copyright notice. For permission to do anything beyond the scope of this licence and copyright terms contact us.