TABLE OF CONTENTS

ALL GRADES GRADE 10 GRADE 11 GRADE 12

Key Points Formulas ROUND ROUNDDOWN
Errors SUM POWER ROUNDUP
Absolute Referencing Average SUMIF INT
MAX IF TRUNC
MIN NESTED IF
COUNT VLOOKUP
COUNTA HLOOKUP
COUNTBLANK XLOOKUP
COUNTIF LEN
RAND CONCATENATE
RANDBETWEEN UPPER
MODE LOWER
MEDIAN LEFT
SMALL RIGHT
LARGE MID
IF FIND
VALUE

Sourcs
CHATGPT
Study Opportunities
 Key Points
1. Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.
A feature is a useful tool in spreadsheet applications for automatically filling a range of cells with data based on a pattern or series.
It saves time and effort when you need to extend a series or replicate data across multiple cells.
2. AutoFill
Location: HOME TAB - Number Group - More Options
3. Formmatting Allows you to change the format of numbers, text, dates and times and currency
Some computers have different formats
Seperation of ranges or criterias Option 1: ; Using a semi colan
4. Option 2: , Using a comma
5. Quotation Marks Using quotation marks are important for different calculations.
 Errors
Errors in Excel can occur for various reasons, and understanding these errors is crucial for troubleshooting and ensuring accurate data. Here’s an overview of common Excel errors, their causes, and how
to address them

Common Errors

#DIV/0!
1. Cause Division by zero. This occurs when a formula attempts to divide a number by zero or a cell that is blank.
Fix Check the divisor in your formula and ensure it is not zero or blank.

#N/A
2. Cause A value is not available or a lookup function (like VLOOKUP or HLOOKUP) cannot find the value you're searching for.
Fix Ensure that the lookup value exists in the range you're searching

#VALUE!
3. Cause Incorrect type of argument or operand in a function. For example, trying to perform arithmetic on text data.
Fix Check the types of data you are using in your formulas.

#REF!
4. Cause A cell reference is not valid. This usually happens when cells referenced in a formula have been deleted or moved.
Fix Review and correct the cell references in your formula.

#NUM!
5. Cause Invalid numeric value in a formula or function. This can occur with improper arguments for functions like SQRT or when a formula results in an invalid number.
Fix Ensure that numeric values are valid and appropriate for the function used.

#NAME?
6. Cause Excel does not recognize text in a formula. This can happen if there is a typo in a function name or if a named range or function is not defined.
Fix Check for typos in function names and ensure that all named ranges and functions are correctly defined.

#NUM!
7. Cause Issues with numeric calculations, such as very large or very small numbers that Excel cannot process, or incorrect arguments in functions like IRR.
Fix Review the formula and arguments to ensure they are within acceptable ranges and formats.

#NULL!
8. Cause An intersection of two ranges that do not intersect or a missing operator between ranges.
Fix Check that you are using the correct range operators.

#########
9. Cause Cell width is to small
Fix Increase cell width

General Troubleshooting Tips

Error Checking Tool Use Excel’s built-in error checking tool. Go to the "Formulas" tab and click "Error Checking" to identify and correct errors.
Evaluate Formula Use the "Evaluate Formula" tool in the "Formulas" tab to step through each part of a formula to see where the error might be occurring.
IFERROR Function Use IFERROR to handle errors gracefully and provide alternative results.
Formula Auditing Use formula auditing tools in Excel to trace precedents, dependents, and errors in your formulas.
 Absolute Referencing
Absolute referencing in Excel is a technique used to lock a specific cell reference in a formula so that it does not change when you copy or move the formula to other cells. This is particularly useful when you need to refer
to a fixed value or range across multiple cells.

Types of Cell References

1. Relative Reference A1 Changes when you copy the formula to another cell.
2. Absolute Reference $A$1 Remains constant when you copy the formula. *NOTE: Used from Grade 10 - 12. [MOST IMPORTANT!!!]
Partially absolute and partially relative. It can either fix the row or the column.
3. Mixed Reference $A1 (absolute column, relative row)
A$1 (relative column, absolute row)

