8/1/26, 7:23 PM ICT question maker - Google Gemini
Pearson Edexcel International GCSE ICT (4IT1/02)
Paper 2 Practical: Spreadsheet Formulas & Functions Guide
To secure full marks in the spreadsheet tasks (Section B), you must be comfortable reading,
writing, and replicating the following formulas and functions.
1. Basic Arithmetic Formulas
These are manually typed mathematical calculations using standard operators. You must start
every formula with an equals sign ( = ).
Addition ( + ): =A1+B1 (Adds the value in cell A1 to the value in B1).
Subtraction ( - ): =A1-B1 (Subtracts the value in B1 from A1).
Multiplication ( * ): =A1*B1 (Multiplies the value in A1 by B1. E.g., Calculating total cost =
Price * Quantity).
Division ( / ): =A1/B1 (Divides the value in A1 by B1).
Powers / Indices ( ^ ): =A1^2 (Calculates A1 to the power of 2).
Note on Order of Operations (BODMAS/PEMDAS): Spreadsheets follow standard mathematical
order. If you need a calculation to happen first, put it in brackets.
Example: =(A1+B1)*C1 (Adds A1 and B1 before multiplying the result by C1).
2. Standard Aggregation Functions
These are built-in functions used to perform calculations on a range of cells.
SUM: =SUM(A1:A10)
Purpose: Adds all the numeric values in the specified range.
AVERAGE: =AVERAGE(A1:A10)
Purpose: Calculates the arithmetic mean of the specified range.
MAX: =MAX(A1:A10)
Purpose: Finds the highest (maximum) value in the range.
MIN: =MIN(A1:A10)
Purpose: Finds the lowest (minimum) value in the range.
3. Counting Functions
Edexcel frequently tests your ability to choose the correct counting function based on the data
type.
COUNT: =COUNT(A1:A10)
Purpose: Counts the number of cells that contain NUMBERS ONLY. (If a cell has text, it is
ignored).
COUNTA: =COUNTA(A1:A10)
Purpose: Counts the number of cells that are NOT EMPTY (It counts cells containing
either text or numbers).
COUNTIF: =COUNTIF(range, criteria)
Purpose: Counts cells that meet a specific condition.
[Link] 1/3
8/1/26, 7:23 PM ICT question maker - Google Gemini
Example: =COUNTIF(B2:B20, "Peripherals") (Counts how many items are in the
Peripherals category).
Example: =COUNTIF(C2:C20, ">50") (Counts how many items have a stock level
greater than 50).
4. Logical & Conditional Functions
These functions test conditions and output different results based on whether the condition is True
or False.
The IF Statement: =IF(logical_test, value_if_true, value_if_false)
Purpose: Outputs one value if a condition is met, and a different value if it is not.
Example (Text output): =IF(A1>100, "Reorder", "") (If A1 is >100, display "Reorder". If
not, display nothing - note the two quotation marks with no space for a blank cell).
Example (Calculation output): =IF(B2>50, B2*0.10, 0) (If B2 is >50, calculate a 10%
discount. If not, the discount is 0).
SUMIF: =SUMIF(range, criteria, [sum_range])
Purpose: Adds the values in the sum_range only if the corresponding cells in the range
meet the criteria .
Example: =SUMIF(A2:A20, "London", C2:C20) (Looks through column A for "London".
Every time it finds it, it adds the corresponding sales figure from column C).
5. Data Lookup Functions (The VLOOKUP)
This is usually the highest-scoring formula in the exam. It searches for data in one table to populate
another.
VLOOKUP (Vertical Lookup): =VLOOKUP(lookup_value, table_array, col_index_num,
[range_lookup])
lookup_value : The single piece of data you want to search for (e.g., A2 ).
table_array : The reference table where the data is stored. This MUST be absolutely
referenced using $ signs or a Named Range (e.g., $D$2:$F$10 or DiscountTable ).
col_index_num : The column number in the reference table containing the answer you
want to pull back (e.g., 2 for the second column).
[range_lookup] : ALWAYS use 0 or FALSE in Edexcel exams to force an exact match.
Full Example: =VLOOKUP(A2, $D$2:$F$10, 2, 0)
(Less Common but still on syllabus) HLOOKUP (Horizontal Lookup):
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Purpose: Works exactly like VLOOKUP, but searches across the top row of a table instead
of the first column.
(Less Common but still on syllabus) LOOKUP (Vector Lookup): =LOOKUP(lookup_value,
lookup_vector, result_vector)
Example: =LOOKUP(A2, INFORMATION!A4:A30, INFORMATION!D4:D30)
6. Rounding Functions
Sometimes calculations leave you with long decimals that need standardising, distinct from simply
changing the visual currency formatting.
INT: =INT(A1)
[Link] 2/3
8/1/26, 7:23 PM ICT question maker - Google Gemini
Purpose: Returns the integer (whole number) portion of a decimal by always rounding
down (e.g., 4.9 becomes 4).
ROUND: =ROUND(number, num_digits)
Purpose: Rounds a number to a specified number of decimal places.
Example: =ROUND(A1, 2) (Rounds the value in A1 to 2 decimal places).
Example: =ROUND(A1, 0) (Rounds the value to the nearest whole number).
7. Length and Text Functions
LEN: =LEN(A1)
Purpose: Returns the total number of characters in a cell (including spaces). Often used in
theory questions (e.g., "State the function used to find how many characters are in cell
A1").
Critical Exam Rules for Formulas (The Cheat Sheet)
1. Absolute Referencing ( $ ): If you are multiplying a column of numbers by a single fixed value
(like a VAT rate in cell G1), you must lock it using dollar signs: =A2 * $G$1 . If you don't, when
you drag the formula down, it will try to multiply A3 by G2, which will cause an error.
2. Text needs Quotes ( "" ): If a formula outputs text, it must be in quotes: =IF(A1>50,
"Pass", "Fail") .
3. The Blank Cell Trap: If a question asks you to leave a cell blank if a condition isn't met, you
must use two double-quotes with no space between them: "" . (e.g., =IF(A1>50,
"Discount", "") ).
expand
4. Formula View is Mandatory: Examiners mark your printouts. If the question asks for Formula tune
View ( Ctrl + ` ), and you only show the numbers (Values View), you will score zero marks
for all your calculations. chat_spark
5. Named Ranges: If the exam asks you to create a "Named Range" for a table (e.g., TaxData ),
you can type that name into your VLOOKUP instead of using dollar signs: =VLOOKUP(A2,
TaxData, 2, 0) .
[Link] 3/3