COUNTIFS function

This Online Tutorial gives you more practice in descriptive data analysis, creating graphs, and interpreting your results.  You will also have a go at finding National data through the ABS website to compare with the Health & Society health survey data.  

This week we will build on what you have learnt over the last few tutorials in undertaking descriptive analyses of health data, using the data from the 2016 Health & Society Student Health Survey. 

You will also find and access data from the most recent National Health Survey and Aboriginal and Torres Strait Islander Health Survey to compare with your Health and Society data, and you'll spend some time writing up a brief interpretation of your analyses and graphs.

On completion of this tutorial, you should be able to:

On completion of this tutorial, you should be able to:

1:  Confidently navigate data sets in Excel;

2:  Confidently calculate counts and prevalence (%) in Excel;

3:  Create and format tables in Excel to summarise data;

4:  Find and use relevant national data on the Australian Bureau of Statistics Website;

5:  Create a table and a graph comparing 2014 Health & Society Health Survey data with national data; and  

6:  Write a summary comparing the Health & Society with national data presented in the graph.

1:  Right click on the file below to save a copy of the dataset for Part 2 of this module containing data from the 2016 Health and Society Health Survey in Excel format.  You should select the "save as ..." option, and save the file to your flash drive:

2016 Health and Society Student Health Survey data

2:  Open your Excel dataset (workbook)

The workbook has several worksheets, including "data", "metadata", "calculations", "summary table", and "graph".  

Your "calculations" worksheet is blank - the tables you will need to enter your calculations have not been pre-formatted for you.  This will give you more practice in creating/formatting your own tables in Excel.  

1.  In the three worksheets containing data, identify the variable that represents cardiovascular (heart or circulatory) conditions (HINT: Use your "metadata" sheet to help you, if needed). 

2.  In your "calculations" worksheet, calculate the count and prevalence (%) of cardiovascular conditions for males and females separately, and for the total cohort.  

To do this, you will need to use the COUNTIFS formula - this is an extension of the COUNTIF formula used in Part 1 of this module.  The COUNTIFS formula allows the calculation of counts where multiple conditions are met (e.g., having a cardiovascular condition AND being male).

The formula structure is as follows:  

=countifs(range1,criteria1,range2,criteria2)

e.g., we want to calculate the number of males who have reported having a cardiovascular condition.  To do this, we need to count the number of participants meeting both the criteria for being male (range = sex variable column, criteria = 1 (male)) and having a cardiovascular condition (range = cvcondition variable column, criteria = 1 (yes)):

=COUNTIFS(C2:C307,1,AX2:AX307,1)

3.  Create a summary table in your "summary table" worksheet, and enter the count and prevalence (%) of cardiovascular conditions for men, women and the total cohort, as well as the total count of 2016 Health and Society Student Health Survey participants.

Create a bar graph from the summary table you have just made for cardiovascular conditions data, to compare the prevalence of cardiovascular conditions among men, women, and the total cohort.

• • • You should end up with a graph with three bars (males, females and total persons).
    • Make sure you have a graph title, axis titles and labels and a key (legend).
    • Have a go at making the graph more visually appealing by changing colours and fonts.

Go to Part 3: Derived variables