Previous step: Phase 2 Excel data capture »
Leave the formatting to the end!
Once you have captured your questionnaire data in Microsoft Excel (or downloaded your data from a digital Google Form questionnaire), you must analyze the data. Your analysis must demonstrate your ability to use a range of functions varying in complexity. One of the biggest problems learners face at this stage is finding out that they didn’t collect appropriate data with their questionnaires!
Do NOT use Excel’s Insert » Table or Format as Table commands!
In this post:
See also:
1. First steps
Once you have captured all your raw data, duplicate the raw Data worksheet. Rename this copy “Analysis“. On this sheet, you will perform all the required calculations and formatting.
Do you have some “test data” responses from your Google Form? Keep them on your Raw worksheet as evidence of your testing!
Top tips:
- Did you collect a name and a surname? If you did and you entered both name and surname in one Excel column, we need to fix that on our Data worksheet before we copy it for analysis. Check the Level 4 section below to see how this can be done, scoring you extra cred at the same time by knocking off some of the more difficult analyses!
- Did you capture two pieces of data in one column? Head on over to the post on Excel’s Text to Columns command.
- Don’t forget to use absolute references where required (examples: COUNTIF, COUNTIFS & VLOOKUP functions) — throw in a named cell or a named range while you are at it!
- You must select your functions’ criteria correctly.
- Attempt to use all the Excel functions you know.
2. Grade 12 Levels
| LEVEL | EXAMPLES OF FORMULAS/FUNCTIONS |
|---|---|
| ① | Simple functions that only use/include a single cell range, e.g. SUM, MAX, MIN, COUNT, AVERAGE, MODE, MEDIAN, LEN, VALUE OR a formula using any of the arithmetic operators ( +, –, *, / ) |
| ② | Functions that include a cell range and one other parameter/condition, e.g. ROUND, LARGE, SMALL, LEFT, RIGHT, CONCATENATE, COUNTIF, SUMIF OR Calculations using a combination of arithmetic operators and brackets OR Calculations using a combination of any two simple functions from level ① |
| ③ | Functions that include a cell range plus two parameters/conditions, e.g. POWER, MID, FIND, COUNTIFS, ROUNDUP, RANDBETWEEN OR a simple IF function or any of the DATE and TIME functions OR Any combination of more than two functions of arithmetic operators, brackets and other functions |
| ④ | A LOOKUP OR nested IF-function OR functions not in the CAT CAPS curriculum |
Use “labels” to describe your information.
2.1 Level 1
This analysis is pretty straightforward. You can use a variety of these functions on the age data, for instance, calculating the minimum, maximum, average and mode of the range of ages of the respondents. Use the opportunity to use ROUND (a level 2 function) in conjunction with AVERAGE to demonstrate your ability to include a function as an argument for another function.
Note the use of the labels Average, Youngest, Oldest & Mode in D9:D12. These make your work more readable and easier to understand.
Don’t forget to do obvious calculations, for example: using COUNT to determine the number of respondent records. You will use the results of these calculations in further analysis later on.
Be sure to set up your data analysis neatly!
2.2 Level 2
The easy win here is the COUNTIF function, for example in the Gender column you can calculate the total number of respondents per gender.
Be sure to select your criteria correctly!
Again, you will use the results of these calculations in further analysis later on.
2.3 Level 3
Level 3 is probably the most important level of analysis as it contains the COUNTIFS function. Remember, you are looking to process your data so that you have information that helps you “solve” your Focus Question; Phase 3 requires you to make at least three statements demonstrating what you have learned.
Start by combining a question from the Biographical questions with a question from the Topic section of questions using the COUNTIFS function: calculate and compare the number of females who gave a particular answer to a question to the number of males that gave the same answer to the same question.
For example: if as one of your topic questions you asked the closed question “Do you like ice cream?”, you will be able to calculate how many females answered “Yes” versus how many males answered “Yes” and then compare the two groups.
Be careful: unless you have exactly equal numbers of respondents by gender, you will still have to normalise your data. In other words, if you look at the above numbers you might make the mistake of concluding “1 female eats ice cream and 2 males eat ice cream, so twice as many males as females eat ice cream”.
However, if your sample of respondents does not comprise equal numbers of each gender, you must normalise your data. The easiest way to accomplish this is to calculate the percentage of females who answered “Yes” as a percentage of the total number of female respondents etc. You will now be able to establish which gender has the highest likelihood of saying they like ice cream! You already have the total number of each gender as a result of your Level 2 calculations.
As you can see above, the initial assumption was, in fact, incorrect: the data shows no difference between the number of females who report they like ice cream versus the number of males who report they like ice cream.
Note again the use of labels which are used both to describe your information as well as to serve as the criteria in the functions.
See the post Advanced criteria in Excel logic functions for more advanced examples.
2.4 Level 4
Level 4 is not easy. The rubric’s suggestion of “A Lookup or nested IF-function OR functions not in the CAT CAPS curriculum” is deceiving: quite honestly I have not been creative enough to imagine a way of using a VLOOKUP in the PAT and I am seldom feeling brave enough for a nested IF statement!
Here, however, is one way to use a nested IF by grouping data into “categories” based on ranges of numeric data: Nested IF Statements in Excel — PAT example
2.5 Bonus extras
Consider using one of the following functions not in the Curriculum:
An example of the CONVERT function can be found in the post Phase 2 “additional data”.
3. Grade 11 Levels
| LEVEL | EXAMPLES OF FORMULAE/FUNCTIONS |
|---|---|
| ① | Simple, general functions that only include a range/list of cell references/values (e.g. SUM, MAX, MIN, COUNT, AVERAGE, TODAY) OR formulae to substitute any one of these functions OR simple calculations using only one arithmetic operator ( +, – ,*, / ) |
| ② | Less general functions that include only a range/list of cell references/values, (e.g. MEDIAN, MODE) OR functions that include a range/list of absolute cell references/values plus one parameter/value (e.g. SMALL, LARGE, ROUND) OR calculations using a combination of arithmetic/relational operators, brackets or calculations using a combination of any two simple functions at level ① |
| ③ | Functions that include a range/list of cell references/ values plus two parameters/criteria OR functions using a range/list of cell references/values plus criterion with one relational operator (e.g. COUNTIF, COUNTA, COUNTBLANK, SUMIF) OR a combination of more than two functions or calculations using a combination of any operators, brackets and other functions |
| ④ | More complex functions (AND, TEST, TRUE, FALSE) or functions not in the Grade 11 curriculum |
4. Grade 10 Levels
| LEVEL | EXAMPLES OF FORMULAE/FUNCTIONS |
|---|---|
| ① | Simple, general functions that only include a range/list of cell references/values (e.g. SUM, MAX, MIN, COUNT, AVERAGE) OR formulae to substitute any one of these functions or simple calculations using only one arithmetic operator ( +, – ,*, / ) |
| ② | Less general functions that include only a range/list of cell references/values, (e.g. MEDIAN, MODE) OR functions with empty brackets (e.g. RAND, TODAY, NOW) OR calculations using a combination of arithmetic /relational operators, brackets OR calculations using a combination of only two simple functions at level ① |
| ③ | Use a combination of more than two functions OR calculations using a combination of any operators, brackets and other functions |
| ④ | Functions using a range/list of cell references/values plus criterion with a relational operator (e.g. COUNTIF) OR functions not in the Grade 10 curriculum meaningfully used |
Next step: Phase 2 Excel Charts »