Australian Bureau of Statistics

Rate the ABS website
CensusAtSchool
ABS @ Facebook ABS @ Twitter ABS RSS ABS Email notification service
CensusAtSchool Australia
 
 
Education Services home page

Teacher Statistical Literacy

Back to 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:
LinkDescriptionDuration (mins)

General CensusAtSchool

Introducing CensusAtSchoolFormer 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 WorkbookInsert a new sheet into an Excel workbook.00:54

Simplify the DataCopy and paste data from an existing worksheet into a new worksheet.04:12


Working with Data in Microsoft Excel

Create a Summary TableFormat a table used to summarise data.


Show details for Video TranscriptVideo Transcript
01:55

COUNTIF FunctionUse COUNTIF to tally the frequency of a variable.


Show details for Video TranscriptVideo Transcript
02:20

Draw a GraphInsert and format a graph using categorical data.


Show details for Video TranscriptVideo Transcript
03:38

Using the Sort FunctionSort data according to topic and value.


Show details for Video TranscriptVideo Transcript
01:30

Mean and Median FunctionFind the mean and median of numerical data.


Show details for Video TranscriptVideo Transcript
04:31

Using CensusAtSchool in the ClassroomConstruct a scatterplot, remove outliers, add a trend line and remove outliers; and identify possible outliers using conditional formatting.

Hide details for Video TranscriptVideo 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


Using ABS Census Data Products

Introductory Video for Census TopicsIntroduction to the Census topics as well as what is not included in the Census product videos.


Show details for Video TranscriptVideo Transcript
00:33

Census Product - QuickStatsThis video demonstrates how you can search for key summary Census data using QuickStats.


Show details for Video TranscriptVideo Transcript
03:34

Census Product - Community ProfilesThis video demonstrates how to find Census data for your area of interest using Community Profiles.


Show details for Video TranscriptVideo Transcript
02:45

Census Product - Census TablesThis video demonstrates how to find detailed Census data for your area of interest and how to download a Census table.


Show details for Video TranscriptVideo Transcript
04:56


Key ABS Products for Teachers

Australian Social TrendsA description of some of the main features of the Australian Social Trends publication and where to find it on the ABS website.


Show details for Video TranscriptVideo Transcript
03:09

Measures of Australia's ProgressHow to find the Measures of Australia's Progress publication and a brief demonstration of key features.


Show details for Video TranscriptVideo Transcript
03:02

Yearbook AustraliaHow to find the Yearbook Australia series and a brief demonstration of key features.


Show details for Video TranscriptVideo Transcript
01:39

Consumer Price IndexHow to find Consumer Price Index (CPI) on the website and a demonstration of how to access CPI education resources from the ABS Education pages.


Show details for Video TranscriptVideo Transcript
02:41

Key National IndicatorsHow to find Key National Indicators on the ABS website and a demonstration of how to access source publications.


Show details for Video TranscriptVideo Transcript
02:25

National AccountsA demonstration of the National Accounts themes page and how to find quarterly and annual GDP data.


Show details for Video TranscriptVideo Transcript
02:46


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.