How to Use Absolute Referencing

Condition $A$1 Using Shortcut Keys [Fn F4]

Behavior When you copy a formula with $A$1 to another cell, the reference remains $A$1 regardless of where the formula is pasted.

Example:
1. Enter Data Suppose you have a constant value in cell B1 that you want to use in multiple formulas.
2. Create a Formula In cell C1, you enter the formula =A1*$B$1.
When you drag the formula from C1 to C2, the formula in C2 will still reference B1 as $B$1:
3.
Copy the Formula In C2: =A2*$B$1
 FORMULAS
A formula is an expression used to perform calculations, manipulate data, or evaluate conditions. Formulas can be as simple as adding two numbers or as complex as
calculating statistical measures or financial projections.
Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
Operands A1 and B1 These are the values or cell references used in the formula.

Operators Example Scenario

Addition + Adds values. Name Test 1 Test 2 Final Mark
Subtraction - Subtracts values. 50 100 150
Multiplication * Multiplies values. Ginger Beard 25 65 90
Division / Divides values. Harvey Delacruz 50 95 145
Exponentiation ^ Raises a number to a power. Megan Wagner 42 85 127
Concantenation & Joins text strings. Marion Spears 10 50 60
Neil Miles 24 84
Basic Arithmetic Examples

1. Calculate the total marks for Ginger Beard using the addition formula
Ginger Beard 90

2. Determine Neil Miles score for Test 2.

Neil Miles - Test 2 60

3. What is the minimum passing mark required for the final grade?
Pass Mark? 75

4. Calculate the percentage score achieved by Megan Wagner.

Megan Wagner 84.6666666666667
 SUM Function (=SUM)
A function in Excel is one of the most commonly used functions. It allows you to quickly add up a range of numbers, cells, or a combination of both. This function is particularly useful for summarizing data,
performing calculations, and analyzing numerical datasets.
Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
SUM = SUM(range)
Example Scenario
Using the SUM Function Name Test 1 Test 2 Final Mark
1. A1:A5 Adding a Range of Cells 50 100
2. A1, A3, A5 Adding Specific Cells Ginger Beard 25 65 90
Harvey Delacruz 50 95 145
Key Points Megan Wagner 42 85 127
Cell Ranges You can specify ranges of cells Marion Spears 10 50 60
Multiple Ranges You can include multiple, non-contiguous ranges Neil Miles 24 60 84
Non-Numeric Data Cells containing text or empty cells are ignored. Cells with errors, such as #DIV/0!, will cause the function to return an error.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the SUM Function Examples

1. Calculate the total of both tests to determine the Final Mark
Final Mark 150

2. Calculate the Test 1 marks for the student whos names start with G and N
G+N 49
 AVERAGE Function (=AVERAGE)
A function in Excel calculates the arithmetic mean of a group of numbers. It’s widely used to find the central tendency of a dataset, which helps in understanding the overall trend or typical value
within a range of data. for summarizing data, performing calculations, and analyzing numerical datasets.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
AVERAGE = AVERAGE(range)

Using the AVERAGE Function

1. A1:A5 Average of a Range of Cells
2. A1, A3, A5 Average of Specific Cells

