Manipulate data

Manipulate Data in Spreadsheets: Your IGCSE Study Guide

Welcome to the "Manipulate Data" chapter! This is where we stop just inputting numbers and start making them work for us. Data manipulation is about taking raw data and sorting it, filtering it, and calculating new information from it to answer specific questions.

Think of your spreadsheet as a digital filing cabinet. We will learn how to quickly organise data (Sorting) and how to extract only the exact records we need (Searching and Filtering). These skills are essential for success in your practical spreadsheet tasks!

Section 1: Quick Review of Calculations and Functions

Before you can manipulate data by sorting or filtering, you often need to perform calculations to generate the necessary values. Spreadsheets use two main tools for calculation:

1.1 Formulae (Creating your own calculations)

A formula starts with an equals sign \(=\) and uses cell references, named cells, and arithmetic operators.

Arithmetic Operators:

• Addition: \(+\)
• Subtraction: \(-\)
• Multiplication: \(*\)
• Division: \(/\)
• Indices (Power): \(\wedge\)

Example: To calculate VAT (15% of the price in cell B2): =B2 * 0.15

1.2 Functions (Pre-defined tools)

A function is a built-in command that performs a calculation on a range of cells or named ranges.

Key Functions You Must Know (Syllabus 20.1):

• SUM: Adds up a range of numbers. (e.g., =SUM(A1:A10))
• AVERAGE: Calculates the arithmetic mean.
• MAX / MIN: Finds the maximum or minimum value in a range.
• COUNT: Counts cells containing numerical values.
• COUNTA: Counts non-empty cells (numbers or text).
• COUNTIF / COUNTIFS: Counts cells that meet single or multiple criteria.
• IF / Nested IF: Performs logical tests to return different values depending on whether conditions are true or false.
• LOOKUP (VLOOKUP, HLOOKUP, XLOOKUP): Searches for a value in a column or row and returns a corresponding value from another column or row.
• INT (Integer): Extracts the integer portion of a number.
• ROUND: Rounds a number to a specified number of decimal places.

Section 2: Sorting Data

Sorting means arranging data in a logical sequence. It changes the order of your spreadsheet records.

2.1 Ascending and Descending Order

When you sort data, you specify the order:

• Ascending Order: Arranges data from smallest to largest. (A to Z, 0 to 9, earliest date to latest date).
• Descending Order: Arranges data from largest to smallest. (Z to A, 9 to 0, latest date to earliest date).

2.2 Using Single and Multiple Criteria Sorts

Data can be sorted using a single criterion or multiple criteria:

Example with multiple criteria:

1. Primary Sort Key: Sort by Grade (Descending, e.g., A* at the top).
2. Secondary Sort Key: If two students have the same grade, sort them by Total Mark (Descending).

When sorting, always select all associated rows and columns across the dataset. Selecting only one column will sort that column independently and scramble your data records!

Section 3: Filtering and Selecting Data (Subsets)

Filtering (selecting subsets of data) hides rows that do not meet your specified conditions, displaying only the records that match your criteria.

3.1 Criteria

A criterion (plural: criteria) is the rule or condition applied to select data.

Example criteria:

• Show products where Stock Level is less than 50.
• Show staff members in the "Sales" department.

3.2 Comparison Operators

Use standard spreadsheet comparison operators to define criteria for numbers, text, or dates:

Operator	Meaning	Example Criteria
=	Equal to	=500
>	Greater than	>10.50
<	Less than	<18
>=	Greater than or equal to	>=01/01/2024
<=	Less than or equal to	<=10
<>	Not equal to	<>"North"

3.3 Logical Operators: AND, OR, NOT

When selecting data with multiple criteria, logical operators define how conditions combine:

1. AND: Strict Conditions (All criteria must be met)

• Selects records where ALL specified conditions are true.
• Example: Select records where Location = "London" AND Spend > 500.

2. OR: Inclusive Conditions (At least one criterion must be met)

• Selects records where ANY of the specified conditions are true.
• Example: Select employees in Department = "Marketing" OR Department = "Design".

3. NOT: Exclusion

• Excludes records matching a condition.
• Example: Select products where Category is NOT "Clearance" (using the <> operator).

3.4 Using Wildcards in Searches

Wildcards are special characters used to represent unknown or variable characters when searching text strings.

The Asterisk Wildcard (*)

The asterisk (*) represents zero or more characters.

• Example 1: Find names starting with "Sm": Sm* (matches Smith, Smudge, Smart).
• Example 2: Find names ending with "son": *son (matches Johnson, Harrison).

The Question Mark Wildcard (?)

The question mark (?) represents exactly one character.

• Example: Find product codes like A45 followed by one letter: A45? (matches A45A, A45B, but not A45 or A45AA).

Section 4: Presenting Manipulated Data (Display Features)

After manipulating your data, you must present it clearly and accurately (Syllabus 20.3).

4.1 Displaying Formulae vs. Values

In Cambridge examinations, you will be required to show evidence in two views:

• Values view: Shows the calculated numerical or text results (e.g. 15.00). Ensure all columns are fully widened so no data is truncated or displayed as ###.
• Formulae view: Displays the underlying formulae and functions (e.g. =B2*0.15). Row and column headings must be visible.

4.2 Formatting Numeric Data

Ensure all manipulated numeric data matches the required specification:

• Currency: Apply correct currency symbols (e.g., $ or €) consistently.
• Decimal Places: Format numbers to the specified number of decimal places (e.g., 2 decimal places for currency).
• Percentages: Format percentage values appropriately.

---