Skip to content
Cambridge IGCSE0417

Create a data model

Information Communication Technology 0417 Chapter Notes

What this chapter covers

Create a data modelManipulate dataPresent data
ShareWhatsAppPost
Create a data model notes

Read online free. Download a watermarked copy with a free account.

Read the notes

The full Create a data model notes as text: skim, search, and jump between subtopics.

~24 min read

In this chapter

  1. 1Formula or function
  2. 2Building and editing the model
  3. 3Order of operations
  4. 4Relative and absolute cell references
  5. 5Named cells and named ranges
  6. 6Totals, averages and rounding
  7. 7The IF function and nested IF
  8. 8Looking up data
  9. 9Sorting data
  10. 10Sorting on more than one criterion
  11. 11Searching with comparison operators
  12. 12Combining criteria with AND, OR and NOT
  13. 13Searching with wildcards
  14. 14Display features
  15. 15Enhancing and aligning cells
  16. 16Formatting numbers
  17. 17Conditional formatting
  18. 18Printing the formulae
  19. 19Printing the values

1. Formula or function

A formula is a calculation the user writes: it is user defined. It starts with an = sign and may contain cell references, operators and a function.

A function has a name, such as `SUM` or `ROUNDUP`. It is not a whole formula: it is used inside one, for example `=SUM(B2:B9)`.

Examiner insight

“Some candidates explained referencing rather than functions. Few candidates were able to explain functions, usually giving an explanation of a formula rather than a function.”

Worked example 12 marks

A spreadsheet can contain formulas and functions. State two features of a formula which may not be found in a function.

  1. 1

    Starts with an = sign [1]

  2. 2

    Contains cell references (or contains operators, or can contain a function) [1]

2. Building and editing the model

A spreadsheet model is built from cells that hold data and cells that hold formulae. You can insert or delete cells, rows and columns, and merge cells to set out the model.

Build each formula from cell references, not typed values: `=B4/F13`, not the numbers held in those cells. To display the contents of another cell, enter a formula such as `=C8`.

Examiner insight

“Some candidates did not use a formula to display the room length and room width and entered the values in the cells.”

Worked example 13 marks

Enter a formula in cell C13 to display the value held in C8. Enter the same formula in cell C14.

Enter a formula in cell C15 to display the value held in C9. Enter the same formula in cell C16.

Enter a formula in cell C17 to display the value held in D8. Enter the same formula in cell C18.

Enter a formula in cell C19 to display the value held in D9. Enter the same formula in cell C20.

  1. 1

    C13 and C14: `=C8` [1]

  2. 2

    C15 and C16: `=C9` [1]

  3. 3

    C17 and C18: `=D8`, and C19 and C20: `=D9` [1]

3. Order of operations

The operators are `+` add, `-` subtract, `*` multiply, `/` divide and `^` indices. Use `*` for multiply, not the letter x.

Brackets are worked out first, then indices, then multiplication and division, then addition and subtraction. Add brackets only where the order needs to change.

Examiner insight

“Some candidates inserted brackets in the wrong place which produced the wrong result for example =(B7+B13)*((B14)+(B14^2)). No brackets were necessary =B7+B13*B14+B14^2 would produce the correct result.” “This answer was usually correct with a few using x instead of * as the multiplication operator.”

Worked example 12 marks

Cell B7 holds 12, cell B13 holds 3 and cell B14 holds 2. Work out the result of `=B7+B13*B14+B14^2` and of `=(B7+B13)*((B14)+(B14^2))`.

  1. 1

    `=B7+B13*B14+B14^2`: indices first, 2² = 4, then multiply, 3 × 2 = 6, then add, 12 + 6 + 4 = 22 [1]

  2. 2

    `=(B7+B13)*((B14)+(B14^2))`: brackets first, 12 + 3 = 15 and 2 + 4 = 6, then multiply, 15 × 6 = 90 [1]

4. Relative and absolute cell references

A relative reference such as `B4` changes when the formula is replicated to another cell. The reference moves by the same number of rows and columns as the formula.

An absolute reference has a dollar sign before the column letter and before the row number. It does not change while the formula is being replicated.

``` =B2*F1 ```