Key Points
The AVERAGE function ignores empty cells, text, or logical values (TRUE/FALSE) in the specified range.
Handling Non-Numeric Data
However, if a cell contains an error (e.g., #DIV/0!), the function will return an error.
Dividing by Zero If all the cells in the range are empty or contain non-numeric data, the AVERAGE function returns #DIV/0!.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.
Example Scenario
Using the AVERAGE Function Examples Name Test 1 Test 2 Final Mark
1. Calculate the average for Test 2.
AVG Test 2 71 Ginger Beard 25 65 90
Harvey Delacruz 50 95 145
2. Calculate the Average for Harvery and Neil on Test 1 Megan Wagner 42 85 127
H + N AVG 37 Marion Spears 10 50 60
Neil Miles 24 60 84
 MAX Function (=MAX)
A function in Excel is used to find the maximum value within a range of numbers. It's a useful function for determining the highest value in a dataset, such as identifying the top score,
the highest sales number, or the peak value in a series of data.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
MAX = MAX(range)

Using the MAX Function

1. A1:A5 Find the Maximum Value in a Range
2. A1, A3, A5 Find the Maximum Value from Specific Cells

Key Points

Non-Numeric Data The MAX function ignores text, logical values (TRUE/FALSE), and empty cells within the specified range. However, if a cell contains an error (e.g.,
#DIV/0!), it will cause the function to return an error.
Handling Errors If all cells are non-numeric or empty, the MAX function returns #VALUE!.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the MAX Function Examples Example Scenario

1. Calculate the higest mark for Test 2 Name Test 1 Test 2 Final Mark
MAX 95
Ginger Beard 25 65 90
2. Based on the females mark, what was the higest mark for Test 1. Harvey Delacruz 50 95 145
Females MAX 42 Megan Wagner 42 85 127
Marion Spears 10 50 60
Neil Miles 24 60 84
 MIN Function (=MIN)
A function in Excel is used to find the minimum value in a range of numbers. It's useful for identifying the lowest value within a dataset, such as the lowest score, the smallest
amount, or the minimum temperature in a series of data.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
MIN = MIN(range)

Using the MIN Function

1. A1:A5 Find the Minimum Value in a Range
2. A1, A3, A5 Find the Minimum Value from Specific Cells

Key Points

Non-Numeric Data The MIN function ignores text, logical values (TRUE/FALSE), and empty cells within the specified range. However, if a cell contains an error (e.g.,
#DIV/0!), it will cause the function to return an error.
Handling Errors If all cells are non-numeric or empty, the MIN function returns #VALUE!.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the MIN Function Examples Example Scenario

1. Calculate the lowest mark for Test 2 Name Test 1 Test 2 Final Mark
MIN 50
Ginger Beard 25 65 90
2. Based on the males mark, what was the higest mark final mark Harvey Delacruz 50 95 145
Males MIN 84 Megan Wagner 42 85 127
Marion Spears 10 50 60
Neil Miles 24 60 84
 COUNT Function (=COUNT)
A function in Excel is used to count the number of cells that contain numeric values ONLY in a specified range. It is particularly useful for counting data entries in numerical form
and for quickly assessing the size of your dataset.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
COUNT = COUNT(range)

Using the COUNT Function

1. A1:A5 Count Numeric Values in a Range
2. A1, A3, A5 Count Numeric Values in Specific Cells

Key Points
Non-Numeric Data The COUNT function only counts cells containing numeric data. It ignores text, logical values (TRUE/FALSE), and empty cells.
Handling Errors Cells with errors or empty cells are not counted.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the COUNT Function Examples Example Scenario

Name Test 1 Test 2 Final Mark
1. Calculate how many students wrote Test 2.
All Student 12 Ginger Beard 15 60 75
Harvey Delacruz 3 57 60
2. Calculate how many cells are in Pink. Megan Wagner 45 68 113
Pink cells 8 Sam Doom 10 50 60
Neil Miles 82 125
Casey Hills 36 61 97
Cesar Flynn 39 75 114
April Golden 22 97 119
Anna Pierce 51 86
Esmeralda Best 14 50 64
Millar Cain 62 107
Andrew Boyce-Jones 32 76 108
 COUNTA Function (=COUNTA)
A function in Excel is used to count the number of cells that contain letters or numbers in a range. Unlike the COUNT function, which only counts cells containing numeric
values, COUNTA includes all cells that contain data, regardless of type—text, numbers, dates, logical values (TRUE/FALSE), or errors.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
COUNTA = COUNTA(range)

Using the COUNTA Function

1. A1:A5 Count Numeric Values in a Range
2. A1, A3, A5 Count Numeric Values in Specific Cells

Key Points
Non-Numeric Data The COUNTA function only counts cells containing numeric data. It ignores text, logical values (TRUE/FALSE), and empty cells.
Handling Errors Cells with errors or empty cells are not counted.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected. Example Scenario
Name Test 1 Test 2 Final Mark
Using the COUNTA Function Examples
Ginger Beard 15 60 75
1. Based on column L, how many students wrote both tests? Harvey Delacruz 3 57 60
12 Megan Wagner 45 68 113
Sam Doom 10 50 60
2. Calculate how many people names start with A. Neil Miles 43 82 125
3 Casey Hills 36 61 97
Cesar Flynn 39 75 114
April Golden 22 97 119
Anna Pierce 35 51 86
Esmeralda Best 14 50 64
Millar Cain 45 62 107
Andrew Boyce-Jones 32 76 108
 COUNTBLANK Function (=COUNTBLANK)
A function in Excel is used to count the number of empty cells within a specified range.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
COUNTBLANK = COUNTBLANK(range)

Using the COUNTBLANK Function

1. A1:A5 This is the range of cells you want to check

Key Points
The COUNTBLANK function only counts cells that are truly empty. Cells with formulas that return an empty string (like "") are not
Empty Cells considered blank.
Non-Blank Cells Cells with text, numbers, or formulas that return non-
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the COUNTBLANK Function Examples Example Scenario

Name Test 1 Test 2 Final Mark
1. Count the amount of students that did not write Test 1
4 Ginger Beard 15 60 75
Harvey Delacruz 57 60
Megan Wagner 45 68 113
Sam Doom 10 50 60
Neil Miles 82 125
Casey Hills 61 97
Cesar Flynn 39 75 114
April Golden 22 97 119
Anna Pierce 35 51 86
Esmeralda Best 50 64
Millar Cain 45 62 107
Andrew Boyce-Jones 32 76 108
 COUNTIF Function (=COUNTIF)
A function in Excel is used to count the number of cells in a range that meet a specific condition or criterion.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
COUNTIF = COUNTIF(range;criteria) or = COUNTIF(range,criteria)

Using the COUNTIF Function

A1:A5;"5"
1. A1:A5 The range of cells you want to apply the criteria to.
"5" The condition that you want to count cells based on. This can be a number, text, logical expression, or a function. (USING THE ACTUAL CRITERIA NAME WITH QUOTATION MARKS)

A1:A5;D7
2. A1:A5 The range of cells you want to apply the crit
"5" The condition that you want to count cells based on. This can be a number, text, logical expression, or a function. (USING THE ACTUAL CELL)

Key Points
COUNTIF supports two wildcards
Wildcards* (asterisk) for any sequence of characters
? (question mark) for a single character.
Logical Operators For conditions like greater than, less than, or not equal to, you need to use operators within quotation marks (e.g., ">100" or "<50").
Text Criteria If the criteria is text, it should be enclosed in quotation marks (e.g., "Apple").
Case Sensitivity The COUNTIF function is not case-sensitive. For example, it will count "apple" and "Apple" as the same.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.
Example Scenario [A]
Using the COUNTIF Function Examples Name Test 1 Test 2 Final Mark

1. Using Example Scenario [A] How many students received a 60 for their Final Mark? Ginger Bea 25 65 90
1 Harvey Del 50 95 145
Megan Wag 42 85 127
Marion Spe 10 50 60

2. Using Example Scenario [B] Calculate how many students in each grade. Example Scenario [B]
Name Grade Book Club Books Read
10 6 Analysis calculation!
T
EC

11 0 1. Name for Criteria was not needed here as there is an autofill needed Ginger Beard 10 Yes 41
RR
CO

12 0 2. No Absolute Referencing range Harvey Delacruz 10 Yes 29

IN

Megan Wagner 11 No 46
Sam Doom 10 Yes 46
Neil Miles 11 No 26
10 6 Analysis calculation! Casey Hills 11 No 29
T
EC

11 6 1. Name for Criteria was not needed here as there is an autofill needed Cesar Flynn 11 Yes 39
RR
CO

12 4 2. Absolute Referencing range when autofilling! April Golden 10 No 21

Anna Pierce 11 Yes 44
3. How many students read more 25 books? Esmeralda Best 10 Yes 15
11 Millar Cain 12 Yes 23
 Jupiter Reeves 12 Yes 16
4. How many students names start with the letter A? Marnie Matthews 12 No 31
3 Bridgette Nicholson 10 Yes 10
Jana Shepard 11 No 29
Andrew Boyce-Jones 12 Yes 28
 RAND Function (=RAND)
A function in Excel is used to generate a random decimal number between 0 (inclusive) and 1 (exclusive). It’s useful for tasks that require random sampling or random number generation
within a defined range.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
RAND = RAND()

Using the RAND Function

1. () To generate a random decimal number bet

Key Points
calculations. If you want to keep the generated number static, you should copy the cell with RAND and then paste its
Dynamic Updates value (using "Paste Special" and selecting "Values").
Decimal Values RAND always produces decimal values between 0 and 1. To work with integer values or a different range, you'll need additional calculations or functions.
Use Cases RAND is often used in simulations, random sampling, or generating random test data.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the RAND Function Examples

1. Add a random decimal number

0.06868
 RANDBETWEEN (=RANDBETWEEN)
A function in Excel is used to generate a random integer between two specified values, inclusive. This function is particularly useful when you need random whole numbers within a
specific range.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
ROUNDBETWEEN = ROUNDBETWEEN(range)

Using the RANDBETWEEN Function

1. bottom The smallest integer that RANDBETWEEN can return.
2. top The largest integer that RANDBETWEEN can return.

Key Points

Dynamic Updates Similar to RAND, the value generated by RANDBETWEEN will recalculate every time the worksheet is updated or when you press F9 to refresh calculations. To keep a generated number
static, copy the cell and paste its value (using "Paste Special" and selecting "Values").
Inclusion of Boundaries Cells with errors or empty cells are not counted.
Use Cases RANDBETWEEN is ideal for tasks such as simulations, random sampling, or generating random data within a defined range
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the RANDBETWEEN Function Examples Example Scenario [B]

Name 1. 2. 3.
1. Give each person a random number between 20 and 100
Ginger Beard 36 121 366
2. Give each person a random number between 90 and 150 Harvey Delacruz 80 108 298
Megan Wagner 55 149 239
3. Give each person a random number between 200 and 400 Sam Doom 79 125 221
Neil Miles 100 104 263
Whenever something gets added to a sheet that has a Randbetween Casey Hills 68 103 343
NOTE:
it will constantly change. DO NOT FREAK OUT!! Cesar Flynn 84 132 355
April Golden 97 144 210
Anna Pierce 60 106 373
Esmeralda Best 20 122 322
Millar Cain 76 98 300
Jupiter Reeves 69 117 203
Marnie Matthews 97 149 345
Bridgette Nicholson 96 93 339
Jana Shepard 46 122 305
Andrew Boyce-Jones 79 124 243
 MODE Function (=MODE )
A function in Excel is used to find the most frequently occurring number (or mode) in a set of values. This function helps in statistical analysis to determine the value that appears
most often in a given range of data.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
MODE = MODE (range)

Using the MODE Function

1. A1:A5 This will return the number that appears most frequently within the specified range.
2. 2; 4; 4;6 ;7 ;8 This will return 4 because 4 is the most frequently occurring number in the set. *Not used often

Key Points
Single Mode If there is a single number that appears most frequently, MODE returns that number.
Multiple Modes If there are multiple numbers with the same highest frequency, MODE will return the lowest of these values.
No Mode If no number repeats, the MODE function will return an error, indicating that there is no mode.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the MODE Function Examples Example Scenario [B]

Name Grade Book Club Books Read
1. Which grade participated the most in the reading challenge
10 Ginger Bea 10 Yes 41
Harvey Del 10 Yes 29
Megan Wag 11 No 46
Sam Doom 10 Yes 46
Neil Miles 11 No 26
Casey Hills 11 No 29
Cesar Flyn 11 Yes 39
April Golde 10 No 21
Anna Pierc 11 Yes 44
Esmeralda 10 Yes 15
Millar Cain 12 Yes 23
Jupiter Ree 12 Yes 16
Marnie Mat 12 No 31
Bridgette N 10 Yes 10
Jana Shepa 11 No 29
Andrew Boy 12 Yes 28
 MEDIAN Function (=MEDIAN)
A function in Excel is used to find the middle value in a set of numbers when the numbers are sorted in ascending order. If the dataset has an even number of observations, MEDIAN calculates the
average of the two middle numbers. This function is useful for statistical analysis as it provides a measure of central tendency that is less sensitive to outliers than the mean.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
MEDIAN = MEDIAN(range)

Using the MEDIAN Function

1. A1:A5 This will return the median value of the nu
2. 1; 3; 3 ;6 ;7 ;8 ;9 The median here is 6 because it is the midd

Key Points
Sorting The MEDIAN function automatically sorts the numbers in ascending order before determining the median.
Handling Even and Odd Sets For odd numbers of observations, the median is the middle number. For even numbers of observations, it is the average of the two middle numbers.
Use with Ranges and Arrays MEDIAN can handle both individual numbers and ranges of cells. It can also process multiple ranges or arrays.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the MEDIAN Function Examples Example Scenario [B]

Name Grade Book Club Books Read
1. What in the median of the books reads?
29 Ginger Beard 10 Yes 41
Harvey Delacruz 10 Yes 29
Megan Wagner 11 No 46
Sam Doom 10 Yes 46
Neil Miles 11 No 26
Casey Hills 11 No 29
Cesar Flynn 11 Yes 39
April Golden 10 No 21
Anna Pierce 11 Yes 44
Esmeralda Best 10 Yes 15
Millar Cain 12 Yes 23
Jupiter Reeves 12 Yes 16
Marnie Matthews 12 No 31
Bridgette Nicholson 10 Yes 10
Jana Shepard 11 No 29
Andrew Boyce-Jones 12 Yes 28
 TODAY Function (=TODAY)
A function in Excel is used to insert the current date into a cell. It dynamically updates to reflect the current date whenever the spreadsheet is recalculated or reopened.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
TODAY = TODAY()

Using the TODAY Function

1. () This will show the date in the format set by your spreadsheet settings, such as 20/08/2024
2. Taught in Grade 12

Key Points
Dynamic Updates The date returned by TODAY updates automatically every day. If you open the spreadsheet tomorrow, it will show the new current date.
The format of the date displayed depends on your spreadsheet's date settings, but it typically defaults to a common date format like MM/DD/YYYY or DD/MM/YYYY. This is
Date Formatting found in more options of the NUMBER GROUP in the HOME TAB.
Use in Calculations TODAY can be used in calculations for time-based functions, such as determining how many days have passed or remaining until a certain date
Handling Errors Cells with errors or empty cells are not counted.
Comparison Useful for comparing dates or determining whether a date is before or after the current date.
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the TODAY Function Examples

1. Add Todays Date!

9/20/2025

2. Add todays date in the following format. Wednesday, 21 August 2024.

Saturday, September 20, 2025
 NOW Function (=NOW)
A function in Excel is used to insert the current date and time into a cell. It provides both the date and the time, updating dynamically to reflect the current moment
whenever the spreadsheet is recalculated or reopened.

Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
NOW = NOW()

Using the NOW Function

1. () This will show the current date and time in the format set by your spreadsheet settings, such as 21/08/2024 14:35.
2. Taught in Grade 12
3. Taught in Grade 12
4. Taught in Grade 12

Key Points

Dynamic Updates The NOW function updates automatically to reflect the current date and time. This can be useful for time-stamping records or calculations that depend on the current
moment.
The display format of the date and time depends on your spreadsheet's date and time settings. You can adjust the format by changing the cell's format settings to show
Date Formatting the date and time in the desired format. This is found in more options of the NUMBER GROUP in the HOME TAB.
Use in Calculations NOW can be used in various time-based calculations, such as determining elapsed time, scheduling, or time-sensitive deadlines
Handling Errors Cells with errors or empty cells are not counted.
Combination TODAY: If you only need the date without the time, you can use TODAY. If you need both date an
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected.

Using the NOW Function Examples

1. What is the date and time today?

9/20/2025 23:19
 SMALL Function (=SMALL)
A function in Excel is used to find the nth smallest value in a data set. It is useful for analyzing data by retrieving specific smallest values from a range or array.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
SMALL = SMALL(array; k)

Using the SMALL Function

1. A1:A5;1 This will return the smallest value in the range.
2. A1:A5;2 This will return the second smallest value in the range.
3. 10:20; 5; 15; 3) This will return 15, the third smallest number in the set.
Find the Smallest Value in a Filtered Range: If you have a range with some blank cells or non-numeric values, SMALL ignores these when
4. (C1:C10;1) determining the smallest values. C1 having blank cells Example Scenario [B]
Name Grade Book Club Books Read
Key Points
Sorting The SMALL function does not sort the data. It directly retrieves the specified smallest value based on the rank provided by k. Ginger Beard 10 Yes 41
If k is greater than the number of numeric values in the array, the function will return an error. For example, =SMALL(A1:A10, 11) will produce an error 10 Yes 29
Errors if there are fewer than 11 numeric values in the range. Harvey Delacruz
Handling Non-Numeric Values SMALL ignores non-numeric values and blanks when determining the smallest values. Megan Wagner 11 No 46
Use with Arrays SMALL can work with arrays of values, not just ranges of cells. Sam Doom 10 Yes 46
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected. Neil Miles 11 No 26
Casey Hills 11 No 29
Using the SMALL Function Examples Cesar Flynn 11 Yes 39
April Golden 10 No 21
1. Calculate the lowest amount of books read? Anna Pierce 11 Yes 44
Both options are correct!
Esmeralda Best 10 Yes 15
However it is important to note the mark
Option 1 10 allocation when answering a question that can Millar Cain 12 Yes 23
Option 2 10 have two different functions as an answer. Jupiter Reeves 12 Yes 16
Marnie Matthews 12 No 31
2. What is the second least books read? Bridgette Nicholson 10 Yes 10
15 Jana Shepard 11 No 29
Andrew Boyce-Jones 12 Yes 28
 LARGE Function (=LARGE)
A function in Excel is used to find the nth largest value in a dataset. It is the counterpart to the SMALL function and is useful for retrieving specific largest values from a range or array.

Components of a Formula
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a formula rather than plain text or a number.
LARGE = LARGE(array; k)

Using the LARGE Function

1. A1:A5;1 This will return the largest value in the range.
2. A1:A5;2 This will return the second largest value in the range.
3. 10:20; 5; 15; 3) This will return 10, the third largest number in the set.

4. (C1:C10;1) If your range contains blank cells or non-numeric values, LARGE will ignore these when determining the largest values. C1 having blank cells
Example Scenario [B]
Key Points Name Grade Book Club Books Read
Sorting The LARGE function does not sort the data. It directly retrieves the specified largest value based on the rank provided by k.

If k is greater than the number of numeric values in the array, the function will return an error. For example, =LARGE(A1:A10, 11) will produce an 10 Yes 41
Errors error if there are fewer than 11 numeric values in the range. Ginger Beard
Handling Non-Numeric Values LARGE ignores non-numeric values and blanks when determining the largest values. Harvey Delacruz 10 Yes 29
Use with Arrays LARGE can work with arrays of values, not just ranges of cells. Megan Wagner 11 No 46
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as expected. Sam Doom 10 Yes 46
Neil Miles 11 No 26
Using the LARGE Function Examples Casey Hills 11 No 29
Cesar Flynn 11 Yes 39
1. Calculate the highest amount of books read? April Golden 10 No 21
Both options are correct!
Anna Pierce 11 Yes 44
However it is important to note the mark
Option 1 46 allocation when answering a question that can Esmeralda Best 10 Yes 15
Option 2 46 have two different functions as an answer. Millar Cain 12 Yes 23
Jupiter Reeves 12 Yes 16
2. What is the second least books read? Marnie Matthews 12 No 31
44 Bridgette Nicholson 10 Yes 10
Jana Shepard 11 No 29
Andrew Boyce-Jones 12 Yes 28
 IF Function
A function in Excel is used to sum values in a range based on a single condition or criterion. This
summing sales figures for a particular region or su
Components of a Function
Equal Sign = Every formula starts with an equal sign. This tells Excel that what follows is a for
IF = IF(logical_test; value_if_true; value_if_false)

Using the SUMIF Function

A1; "True"; "False"

A1 logical_te The condition you want to test. This is a statement that evaluates to T
1.
"True" value_if_t The value to return if the condition is TRUE
"False" value_if_f The value to return if the condition is FALSE.

Key Points
= Equal to
<> Not equal to
> Greater than
Logical Operators
< Less than
>= Greater than or equal to
<= Less than or equal to
Error Handling Use IFERROR to handle errors in formulas
Formula Bar You can view and edit the formula in the formula bar to ensure it’s calculating as

Using the IF Function Examples

"Pass" if the student achieved a score of 25 or higher; otherwise, display
1. "Fail."

Example Scenario
Name Test 1 PASS/FAIL Test 2 PASS/FAIL Final Mark
50 Pass 100
Ginger Bea 15 Fail 60 75
Harvey Del 3 Fail 57 60
Megan Wag 45 Pass 68 113
Sam Doom 10 Fail 50 60
Neil Miles 43 Pass 82 125
Casey Hills 36 Pass 61 97
Cesar Flyn 39 Pass 75 114
April Golde 22 Fail 97 119
Anna Pierc 35 Pass 51 86
Esmeralda 14 Fail 50 64
Millar Cain 45 Pass 62 107
 Andrew Boy 32 Pass 76 108
"Pass" if the student achieved a score of 50 or higher; otherwise, display
2. "Fail."

Example Scenario
Name Test 1 PASS/FAIL Test 2 PASS/FAIL Final Mark
50 Pass 100
Ginger Bea 15 Fail 60 Pass 75
Harvey Del 3 Fail 57 Pass 60
Megan Wag 45 Pass 68 Pass 113
Sam Doom 10 Fail 50 Pass 60
Neil Miles 43 Pass 82 Pass 125
Casey Hills 36 Pass 49 Fail 97
Cesar Flyn 39 Pass 75 Pass 114
April Golde 22 Fail 97 Pass 119
Anna Pierc 35 Pass 51 Pass 86
Esmeralda 14 Fail 50 Pass 64
Millar Cain 45 Pass 62 Pass 107
Andrew Boy 32 Pass 76 Pass 108
 IF Function (=IF)
ondition or criterion. This function is useful when you need to add up numbers that meet specific criteria, such a
r a particular region or summing expenses over a certain amount.

that what follows is a formula rather than plain text or a number.

or = IF(logical_test, value_if_true, value_if_false)

ement that evaluates to TRUE or FALSE.

ensure it’s calculating as expected.

et specific criteria, such as