Last Paper 3 practice

VLOOKUP: exact match

VLOOKUP (vertical lookup) searches down the first column of a table for a value. Then it returns a value from the same row.

It has four arguments. The table shows each one.

Copied down column E, it finds the right meal price for every pupil.

VLOOKUP: approximate match

With TRUE, or with the 4th argument left out, VLOOKUP does an approximate match. It finds the largest value that is less than or equal to the lookup value.

The first column must be sorted in ascending order (smallest first). If not, the result can be wrong with no error shown.

Use it for bands: ticket prices by age, grades by mark, discounts by quantity.

HLOOKUP

HLOOKUP (horizontal lookup) is VLOOKUP turned on its side. It searches across the top row of a table, then goes down.

Argument 1, lookup_value: what to find (B4, the form 10B).

Argument 2, table_array: the table, with codes in its top row ($H$12:$J$13).

Argument 3, row_index_num: which row of the table to return, counting from 1 at the top (2). Argument 4, range_lookup: FALSE for an exact match.

LOOKUP

LOOKUP searches one column (or row) and returns the value in the same position from another column (or row).

Argument 1, lookup_value: what to find. Argument 2, lookup_vector: the single column to search. Argument 3, result_vector: the column of answers, the same size.

LOOKUP always does an approximate match. The lookup vector must be sorted in ascending order. With no exact match, it uses the largest value that is smaller.

XLOOKUP

XLOOKUP is the newest lookup. It searches one column (or row) and returns from another. Unlike LOOKUP, it does an exact match by default.

Argument 1, lookup_value. Argument 2, lookup_array: where to search. Argument 3, return_array: where the answers are.

Argument 4, if_not_found, is optional: what to show when there is no match. Optional arguments 5 and 6 set the match mode and the search direction.

The answer column can be to the left of the search column. VLOOKUP cannot do that.

Show formulae, not only values

Normally cells show values: the results. Formulae view shows what is typed in each cell.

Most spreadsheets have a show formulas option, often on the formulas tab or in the view settings.

In formulae view, widen columns until every formula is fully visible. A cut-off formula cannot be marked.

Adjust row heights and column widths, and wrap text in long headings, so nothing is hidden. Hide or show rows and columns only when the task says.

E3   =VLOOKUP(D3,$H$3:$I$5,2,FALSE)
E4   =VLOOKUP(D4,$H$3:$I$5,2,FALSE)
...  (same pattern down to E8)
F3   =$I$7+E3-C3
F4   =$I$7+E4-C4
...  (same pattern down to F8)
G3   =IF(F3=0,"Paid",IF(F3<=10,"Nearly","Chase"))
...  (same pattern down to G8)
C9   =SUM(C3:C8)
E9   =SUM(E3:E8)
F9   =SUM(F3:F8)
C10  =AVERAGE(C3:C8)

Format cells: text, shading, borders, alignment

Typical tasks: make headings bold or italic, add shading (a fill colour), add borders, and set alignment.

Align text to the left and numbers to the right, so the decimal points line up.

Merge and centre a title across the top of the data, for example A1:G1.

Apply exactly what is asked, to exactly the cells named.

Number formats

A number format changes how a value looks. It does not change the value.

Decimal places: 31.8333 shown to 2 decimal places is 31.83. Currency adds a symbol: €31.83, or 31,83 € in Spanish style.

Percentage: the value is shown multiplied by 100, with a % sign. 0.25 shows as 25%.

Use the symbol asked for: euro (€), pound (£) or dollar ($). The $ in a currency format has nothing to do with the $ in an absolute reference.

Conditional formatting

Conditional formatting changes a cell's format automatically when its value meets a rule.

Select the range, choose the rule (for example "greater than"), type the value, then pick the format.

Because it is a rule, the format updates when the data changes. Colour added by hand does not.

Page layout for printing

Set the orientation: portrait (tall) or landscape (wide). Formulae printouts are wide, so landscape usually fits better.

Use fit to page to scale the printout to 1 page wide, or to the number of pages the task says. Set a print area if only part of the sheet must print.

For formulae printouts, switch on gridlines and row and column headings. Then the examiner can see which cell holds each formula.

Add a header or footer with your name, centre number and candidate number. Check the print preview before you print.

How spreadsheet marks are lost