In this formula `B2` is relative and `F1` is absolute.

Examiner insight

“This part of the question was answered better than the other two parts showing that many candidates understand the concept of absolute referencing, but many candidates do not carry on to say ‘while the formula was being replicated’.” “However, it appears that candidates do not fully understand the implication of the formula having to be replicated and failed to use absolute cell referencing.”

Worked example 12 marks

Cell C2 holds this formula.

``` =B2*F1 ```

The formula is replicated down to cell C3. Write the formula now in C3 and state what has changed.

  1. 1

    ``` =B3*F1 ``` [1]

  2. 2

    The relative reference B2 moved down to B3. The absolute reference F1 did not change. [1]

5. Named cells and named ranges

A named range is a group of cells given a name. A named cell is one cell with a name. Use the name in a formula in place of the reference, for example `=Room_length*B6`.

A name is one word, so it cannot contain a space. Choose a name that tells the reader what the cells hold.

Examiner insight

“Most candidates fell into the trap of using a phrase, which would not have been accepted by the software because it contained a space.” “Fewer candidates used the named cell in the formula (=Room_length*B6).”

Worked example 12 marks

Create a named range with the name door that contains only the cells in the doors table that contain numeric data.

  1. 1

    Named range door created [1]

  2. 2

    for cells A20:E29 only [1]

6. Totals, averages and rounding

Functions in the syllabus: `SUM`, `AVERAGE`, `MAX`, `MIN`, `INT`, rounding and counting. Give a function a cell range such as `B2:B9`, not a list of cells. `COUNTIF` counts only the cells in a range that match a condition.

`ROUND` gives the nearest value. `ROUNDUP` always rounds up. `ROUNDDOWN` always rounds down, and `INT` rounds down to a whole number. The second number in the brackets is the number of decimal places, and 0 gives a whole number.

Examiner insight

“Many candidates omitted the ROUND function. Of those who used the ROUND function, a majority of candidates rounded it to one or two decimal places rather than to the nearest hour.” “Most candidates attempted this question as instructed, although many used the ROUND function instead of the ROUNDUP function.”

Worked example 12 marks

Enter in cell B8 a formula to divide the length of the room by the longest Skirting length (metres) in column F. Edit this formula so that the results are rounded up to the nearest whole number.

  1. 1

    Divide the length, in B4, by the longest length, in F13: `B4/F13` [1]

  2. 2

    Edit to round up: `=ROUNDUP(B4/F13,0)` [1]

7. The IF function and nested IF

`IF` tests a condition and gives one value if it is true and another if it is false.

``` =IF(D2>=40,"Pass","Fail") ```

Text results go in speech marks. Write comparisons with `<=` and `>=`. For more than two outcomes, put one `IF` inside another: a nested function.

Examiner insight

