VCE Spreadsheets, SQL & Visualisation Definitions Worksheet: Descriptive statistics and Manipulating, validating and testing spreadsheet data (Modules 21–34)
Name:
Date:
Class:
✎
Key terms
Write what each term means, in your own words.
1
=COUNTIFS(B2:B30,">=10",B2:B30,"<20")
2
=STDEV.S(B2:B30)
3
standard deviation
4
=TRIM(A2)
5
validation
6
Data > Data validation > Whole number between
7
List validation
8
Error alert
9
expected result
10
Conditional formatting
Answer Key
VCE Spreadsheets, SQL & Visualisation · Modules 21–34 · Student wording will vary for definitions
1=COUNTIFS(B2:B30,">=10",B2:B30,"<20") — Counts the numbers in a range that are at least the lower limit and less than the upper limit, giving the frequency of one bin. Equal-width bins must not leave gaps or overlap.
2=STDEV.S(B2:B30) — Returns the sample standard deviation of the numbers in a range, showing how spread out they are. Use STDEV.P if the range is the whole population.
3standard deviation — A measure of how spread out values are around the mean. A small value means the data is clustered close to the mean. A large value means it is widely spread. It uses the same units as the data.
4=TRIM(A2) — A text function that returns a tidy copy of the text in A2 with extra spaces removed. UPPER, LOWER and PROPER work the same way to fix capitals, e.g. =PROPER(A2) gives Mia Chen.
5validation — An automatic check that data is reasonable and allowed when it is entered, such as being in range or the right type, so typos and impossible values are caught.
6Data > Data validation > Whole number between — A rule that only lets a cell accept a number between a minimum and maximum, like age 11 to 14. Numbers outside the range are rejected. Use Decimal for numbers with parts.
7List validation — A rule that lets a cell accept only items from a set list, shown as a drop-down. Items can be typed in or taken from a range of cells, which keeps spelling consistent.
8Error alert — A message that pops up when someone enters data that breaks a validation rule. It can Stop the entry, Warn about it, or give Information, and a good message explains how to fix it.
9expected result — The answer you work out by hand using a few rows of data. Compare it with the formula's actual result. If they match, the formula works. If not, check the formula and its range.
10Conditional formatting — A spreadsheet tool that changes a cell's colour or icon automatically when its value meets a rule, so highs, lows and outliers stand out.