BTEC (Business and Technology Education Council) BTEC Information Technology Syllabus

Write the spreadsheet formula to find the average of cells B2 to B20.

$=\text{AVERAGE(B2:B20)}$

What does the IF function do in a spreadsheet and what is its syntax?

It performs a logical test and returns one value if true and another if false. Syntax: $=\text{IF(logical\_test, value\_if\_true, value\_if\_false)}$.

What is the difference between a VLOOKUP and an HLOOKUP function?

VLOOKUP searches for a value in the leftmost (vertical) column of a table and returns a value from a specified column; HLOOKUP searches the top (horizontal) row and returns a value from a specified row.

What do the COUNT and COUNTA functions do?

COUNT counts cells in a range that contain numbers; COUNTA counts all non-empty cells (numbers and text).

What does the COUNTIF function do? Give its syntax.

It counts the number of cells in a range that meet a single condition. Syntax: $=\text{COUNTIF(range, criteria)}$.

Explain the difference between a relative and an absolute cell reference.

A relative reference (e.g. A1) changes when a formula is copied; an absolute reference (e.g. $\$A\$1$) is locked with dollar signs and stays fixed when copied.

What is a nested IF function?

An IF function placed inside another IF function to test multiple conditions in sequence, e.g. assigning grades based on several score boundaries.

What is data validation in a spreadsheet?

A feature that restricts the type or range of data that can be entered into a cell (e.g. whole numbers between 1 and 100, a date, or items from a list) to reduce input errors.

Give three examples of data validation rules/checks.

Range check (value within limits), type/data-type check, length check, presence check, list (drop-down) restriction, and format check.

What is the purpose of conditional formatting in a spreadsheet?

It automatically changes a cell's appearance (colour, icon, data bar) based on its value or a rule, making patterns, highs/lows and exceptions easier to spot.

Why is good data presentation important in a spreadsheet solution?

It makes information clear and easy to interpret for the user, using formatting, headings, charts and consistent number formats so trends and key figures stand out.

When should you use a line chart rather than a bar chart?

Use a line chart to show trends or continuous change over time; use a bar/column chart to compare discrete categories or values.

What type of data is best shown using a pie chart?

Parts of a whole — showing how each category contributes as a proportion/percentage of a total. Best with a small number of categories.

What is a dashboard in a spreadsheet context?

A single screen that brings together key data, charts, KPIs and summaries (often interactive) to give an at-a-glance overview for monitoring and decision-making.

Why is testing a spreadsheet solution important?

To ensure formulas calculate correctly, validation works, and the solution meets requirements and is error-free before being used for real decisions.

What are normal, extreme (boundary) and erroneous test data?

Normal: typical valid data the system should accept. Extreme/boundary: values at the limits of acceptable range. Erroneous: invalid data that should be rejected.

What is a test plan for a spreadsheet solution?

A structured document listing each test: test number, what is being tested, the test data, expected result, actual result and any action taken.

Define malware and name three types.

Malware is malicious software designed to harm or gain unauthorised access to a system. Types: virus, worm, trojan, ransomware, spyware, adware.

What is phishing?

A social engineering attack where fraudulent emails, messages or websites pretend to be from a legitimate source to trick users into revealing personal data such as passwords or bank details.

What is the difference between a computer virus and a worm?

A virus attaches to a file/program and needs the user to run it to spread; a worm is self-replicating and spreads across networks automatically without user action.

What is ransomware?

Malware that encrypts or locks a user's files or system and demands payment (a ransom) to restore access.

What is a Distributed Denial of Service (DDoS) attack?

An attack that floods a server or network with huge amounts of traffic from many computers at once, overwhelming it so legitimate users cannot access the service.

What is social engineering?

Manipulating people into breaking security or giving away confidential information (e.g. phishing, pretexting, baiting) rather than attacking technology directly.

Name four protection measures against threats to digital systems.

Firewalls, antivirus/anti-malware software, strong passwords, encryption, two-factor authentication, regular backups, software updates/patching, and user access levels/permissions.

What is a firewall and what does it do?

Hardware or software that monitors and controls incoming and outgoing network traffic based on security rules, blocking unauthorised access to/from a network.

What is encryption and why is it used?

Encryption scrambles data into an unreadable form (ciphertext) using a key, so that only authorised parties with the key can decrypt and read it — protecting data confidentiality if intercepted or stolen.

What is two-factor authentication (2FA)?

A security method requiring two forms of identity verification — typically something you know (password) plus something you have/are (a code sent to a device or biometrics).

What does the Data Protection Act / GDPR require organisations to do with personal data?

Process personal data lawfully, fairly and transparently; collect it only for specified purposes; keep it accurate, secure and no longer than necessary; and respect individuals' rights to access and erase their data.

What does the Computer Misuse Act 1990 make illegal?

Unauthorised access to computer material (hacking), unauthorised access with intent to commit further offences, and unauthorised modification of computer material (e.g. spreading viruses).

What does the Copyright, Designs and Patents Act protect?

It protects intellectual property — original works such as software, music, images and text — making it illegal to copy, distribute or use them without the owner's permission.

What is the difference between the internet and the World Wide Web?

The internet is the global network of interconnected computers/infrastructure; the World Wide Web is a service running on it — a collection of websites and web pages accessed via browsers.

Define LAN and WAN and state the key difference.

A LAN (Local Area Network) covers a small area such as one building; a WAN (Wide Area Network) covers a large geographic area, often connecting LANs over long distances (the internet is a WAN).

What is cloud computing?

Delivering computing services — storage, software and processing — over the internet from remote servers, allowing on-demand access from any connected device rather than from local hardware.

Give two advantages and one disadvantage of cloud storage.

Advantages: access files from anywhere, automatic backup, easy sharing/collaboration, scalable. Disadvantage: requires an internet connection and raises security/privacy concerns over data held by third parties.

What is an online community and what is digital citizenship?

An online community is a group of people interacting via the internet around shared interests (e.g. forums, social media). Digital citizenship is using technology responsibly, safely, ethically and respectfully online.

What is the 'digital divide'?

The gap between those who have access to digital technology and the internet (and the skills to use them) and those who do not, often due to cost, location or education.