ICT 9626 - Spreadsheets: Test Elements

Objective: Test spreadsheet elements (functions, validation, formatting)

This document provides detailed notes and examples to test your understanding of key spreadsheet elements: functions, data validation, and formatting. It's designed to align with the Cambridge A-Level ICT 9626 specification.

1. Spreadsheet Functions

Spreadsheet functions are pre-built formulas that perform calculations. They are essential for data analysis and manipulation. Here are some common function categories and examples:

1.1. Basic Arithmetic Functions

These functions perform fundamental mathematical operations.

• SUM: Adds a range of numbers. Example: =SUM(A1:A10)
• AVERAGE: Calculates the average of a range of numbers. Example: =AVERAGE(B1:B20)
• MIN: Finds the smallest value in a range. Example: =MIN(C1:C15)
• MAX: Finds the largest value in a range. Example: =MAX(D1:D12)
• COUNT: Counts the number of cells in a range that contain numbers. Example: =COUNT(E1:E25)
• COUNTA: Counts the number of non-empty cells in a range. Example: =COUNTA(F1:F18)

1.2. Logical Functions

These functions evaluate logical expressions and return TRUE or FALSE.

• IF: Performs a logical test and returns one value if the test is TRUE, and another value if it's FALSE. Example: =IF(A1>10, "Pass", "Fail")
• AND: Returns TRUE if all conditions are TRUE. Example: =AND(A1>5, B1<10)
• OR: Returns TRUE if at least one condition is TRUE. Example: =OR(C1=10, D1=5)
• NOT: Reverses the logical value (TRUE becomes FALSE, and vice versa). Example: =NOT(A1=0)

1.3. Text Functions

These functions manipulate text strings.

• LEFT: Extracts a specified number of characters from the left of a text string. Example: =LEFT(A1, 3)
• RIGHT: Extracts a specified number of characters from the right of a text string. Example: =RIGHT(B1, 4)
• MID: Extracts a specified number of characters from the middle of a text string. Example: =MID(C1, 2, 5)
• CONCATENATE: Joins two or more text strings into one. Example: =CONCATENATE(A1, " ", B1) (or equivalently, =A1&" "&B1)

1.4. Date and Time Functions

These functions work with dates and times.

• TODAY: Returns the current date. Example: =TODAY()
• NOW: Returns the current date and time. Example: =NOW()
• DATE: Creates a date from year, month, and day values. Example: =DATE(2023, 10, 27)
• YEAR: Extracts the year from a date. Example: =YEAR(A1)

2. Data Validation

Data validation restricts the type of data that can be entered into a cell, ensuring data accuracy and consistency. This is crucial for preventing errors.

2.1. Setting up Data Validation

1. Select the cell(s) you want to apply data validation to.
2. Go to the Data tab and click on Data Validation.
3. In the Settings tab, choose the appropriate criteria from the "Allow" dropdown.

2.2. Common Data Validation Criteria

Criteria	Description	Example
List	Allows a list of values to be selected.	Enter a list of fruits (Apple, Banana, Orange) in the "Source" field.
Whole number	Allows only whole numbers to be entered.	Set the minimum and maximum values.
Decimal	Allows only decimal numbers to be entered.	Set the minimum and maximum values.
Date	Allows only valid dates to be entered.	Set the minimum and maximum dates.
Text length	Allows only text of a specified length to be entered.	Specify the minimum and maximum number of characters.
Custom formula is	Allows validation based on a custom formula.	Example: =COUNT(A1:A10)>0 (requires at least one value in the range)

3. Spreadsheet Formatting

Formatting enhances the readability and presentation of your spreadsheet. This includes formatting cells, numbers, and the overall layout.

3.1. Cell Formatting

Format cells to control appearance (font, color, borders, alignment).

• Font: Change the font type, size, and color.
• Alignment: Align text (left, center, right, top, middle, bottom).
• Borders: Add borders to cells.
• Fill Color: Change the background color of cells.

3.2. Number Formatting

Format numbers to control how they are displayed (currency, percentage, number of decimal places).

• Currency: Display numbers as currency (e.g., $100.00).
• Percentage: Display numbers as percentages (e.g., 0.25).
• Number: Control the number of decimal places and the number of digits.
• Date: Format dates in different ways (e.g., dd/mm/yyyy, mm/dd/yyyy).

3.3. Conditional Formatting

Applies formatting to cells based on their values. Useful for highlighting important data.

• Highlight Cells Rules: Highlights cells based on conditions (e.g., greater than, less than, equal to).
• Data Bars: Displays data bars within cells to visually represent values.
• Color Scales: Applies a color scale to cells based on their values.
• Icon Sets: Displays icons within cells based on their values.

Example: Combining Functions, Validation, and Formatting

Consider a spreadsheet to track student scores. You could:

1. Use the SUM function to calculate the total score.
2. Use IF to determine if a student passed or failed based on their total score.
3. Use Data Validation to ensure that only numerical scores are entered.
4. Use Conditional Formatting to highlight students who have scores above a certain threshold.