Most lost marks come from the evidence, not from wrong thinking.

Before each printout, ask: is everything visible, is it on the right number of pages, and is my name on it?

A full stylesheet for h1, h2, h3, p and li

Exam tasks often give a list of styles to set for each element. Write one rule per selector, in the same order as the list.

Repeat a property in each rule where the task asks for it, even if it looks the same as another rule.

Try the toy: paste this CSS into the CSS box. In the HTML box, replace everything between `<body>` and `</body>` with this HTML, so the stylesheet link stays. Change the h2 colour or the p font size and watch the preview update.

/* Café Retiro house style */
body {
    background-color: #FDF6EC;
}

h1 {
    font-family: "Trebuchet MS", Helvetica, sans-serif;
    font-size: 32px;
    color: #8B4513;
    text-align: center;
}

h2 {
    font-family: "Trebuchet MS", Helvetica, sans-serif;
    font-size: 24px;
    color: #2E7D32;
}

h3 {
    font-family: "Trebuchet MS", Helvetica, sans-serif;
    font-size: 18px;
    color: #2E7D32;
    font-style: italic;
}

p {
    font-family: Georgia, "Times New Roman", serif;
    font-size: 16px;
    color: #333333;
    text-align: justify;
}

li {
    font-family: Georgia, "Times New Roman", serif;
    font-size: 16px;
    font-weight: bold;
}

<h1>Café Retiro</h1>
<h2>Breakfast</h2>
<h3>Served until 12:00</h3>
<p>Fresh coffee, toast with tomato and olive oil, and homemade cakes.</p>
<ul>
    <li>Café con leche</li>
    <li>Tostada con tomate</li>
    <li>Tarta de Santiago</li>
</ul>

There is something to try for this step in the IGCSE library.

Table borders: thickness, colour, collapse, invisible

border sets thickness, style and colour in one line: `border: 2px solid #8B4513;`. The style must be given, such as solid, dashed or dotted.

A border on table styles only the outside edge. Borders between cells need a rule for th and td too.

border-collapse: collapse merges the double lines between cells into single lines.

To make borders invisible, use border: none (or border-style: hidden). To make them visible, give a style such as solid.

table {
    border: 3px solid #8B4513;
    border-collapse: collapse;
}

th, td {
    border: 1px solid #8B4513;
}

/* a layout table with no visible borders */
table.plain, table.plain td {
    border: none;
}

Cells: padding, spacing, size, colour and alignment

padding is the space inside a cell, between its border and its content.

border-spacing is the gap between cells. It works only when border-collapse is separate, which is the default.

text-align sets horizontal alignment (left, center, right). vertical-align sets vertical alignment (top, middle, bottom).

Cells take width, height and background-color like any other element.

table {
    width: 80%;
    border-collapse: separate;
    border-spacing: 4px;
}

th {
    background-color: #8B4513;
    color: #FFFFFF;
    padding: 8px;
}

td {
    height: 40px;
    padding: 8px;
    text-align: center;
    vertical-align: middle;
    background-color: #FFF3E0;
}

The cascade: which style wins

When two rules set the same property on the same element, the browser uses the cascade to choose.

Highest priority first: inline style, then internal stylesheet, then external stylesheet, then the browser's own defaults.

Internal beats external when the `<style>` element comes after the `<link>`, which is the normal order. In fact, the rule read last wins.

Inside one stylesheet, a later rule beats an earlier rule for the same selector. A class selector beats a plain element selector, whatever the order.

<link rel="stylesheet" href="styles/cafe.css">  <!-- h2 green -->
<style>
    h2 { color: #1565C0; }                      /* h2 blue */
</style>
...
<h2>Breakfast</h2>
<h2 style="color: #C62828;">Lunch</h2>        <!-- inline red -->

/* styles/cafe.css */
h2 {
    color: #2E7D32;
    font-size: 24px;
}

How website marks are lost

Most lost marks come from small typing errors that a browser ignores without warning.

Before taking screenshots, open the page in a browser and check every style and link really works.

Giving evidence

Take a screenshot of the HTML source in the text editor, with every line readable. Use more than one screenshot if the code is long.

Take a screenshot of the page in a browser, showing the result of the styles.

Take a screenshot of the CSS file in the editor, showing its file name if possible.

Paste them into the evidence document in the order the question asks, and label each one.