Syllabus Detail

  • Spreadsheet terms; cell, formula, function (sum, average, max, min, count, countif) , label, worksheet, lookup tables vlookup/hlookup 11 ATAR, 12 GEN

 Quiz below.

vLookup - excellent help here

vLookup help 2 here

 Term Meaning Example
 Cell A cell is a box created by the crossing of a column and a row. The cell value is referenced with coordinates made up from the row and columns.  A1 is composed of a vertical column, A, and a horizontal row, 1.
 Formula A formula is an expression that is used to calculate the value of a cell. =(A1 + Z10)

 Function

A function is a pre-defined formula that are readily available in a spreadsheet software.  

- Sum

Returns the sum of the given range of cells. =SUM(A1:Z10)

- Average

Returns the average of the given range of cells. =AVERAGE(A1:Z10)

- Max

Returns the value of the cell with the largest value. =MAX(A1:Z10)

- Min

Returns the value of the cell with the smallest value. =MIN(A1:Z10)

- Count

Returns the counted number of cells with a value in the given range. =COUNT(A1:A10)

- Countif

Returns the counted number of cells with specified criterion.

 =COUNTIF(A1:Z10, "Hello") [Shows the count of cells with the word'Hello']

=COUNTIF(A1:Z10, 3) [Shows the count of cells with a value of '3']

Label

Commonly referred to as a heading used to identify a column in a spreadsheet.  "Date" - e.g. a column consisting of date values.

Worksheet

 A worksheet is a single spreadsheet made up of rows and columns that meet to create cells.  "Worksheet 1"

Lookup tables

These functions are used to reference values in cells, either in the same worksheet (locally) or external worksheets. LOOKUP / VLOOKUP / HLOOKUP

- VLookup

VLOOKUP is a function used when you need to find values from a table, or a specific range by row. For example, a spreadsheet could utilize VLOOKUP to find an employee's email address by looking up their last name. Note: the key to VLOOKUP is to organize data in such a way that the value we know (e.g. their last name) is to the left of the value we want to find (e.g. their email address).

 VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])

=VLOOKUP("Bloggs",B2:D4,2)

In this example, we're looking for the 2nd column's value from the ranges B2:D4, but only where "Bloggs" is found.

- HLookup

HLOOKUP works in the same way that VLOOKUP works, except with one difference: this looks up information specified by a row as opposed to a column. This means that it returns a value from the same column as a row is in.

 HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

=HLOOKUP("Chocolates",A1:C4,2)

In this example, we're looking up "Chocolate" in row 1 and returns the value from row 2 in the same column of the given range (A1:C4).

 

Practice by yourself or with a friend

A cell is the smallest part of a spreadsheet

A row is the line along a spreadsheet from left to right.

A column is the line down a spreadsheet from top to bottom.

A spreadsheet can have multiple worksheets - these are the tabs at bottom of the spreadsheet.

Answer these.

What is the difference between a label and a text or number in a cell?

List the 6 most common functions.

 

 

This quiz is for registered users only.

Further Research

Found an error or enhancement? Please fill out this contact us form.