Create a Data Model (Cambridge (CIE) IGCSE ICT): Flashcards
Exam code: 0417 & 0983
1/660Still learning
Know0
Define spreadsheet.
Was this flashcard helpful?
Cards in this collection (66)
Define spreadsheet.
A spreadsheet is software used to analyse, visualise and manipulate data.
What three things is a spreadsheet made up of?
A spreadsheet is made up of cells, rows and columns.
What is a cell?
A cell is one box on the spreadsheet, referenced using its cell reference, such as A1.
What is the difference between a row and a column?
A row goes across and is referenced using the number down the side. A column goes down and is referenced using the letter at the top.
True or False?
Rows go down and columns go across.
False.
Rows go across and columns go down.
How can the structure of a spreadsheet be altered?
By inserting or deleting cells, rows and columns.
What does merging cells do?
Merging combines two or more cells into one larger cell, which is useful for headers or titles that span multiple columns.
Why are cell references used in formulae?
Referencing specific cells makes formulae dynamic and adaptable, so the calculation updates with the data.
Name the five arithmetic operators used in formulae.
The operators are add (+), subtract (-), multiply (\*), divide (/) and indices (^).
What is the difference between an absolute and a relative cell reference?
An absolute reference, written $A$1, stays constant when a formula is copied. A relative reference, written A1, changes.
Give two benefits of using named cells and named ranges.
They let you easily refer to a group of adjoining cells, they shorten and simplify formulae, they remove the need to look up cell references, and you do not have to re-set absolute referencing manually.
Define formula.
A formula is a statement that performs simple calculations in a spreadsheet.
What must every formula start with?
Every formula starts with an = sign.
What two ways can a formula perform a calculation?
A formula can use numbers directly, such as
=5*2, or referenced data held in cells, such as=A1*B2.What happens when data in a referenced cell is changed?
The formula automatically recalculates based on the new value. This is a core concept of spreadsheet modelling.
Which symbol is used for indices in a formula?
The ^ symbol is used for indices, meaning power of.
Define function.
A function is a pre-defined formula, built into the spreadsheet software, used to carry out more complex calculations.
What is the difference between a formula and a function?
A formula is a statement written by the user to perform simple calculations, while a function is a pre-defined formula built into the software with a specific name.
What do the SUM and AVERAGE functions do?
SUM adds all the numbers in a range of cells, and AVERAGE calculates the average of a range of cells.
What do the MAX and MIN functions do?
MAX finds the largest number in a range and MIN finds the smallest.
What is the difference between the INT and ROUND functions?
INT rounds a number down to the nearest integer, while ROUND rounds a number to a specified number of digits.
What is the difference between COUNT and COUNTA?
COUNT counts the cells in a range that contain numbers, while COUNTA counts cells that contain numbers and/or labels.
True or False?
COUNT counts every cell in a range that contains numbers or labels.
False.
COUNT counts only cells containing numbers. COUNTA is the one that also counts labels.
What does the IF function do?
IF returns one value if a condition is true and another if it is false, written as
=IF(condition, true, false).What is the difference between HLOOKUP, VLOOKUP and XLOOKUP?
HLOOKUP performs a horizontal lookup, VLOOKUP performs a vertical lookup, and XLOOKUP performs either.
Define nesting.
Nesting is using a function within another function, for example
=IF(A1>B1, MAX(A1:B1), MIN(A1:B1)).Functions can use external data from another worksheet, workbook or even a .
Functions can use external data from another worksheet, workbook or even a database.
What is the order of operations?
The order of operations determines the sequence in which calculations are performed within a formula.
Why does the order of operations matter?
It ensures the calculation is completed correctly.
What does BIDMAS stand for?
Brackets, Indices, Division and Multiplication, then Addition and Subtraction.
What is calculated first in BIDMAS?
Brackets are calculated first.
What does the 'I' in BIDMAS stand for?
Indices, meaning powers of and square roots.
In what order are division and multiplication carried out?
Division and multiplication are carried out left to right, as are addition and subtraction.
True or False?
Multiplication is always carried out before division.
False.
Division and multiplication have equal priority and are carried out left to right.
A1 holds 10 and B1 holds 20. What is the result of
=(A1+B1)*2?The result is 60, because the brackets are calculated first (10 + 20 = 30) and the result is then multiplied by 2.
A1 holds 10 and B3 holds
=20*2. What is the result of=A1+B3?The result is 50, because B3 is calculated first (20 x 2 = 40) and then A1 is added (10 + 40 = 50).
Define relative cell reference.
A relative cell reference means that when a formula is copied, the cell reference can change. It is relative to the cell containing the formula.
Which type of cell referencing is used by default?
Relative cell referencing is the default used in spreadsheets.
The formula
=A1+B1in C1 is copied to C2. What does it become?It adjusts to
=A2+B2, because the references are relative to the new location.Give two benefits of relative cell references.
They save time by automatically adjusting references when copied, improving efficiency, and they are flexible, so one formula can be used on a range of data.
Define absolute cell reference.
An absolute cell reference means that when a formula is copied, the cell reference does not change.
Which symbol fixes an absolute cell reference?
Absolute cell references are fixed using the $ symbol, for example
$A$1.The formula
=$A$1+B1in C1 is copied to C2. What does it become?It becomes
=$A$1+B2. The absolute reference to A1 stays fixed while the relative reference changes.Give an example of when an absolute cell reference is needed.
When a calculation uses a fixed value such as a tax rate that you want consistently applied across the spreadsheet.
Give two benefits of absolute cell references.
They provide consistency, because the same cell is always referenced, and clarity, because the formulas become easier to understand.
True or False?
A relative cell reference stays the same when a formula is copied.
False.
A relative reference changes when the formula is copied. It is an absolute reference that stays the same.
Name the three display features of a spreadsheet.
The display features are displaying either formulas or values, adjusting the height and width of rows and columns, and wrapping text.
What are the three parts of formatting a spreadsheet?
Formatting is split into enhancing the look, formatting numeric data and using conditional formatting.
What three things are changed to enhance the look of a spreadsheet?
The text colour, the cell colour and the cell emphasis, such as bold, italic and underline.
What three things does formatting numeric data include?
Adjusting the number of decimal places, using different currency symbols as appropriate, and dealing with percentages.
Which formatting would be used to display prices in a spreadsheet?
Currency symbols would be used, as part of formatting numeric data.
Define conditional formatting.
Conditional formatting means dynamically changing the format of a cell based on its contents.
True or False?
Conditional formatting has to be applied by hand to each cell.
False.
Conditional formatting changes a cell's format dynamically, based on its contents.
What two things does setting the page layout of a spreadsheet include?
Changing the orientation and controlling the print layout.
What are the two page orientations?
The two orientations are portrait and landscape.
Define data selection.
Data selection allows you to focus on a specific subset of your data based on certain criteria.
Why is data selection useful?
It is useful for analysing parts of a larger dataset.
Name the three logical operators used to search a spreadsheet.
The logical operators are AND, OR and NOT.
Name three comparison operators used in searches.
The comparison operators are greater than (>), less than (<), equal to (=), greater than or equal to (>=), less than or equal to (<=) and not equal to (<>).
When are wildcards used?
Wildcards are used when you are unsure of the exact data you are looking for.
What do the asterisk and question mark wildcards represent?
The asterisk (\*) represents any number of characters, while the question mark (?) represents a single character.
What would a search for 'A\*' find?
It would find any entry beginning with A, such as Alex and Aaron, because the asterisk represents any number of characters.
True or False?
A search for 'A?e' would find 'Alex'.
False.
The question mark represents a single character, so 'A?e' would find 'Abe' but not 'Alex'.
What does sorting do in a spreadsheet?
Sorting arranges data based on specific criteria, in either ascending or descending order.
Give an example of sorting on multiple criteria.
Sorting a list of students first by grade, and then alphabetically by name within each grade.
To find students who scored above 85 and are in Year 11, the two criteria are joined with the operator.
To find students who scored above 85 and are in Year 11, the two criteria are joined with the AND operator.
Sign up to unlock flashcards
or