Grade 12 IEB CAT Excel Formulas and Functions

Comprehensive vocabulary flashcard set covering essential Excel functions, formulas, cell references, and error codes for the Grade 12 IEB CAT exam.

=SUM(A1:A10)

Adds all numbers in the specified cell range from A1 to A10.

=AVERAGE(A1:A10)

Calculates the arithmetic mean of the values from cell A1 to A10.

=MAX(A1:A10)

Finds and returns the highest value in the specified range.

=MIN(A1:A10)

Finds and returns the lowest value in the specified range.

=COUNT(A1:A10)

Counts the number of cells in the specified range that contain numbers.

=COUNTA(A1:A10)

Counts all non-empty cells in the specified range, including text, names, and numbers.

=COUNTBLANK(A1:A10)

Counts the number of empty cells in the specified range to identify missing entries.

=ROUND(A1,2)

Rounds a number to a specified number of decimal places (e.g., rounds $15.678$ to $15.68$).

=ABS(A1)

Returns the absolute value or positive distance of a number from zero (e.g., $ABS(-10) = 10$).

=PRODUCT(A1:A5)

Multiplies all numbers and values together across the specified cell range.

=MOD(A1,2)

Returns the remainder after division; useful for checking whether a number is odd or even.

=IF(A1>=50, "Pass", "Fail")

Tests a condition and gives one result if the condition is TRUE and another if it is FALSE.

Nested IF

An IF function placed inside another IF function to evaluate multiple sequential conditions (e.g., =IF(A2>=80,"Distinction",IF(A2>=50,"Pass","Fail"))).

=IFS(…)

Tests multiple conditions in order and returns the value corresponding to the first TRUE condition (e.g., assigns grades based on score brackets).

=AND(A1>=50, B1>=50)

Returns TRUE only if all specified conditions are met simultaneously (e.g., both marks are at least $50$).

=OR(A1>=50, B1>=50)

Returns TRUE if at least one of the specified conditions is met (e.g., either mark is at least $50$).

=NOT(A1="Yes")

Reverses a logical value from TRUE to FALSE or FALSE to TRUE (e.g., tests whether A1 is not "Yes").

=IFERROR(A1/B1, 0)

Evaluates a formula and replaces an error with a specified alternative value (e.g., displays $0$ if division by zero occurs).

=COUNTIF(range, criteria)

Counts cells within a range that match a single specified condition (e.g., =COUNTIF(A1:A10, ">50")).

=COUNTIFS(criteria_range1, criteria1, …)

Counts cells that meet multiple criteria across multiple ranges (e.g., =COUNTIFS(A:A, "Yes", B:B, ">50")).

=SUMIF(range, criteria, [sum_range])

Adds numerical values in a range that meet a single condition (e.g., =SUMIF(A1:A10, "Apple", B1:B10)).

=SUMIFS(sum_range, criteria_range1, criteria1, …)

Adds numerical values that satisfy multiple conditions across different criteria ranges.

=AVERAGEIF(range, criteria, [average_range])

Calculates the average of cells that meet a single specified condition (e.g., =AVERAGEIF(A:A, "Male", B:B)).

=AVERAGEIFS(average_range, criteria_range1, criteria1, …)

Calculates the average of values that meet multiple specified conditions across multiple ranges.

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

Searches vertically down the first column of a table for a value and returns a value from the specified column in the same row.

VLOOKUP Approximate vs Exact Match

FALSE specifies an exact match (standard for IDs and specific names); TRUE specifies an approximate match and requires the first column to be sorted in ascending order.

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Searches horizontally across the first row of a table and returns a value from a specified row in the same column.

=XLOOKUP(lookup_value, lookup_range, return_range, [if_not_found])

Looks for a value in a lookup array and returns a corresponding result from a return array; availability depends on Excel version.

=LEFT(A1,3)

Extracts a specified number of characters from the beginning (left) of a text string.

