VCE Spreadsheets, SQL & Visualisation Definitions Worksheet: Spreadsheet structure, formulas and functions and Descriptive statistics (Modules 4–20)
Name:
Date:
Class:
✎
Key terms
Write what each term means, in your own words.
1
Raw_Data
2
header label
3
$B$1
4
=COUNTIF(range, criteria)
5
=SUMIF(range, criteria, sum_range)
6
=IF(test, value_if_true, value_if_false)
7
XLOOKUP
8
=MEDIAN(B2:B30)
9
mean vs median
10
=COUNTIF(range,A2)
Answer Key
VCE Spreadsheets, SQL & Visualisation · Modules 4–20 · Student wording will vary for definitions
1Raw_Data — A short, clear worksheet tab name that describes what the sheet holds. Use one consistent style with no spaces, and keep raw data on a separate tab from analysis, such as Summary.
2header label — A label in the first row or column that names the data below or beside it. Use one per column, add units like Height_cm, and keep capitalisation consistent.
3$B$1 — The dollar signs lock a cell reference, so $B$1 stays fixed when a formula is copied. A plain B1 is relative and shifts to match its new position. B$1 locks only the row.
4=COUNTIF(range, criteria) — Counts how many cells in a range match a value or condition, such as "Yes" or ">10". Example: =COUNTIF(C2:C30,"Yes") counts every Yes in C2 to C30.
5=SUMIF(range, criteria, sum_range) — Adds only the numbers in sum_range whose matching cell in range meets the criteria, such as the total of all sales for one category.
6=IF(test, value_if_true, value_if_false) — Checks a test, like B2>=50. Shows the second value if the test is true and the third if it is false. Text results go in quotes, e.g. =IF(B2>=50,"Pass","Fail").
7XLOOKUP — A lookup function that finds a key in one column and returns the matching value from another column, such as a price for a product code. It can show a message if no match is found.
8=MEDIAN(B2:B30) — Returns the middle value of the numbers in B2:B30 once ordered. With an even count it averages the middle two. You do not need to sort the cells first.
9mean vs median — An outlier drags the mean towards it but barely moves the median. If the two differ a lot, the data is skewed, so the median is usually the fairer typical value to report.
10=COUNTIF(range,A2) — Used in a frequency table: list each category in column A, then put this beside it to count how many times that category appears in the data range. Copy it down for every category.