“Some candidates failed to use the IF function correctly and created two separate conditions, IF(F5<6 and IF(F5>6, therefore providing no solution for the condition F5=6.” “Most realised the need for a nested IF but failed to use comparison operators effectively.”

Worked example 15 marks

Enter in cell D8 a formula that displays the value held in cell H13 if more than one board is needed for the length. If only one board is needed for the length, the formula must display a value of 0.

Enter in cell D9 a formula that displays the value held in cell H13 if more than one board is needed for the width. If only one board is needed for the width, the formula must display a value of 0.

  1. 1

    Cell D8: `=IF(…)` with correct syntax [1]

  2. 2

    Test the number of boards, in B8: `B8=2` or `B8>1` [1]

  3. 3

    Value if true: `H13` [1]

  4. 4

    Value if false: `0` [1]

  5. 5

    Cell D9: `=IF(B9=2,H13,0)` [1]

8. Looking up data

`VLOOKUP` finds a value in the first column of a range and returns a value from the same row: `VLOOKUP(lookup value, range, column index, match type)`.

The column index counts columns inside the range, not across the sheet. Match type 0 (or FALSE) finds an exact match. Match type 1 (or TRUE) finds the largest value that is not bigger than the lookup value. Use `HLOOKUP` when the data runs across a row.

The range may be a named range, or data in an external file. Include only the data cells, not the labels. `XLOOKUP(lookup value, lookup range, return range)` takes two separate ranges.

Examiner insight

“Candidates need to reference only the required cells and not include extra columns or rows in the range.” “Some candidates incorrectly specified the lookup range (including the labels as well as the data).”

Worked example 16 marks

Enter in cell C8 a formula to subtract the contents of cell D8 from the length of the room.

Enter in cell C9 a formula to subtract the contents of cell D9 from the width of the room.

Edit both of these formulas to look up and display the stock length needed. You must use the named range Slength in your formulas.

  1. 1

    Cell C8: `=VLOOKUP(…)` [1]

  2. 2

    Lookup value `B4-D8` [1]

  3. 3

    Range `Slength` [1]

  4. 4

    Column index `3` [1]

  5. 5

    Match type `1` or `TRUE` [1]

  6. 6

    Cell C9: `=VLOOKUP(B5-D9,Slength,3,1)` [1]

Recap

  • A formula starts with = and can hold cell references, operators and functions such as SUM.
  • Operators: + - * / ^. Brackets first, then indices, then multiply and divide, then add and subtract.
  • Relative references move when a formula is replicated. Absolute references do not.
  • A named range is one word, with no space, and tells the reader what the cells hold.
  • ROUND is nearest, ROUNDUP is always up, ROUNDDOWN and INT are always down.
  • IF needs a test, a value if true and a value if false. Text goes in speech marks.
  • VLOOKUP range holds data only, not labels. The column index counts inside the range.

9. Sorting data

To sort data is to put records in order. Ascending order puts the smallest value, or the letter A, at the top. Descending order puts the largest value, or the letter Z, at the top.

Select all the data, every column of every record, then choose the column to sort on and the order. Every row moves as a whole, so data integrity is maintained.

Sorting only one column breaks each record apart. A price then sits beside the wrong item.

Examiner insight

“Weaker candidates either did not attempt this or sorted the data in the first column only.” “A significant number of candidates did not maintain the integrity of the data during the sorting process.”

Worked example 12 marks

Sort only the numeric data in the Doors table into ascending order of Wall space (centimetres) with the smallest value at the top.

  1. 1

    select the numeric rows of the Doors table, in every column, and sort on the Wall space column [1]

  2. 2

    ascending order with the smallest value at the top and data integrity maintained, so each row stays together [1]

10. Sorting on more than one criterion

A multiple-criteria sort orders the data by a first column, then by a second column inside each group of equal values.

Set the primary sort first and the secondary sort next. Choose ascending or descending for each level separately.

The order of the levels matters. Sorting by surname first and class second gives a different list from class first and surname second.

Examiner insight

“Some omitted this step, whilst other candidates did not get the hierarchy of the sorts as specified.” “Some candidates did not attempt the sort, some candidates performed a single sort in either descending or ascending order.”

Worked example 13 marks

Sort the staff data into ascending order of Department. Staff in the same department must be in descending order of Salary.

NameDepartmentSalary
RuizSales28000
ObiAdmin24000
TateSales31000
QuinnAdmin26000
ValeSales25000
  1. 1

    select all the records and sort on Department first, ascending [1]

  2. 2

    add a second level: sort on Salary, descending [1]

  3. 3

    Quinn, Obi, Tate, Ruiz, Vale [1]

11. Searching with comparison operators

To select a subset of data, search one field with a criterion. The records that match stay; the others are hidden or left out.

The operators are `=` (equal to), `<>` (not equal to), `>` (greater than), `<` (less than), `>=` (greater than or equal to) and `<=` (less than or equal to).

Read the wording of the question. 'More than 90' is `>90`. '90 or more' is `>=90`. The records equal to the limit decide which one is right.

Examiner insight

“The most common error was including records that were equal to 4 instead of <4.” “Although many completed this task with accuracy, a number of candidates searched for greater than or equal to 90 rather than greater than 90.”

Worked example 12 marks

Select the students who scored more than 90.

StudentMark
Ana95
Bo90
Cy88
Di98
Eve91
  1. 1

    search the Mark field with the criterion `>90` [1]

  2. 2

    Ana, Di and Eve are selected; Bo is not, because 90 is not greater than 90 [1]

12. Combining criteria with AND, OR and NOT

AND keeps a record only if both criteria are true. OR keeps a record if either criterion is true. NOT removes the records that match a criterion.

Use OR for two values of the same field, such as two cities. Use AND to add a second field, such as a salary limit.

In an automated filter, set two values of the same field on separate rows. Two limits entered in the same field would not work.

Examiner insight

“Most candidates set the correct criteria on the Male_Female field but not all set the correct criteria on the Gender_Pos field and a number used OR instead of AND to set both criteria.” “Fewer candidates used the correct selection criteria for more than 240GB of storage capacity. The most common incorrect responses included the drives with a 240GB capacity as well as those of greater capacity.”

Worked example 13 marks

Select the staff who work in London or Zagreb and earn less than 30000.

NameCitySalary
AyoLondon31000
BeaZagreb27000
CalParis22000
DovLondon24000
EliZagreb35000
  1. 1

    (City `= London` OR City `= Zagreb`), the two cities kept together as one test [1]

  2. 2

    AND Salary `<30000` [1]

  3. 3

    Bea and Dov; Ayo and Eli fail the salary test, Cal fails the city test [1]

13. Searching with wildcards

A wildcard stands in for characters you do not know. Many applications use the symbol `*` for any run of characters.

Put `*` on both sides to find a word anywhere in a field: `*Sales*` finds 'Sales Manager' and 'Head of Sales'.

'Begins with' and 'ends with' are not the same search as 'contains'. `Sales*` misses a word that comes later in the field. Read which of the three the question asks for.

Examiner insight

“Most candidates who attempted this question extracted those employees working in London or Zagreb, but fewer did a valid wildcard search on the word Sales (some used starts with as the criterion for this part of the search).” “The most common errors were using ‘begins with’ instead of ‘contains’ to select the required activities, the inclusion of the records for Scotland as well as France and the Course_Cost field not displayed with a currency symbol to two decimal places.”

Worked example 13 marks

Select the staff whose job title contains the word Sales.

NameJob
FoxSales Manager
GillSenior Sales Assistant
HurdEngineer
IyerHead of Sales
JainClerk
  1. 1

    search the Job field [1]

  2. 2

    use a wildcard search such as `*Sales*`, with `*` before and after the word [1]

  3. 3

    Fox, Gill and Iyer; 'begins with Sales' would find only Fox [1]

Recap

  • Sort: select all the data, then choose the column and the order, so data integrity is maintained.
  • Ascending: smallest at the top. Descending: largest at the top.
  • Sort on more than one criterion: the primary sort first, then the secondary sort.
  • Operators: `=`, `<>`, `>`, `<`, `>=`, `<=`. Check the records equal to the limit.
  • AND needs both criteria true. OR needs either one. NOT removes the match.
  • A wildcard `*` stands for any characters: `*Sales*` finds the word anywhere in the field.

14. Display features

A spreadsheet can display either formulae or values. Switch to formulae to check or print the formulae, and back to values to read the results.

Adjust the row height and column width so that all data, labels and formulae are fully visible. Where a label is too long, wrap text within the cell so that it is fully visible.

Rows and columns can be hidden and displayed again.

Examiner insight

“Some candidates did not ensure that the contents of all cells were fully visible, and some printouts were difficult to read by examiners even with the use of magnification devices.” “Some candidates did not narrow the column widths or wrap the text in the headings as shown in the question paper.”

Worked example 14 marks

Cell A1 holds the heading Total number of tickets sold in March. The heading is cut off by cell B1. Describe two ways to make the heading fully visible.

  1. 1

    Increase the column width of column A [1]

  2. 2

    until the whole heading can be read in the cell. [1]

  3. 3

    Or wrap the text in cell A1 [1]

  4. 4

    and increase the row height so that every line of the heading is visible. [1]

15. Enhancing and aligning cells

Enhance a spreadsheet with text colour, cell colour, bold, underline, italic and shading. Text colour changes the characters. Cell colour or shading changes the background.

A heading can be placed across several columns by merging the cells. Alignment is then set to match the layout: right aligned, left aligned or centre aligned, horizontally and vertically.

When a task gives a layout, check every merged cell, every alignment and every wrapped cell against it.

Examiner insight

“Candidates did not add a yellow background to all merged cells.” “Some candidates did not align the text to the right in column A or centre align the data in column B.”

Worked example 110 marks

Merge the cells and format the spreadsheet to look like the layout given. In the layout, the titles in rows 1, 6, 12 and 17 are large white text on grey, each across several columns. The labels in A3:B4 and A7:A10 sit at the right of their cells. The column headings in rows 18 and 19 are bold. Centre align all cells in the range B7:B10. Format cell B10 to display dollars ($) with 2 decimal places.

  1. 1

    A1:C1, A6:C6, A12:B12 and A17:E17 merged and centred [1]

  2. 2

    with large white text on a grey background [1]

  3. 3

    Cells A3:B3, A4:B4 and C18:E18 merged [1]

  4. 4

    Cells A18:A19 and B18:B19 merged [1]

  5. 5

    and wrapped as in the layout [1]

  6. 6

    Cells A3:B4 and A7:A10 right aligned [1]

  7. 7

    A3:B4, A18:B19 and C18:E18 in bold [1]

  8. 8

    C7:C8 left aligned [1]

  9. 9

    Other cells centre aligned vertically and horizontally [1]

  10. 10

    All currency values in dollars with 2 decimal places [1]

16. Formatting numbers

Format numeric data appropriately. Set the number of decimal places, a currency symbol and percentages as the task asks.

Read the introduction to the task for the currency. The symbol is the one the task names, not the one you know best. Apply the format to every cell that holds a currency value.

Examiner insight

“Many candidates who attempted this question did not format all monetary value to 2 decimal places.” “Some used $, £ or € as the currency symbol rather than TZS as instructed in the introduction.”

Worked example 13 marks

Cell B10 holds 4500.456. The task says all money must be in TZS with 2 decimal places. Format cell B10.

  1. 1

    Select cell B10 and apply a currency format [1]

  2. 2

    with the symbol TZS, not $, £ or € [1]

  3. 3

    and 2 decimal places, so B10 displays 4500.46 with the TZS symbol. [1]

17. Conditional formatting

Conditional formatting changes the display format depending on the contents of a cell. A cell may change text colour or cell colour when its value meets a rule.

A rule compares the cell with a value using 'greater than' or 'less than'. Decide whether values that are equal to the limit must be included.

Examiner insight

“Where the evidence was presented, some candidates mixed up the symbols for 'greater than' or 'less than' within their formulae or omitted to include those values that were equal to.”

Worked example 13 marks

The scores in column C must show red text when they are less than 40. Describe how to set this up.

  1. 1

    Select the cells in column C and open conditional formatting [1]

  2. 2

    Set the rule to less than 40, using the correct symbol [1]

  3. 3

    Set the format to red text, which only shows when a cell meets the rule. [1]

18. Printing the formulae

Page layout controls what is printed. Set the orientation to portrait or landscape. Control the number of pages, the print area, the gridlines and the row and column headings.

A formulae printout shows the formulae, not the values. When a task asks for one, it is usually in landscape, with the row and column headings displayed and the contents of all cells fully visible.

Examiner insight

“This was completed well by many candidates, but row and column headings were often omitted, and formulae were sometimes not fully visible in candidates work.”

Worked example 12 marks

Print your spreadsheet showing the formulas. Make sure that the printout is in landscape orientation, the row and column headings are displayed and the contents of all cells are fully visible.

  1. 1

    Formulae displayed and the contents of all cells fully visible [1]

  2. 2

    Landscape orientation with row and column headings displayed [1]

19. Printing the values

A values printout shows the results. When a task asks for one, it is usually in portrait, on a single page, with the row and column headings not displayed and the contents of all cells fully visible.

To remove the headings, switch off the display of row and column headings. Do not delete the rows and columns that hold your data.

Examiner insight

“Some candidates printed the full spreadsheet and not the specified cell range.” “A small number of candidates had the page orientation set to landscape rather than portrait.”

Worked example 11 mark

Print your spreadsheet showing the values. Make sure that the printout fits on a single page, is in portrait orientation, the row and column headings are not displayed and the contents of all cells are fully visible.

  1. 1

    Values printout: portrait, fully visible, single page, no row and column headings [1]

Recap

  • Display either formulae or values. Adjust row height and column width, and wrap text, so that all contents are fully visible.
  • Rows and columns can be hidden and displayed again.
  • Enhance with text colour, cell colour, bold, underline, italic and shading. Merge and align cells to match the layout given.
  • Format numbers: decimal places, the currency symbol the task names, percentages.
  • Conditional formatting changes the display depending on the contents of a cell. Check the symbol and the equal-to case.
  • Formulae printout: formulae shown, landscape, row and column headings displayed, all cells fully visible.
  • Values printout: portrait, single page, no row and column headings, all cells fully visible.

End-of-chapter exercise

Test yourself on the whole chapter. Work through these before moving on.

  1. A cell holds `=SUM(B2:B9)`. Name the function used in this formula.1 mark
  2. Cell B2 holds a price and cell C2 holds a quantity. Write a formula in D2 to calculate the cost.2 marks
  3. Cell A1 holds 8, cell A2 holds 4 and cell A3 holds 2. Work out the result of `=A1+A2*A3`.2 marks
  4. Cell A1 holds 20 and cell A2 holds 30. Write a formula to add them and divide the total by 5.2 marks
  5. Cell C2 holds this formula. ``` =B2*F1 ``` The formula is replicated down to cell C4. State each cell reference in C4, and why.2 marks
  6. Explain why a formula that is replicated down a column may need an absolute reference.2 marks
  7. A student names a range of cells Sales totals. The software rejects the name. Explain why, and give a name that would be accepted.2 marks
  8. Cell B8 holds a time of 130 minutes. Write a formula to display the time in hours, rounded to the nearest whole hour.3 marks
  9. A water company works out each bill from the volume of water used. Cell B2 holds the volume in cubic metres. Up to and including 10 cubic metres, the usage charge is a fixed £12. Above 10 and up to and including 25, it is the volume multiplied by 1.5. Above 25, it is the volume multiplied by 2. A standing charge of £4 is added to every bill, and the bill is rounded up to a whole number of pounds. (a) Write one formula in C2 to display the bill. (b) Give the value displayed when B2 holds 10, and when B2 holds 25.2.6 marks
  10. A post office keeps its prices in cells A1:C5, as in the table below. Each row gives the smallest weight in a band. Cell A9 holds the weight of a parcel in grams and cell B9 holds the number of parcels. A student enters `=VLOOKUP(A9,A1:C5,3,0)*B9` in C9 to work out the total cost, then replicates it to C10 and C11. (a) Identify three mistakes in the student's formula. (b) Write the corrected formula for C9. (c) Cell A10 holds 750 and cell B10 holds 4. Work out the value the corrected formula displays in C10. | | A | B | C | | --- | --- | --- | --- | | 1 | Minimum weight (g) | Letter cost | Parcel cost | | 2 | 0 | 1.10 | 3.20 | | 3 | 250 | 2.10 | 4.60 | | 4 | 500 | 2.90 | 5.80 | | 5 | 1000 | 3.80 | 7.50 |6 marks
  11. State what is meant by sorting data into descending order.1 mark
  12. Sort the data into ascending order of Age. Give the names in the new order. | Name | Age | | --- | --- | | Asha | 15 | | Ben | 12 | | Chen | 14 | | Dara | 11 | | Eli | 13 |2 marks
  13. A student selects only the Price column and sorts it. Explain why this is wrong.2 marks
  14. State the meaning of the operator `<>`.1 mark
  15. Select the students whose Mark is 50 or more. List them. | Student | Mark | | --- | --- | | Fay | 49 | | Gus | 50 | | Hal | 62 | | Ivy | 50 | | Jo | 38 |2 marks
  16. Sort the data into ascending order of Class. Students in the same class must be in ascending order of Surname. Give the surnames in the new order. | Surname | Class | | --- | --- | | Patel | 9B | | Adams | 9A | | Young | 9B | | Brown | 9A | | Lee | 9C |3 marks
  17. Select the people from France who are over 15. List them. | Name | Country | Age | | --- | --- | --- | | Kim | France | 17 | | Lou | Spain | 15 | | Max | France | 14 | | Ned | Italy | 16 | | Ola | France | 16 |2 marks
  18. A teacher searches the Surname field for names that contain the letters ar anywhere. State the search text, using a wildcard, and list the matching names. The names are Hart, Carter, Mara, Brown and Stone.3 marks
  19. A sailing club records one race. Select the boats whose name ends in star, whose Hull is not Steel and whose Time is 45 minutes or less. Then sort them into ascending order of Class. Boats in the same class must be in ascending order of Time. State the three criteria and list the boats in order. | Boat | Hull | Class | Time (minutes) | | --- | --- | --- | --- | | Northstar | Wood | B | 42 | | Starling | Fibre | A | 39 | | Daystar | Fibre | A | 45 | | Polestar | Steel | B | 40 | | Seastar | Wood | A | 47 | | Lodestar | Wood | A | 41 | | Starboard | Fibre | B | 38 |5 marks
  20. A shop sells bikes. Select the bikes that are Road or Mountain type, cost less than 450 and have a Stock above 0. Then sort them into ascending order of Type. Bikes of the same type must be in descending order of Price. State the search criteria and list the models in order. | Model | Type | Price | Stock | | --- | --- | --- | --- | | Aero | Road | 450 | 3 | | Brisk | Road | 380 | 0 | | Crest | Mountain | 520 | 5 | | Dune | Mountain | 390 | 2 | | Echo | Road | 410 | 4 | | Flux | Hybrid | 360 | 6 | | Gale | Mountain | 440 | 1 | | Halo | Road | 300 | 7 |6 marks
  21. A label in cell A1 is cut off by cell B1. Give two ways to make the label fully visible.2 marks
  22. A task asks for a printout showing the formulae. State what the cells must display on the printout.1 mark
  23. Cell A1 must have a grey background with white text. State the two settings that are changed.2 marks
  24. Cell B4 holds the value 0.075. It is formatted as a percentage with 1 decimal place. State what B4 displays.2 marks
  25. The cells hold the prices 12.5, 8 and 3.456. They are formatted as dollars ($) with 2 decimal places. State what each cell displays.3 marks
  26. A task says the values printout must not display row and column headings. A student deletes column A and row 1 of the spreadsheet. Explain why this is wrong and state what should be done.2 marks
  27. A task asks for a printout showing the formulae, in landscape orientation, with the row and column headings displayed. A student's printout shows the values, in portrait orientation, with no row and column headings. State the three changes needed.3 marks
  28. Scores of 50 or more must show a green cell colour. A student sets the rule 'greater than 50'. A pupil scores exactly 50. Explain the error.2 marks
  29. A swimming club records lap times in seconds in cells B2:B6, listed in the table. The cells are formatted to display 0 decimal places. The coach wants every time of 32 seconds or less to show a green cell colour. A helper sets the conditional formatting rule 'less than 32'. (a) State what each cell displays. (b) State which cells the helper's rule colours green. (c) Give the rule that is needed and state which cells it colours green. | Cell | Contents | | --- | --- | | B2 | 31.96 | | B3 | 32.04 | | B4 | 32 | | B5 | 31.4 | | B6 | 33.26 |5 marks
  30. The introduction to a task for a school shop says all currency values must be in euros (€) with 2 decimal places. The task asks for a printout of the values in cells A1:D12 only, in portrait orientation, with the row and column headings not displayed and the contents of all cells fully visible. Cell D12 holds 148.5. A student's printout covers the whole spreadsheet in landscape orientation. The letters A to D and the numbers 1 to 12 are printed along its edges. The student has deleted the column titles typed in row 1. The label in cell A3 is cut off. Cell D12 displays £148.5. Identify the seven errors and state how to correct each one.7 marks

Go deeper

Practise and revise with member-only material for this chapter.

Workbook

Exam-style practice questions with full worked solutions.

Unlock with a plan

Chapter at a Glance

A single-page summary of every key idea for fast revision.

Unlock with a plan

Free notes are just the start.

Unlock every Workbook and Chapter at a Glance, and generate your own worksheets and predicted papers.

Explore plans

Related chapters

Hardware and softwareInput devices and their usesStorage devices and mediaNetworksMicroprocessor-controlled devicesCommunication