=RIGHT(A1,3)

Extracts a specified number of characters from the end (right) of a text string.

=MID(A1,2,4)

Extracts a specific number of characters from text, starting at a given character position (e.g., $4$ characters starting at position $2$).

=LEN(A1)

Counts and returns the total number of characters in a text string.

=UPPER(A1)

Converts all alphabetical characters in a text string to uppercase (e.g., "reagile" becomes "REAGILE").

=LOWER(A1)

Converts all alphabetical characters in a text string to lowercase (e.g., "REAGILE" becomes "reagile").

=PROPER(A1)

Capitalises the first letter of each word in a text string (e.g., "reagile matome" becomes "Reagile Matome").

=TRIM(A1)

Removes extra leading, trailing, and repeated spaces from imported text, leaving single spaces between words.

=CONCAT(A1,B1)

Joins two or more text strings together into a single string.

& (Ampersand Operator)

Concatenation operator used to join text strings with other strings or spaces (e.g., =A1&" "&B1).

=TEXT(A1,"000")

Formats a number and converts it to text using a specified format code (e.g., converts $7$ to "007").

=SUBSTITUTE(A1, "old", "new")

Replaces existing specified text with replacement text in a string.

=FIND("a", A1)

Finds the character position of a substring within text; it is case-sensitive.

=SEARCH("cat", A1)

Finds the character position of a substring within text without case sensitivity.

=TODAY()

Returns and displays the current system date.

=NOW()

Returns and displays the current system date and time.

=DAY(A1), =MONTH(A1), =YEAR(A1)

Functions that extract the day number, month number, or four-digit year from a given date serial number.

=DATE(2026,10,14)

Creates a valid date serial number from separate year, month, and day integers (e.g., 14 October 2026).

=DAYS(B1,A1)

Calculates the number of days between two specified dates.

=WEEKDAY(A1)

Returns an integer representing the day of the week for a given date.

=RANK.EQ(A1,$A$1:$A$10,0)

Ranks a number within a range; an order value of $0$ assigns rank $1$ to the highest value.

=LARGE(A1:A10,2)

Finds the k-th highest value in a data set (e.g., setting $k=2$ finds the second-highest mark).

=SMALL(A1:A10,3)

Finds the k-th lowest value in a data set (e.g., setting $k=3$ finds the third-lowest mark).

=MEDIAN(A1:A10)

Calculates and returns the middle value in a set of numbers.

=MODE.SNGL(A1:A10)

Finds the most frequent or commonly occurring number in a range.

=STDEV.S(A1:A10)

Estimates sample standard deviation to measure data variation around the mean.

=QUARTILE.INC(A1:A10,1)

Finds a specified quartile of a data set (e.g., quart argument $1$ returns the lower quartile).

=RAND()

Generates an evenly distributed random decimal number greater than or equal to $0$ and less than $1$.

=RANDBETWEEN(1,200)

Generates a random integer between two specified numbers (inclusive).

Relative Reference (A1)

A cell reference that changes automatically based on relative position when copied to other cells.

Absolute Reference ($A$1)

A cell reference locked with dollar signs so that both row and column remain fixed when copied (toggled via F4).

Mixed Reference ($A1 or A$1)

A reference where either only the column ($A1) or only the row (A$1) remains fixed while the other part changes when copied.

N/A

An Excel error indicating that a lookup function cannot find a matching value.

DIV/0!

An Excel error caused by attempting to divide a number by zero or by an empty cell.

VALUE!

An Excel error indicating that the incorrect type of value or data type was used in a formula.

REF!

An Excel error indicating an invalid cell reference, typically caused when referenced cells have been deleted.

NAME?

An Excel error indicating that Excel does not recognise the function name or text in a formula.

#

An error indicator showing that the column is too narrow to display the number, or that an unsupported negative date/time exists.

NUM!

An Excel error indicating an invalid numerical calculation.