CHAPTER

09 Spreadsheets

334
335
336
 9.1 Understanding Spreadsheets

LEARNING OUTCOMES
3.1.1 Use appropriate relative, absolute and mixed cell references in formulas so they give the
correct results when copied to similar cells in a table.

ERMS
A spreadsheet is an electronic worksheet used to KEY T
manage and manipulate data arranged in columns
and rows. Cell
The space for data in a particular
Columns are labelled with letters and rows with column and row of a spreadsheet
numbers. The rectangular space located in a
particular column and row is called a cell. Spreadsheet
An electronic worksheet used to
Figure 9.1 shows a typical spreadsheet with a grid of manage and manipulate data arranged
cells that starts from column A and row 1 at the top- in columns and rows
left corner and extends rightwards and downwards.

LEARNING OUTCOMES
Figure 9.1 A spreadsheet starts from cell A1 at the top-left corner and extends rightwards and downwards

Each cell can be double-clicked to enter a number or text value. Most spreadsheet applications do not
distinguish between integers and floating-point numbers. To force a cell to treat the input as text, start it
with an apostrophe ('), e.g., '20-12.

337
 U
DID YO
?
KNO W
Spreadsheet applications such as Microsoft Excel have a “number format” feature that can display
numbers in different formats such as percentages (20.24%), currencies ($20.24) and dates (9/8/65).
To set the number format of a particular cell, right-click on it and select “Format Cells...” from the
context menu.

Figure 9.2 Built-in number formats in a typical spreadsheet application

Note that number formats only determine how such cells are displayed. Internally the cells’ values
are still stored and treated as numbers.

9.1.1 Cell and Range Addresses

ER MS
We often need to refer to the location of a cell or range
of cells in a spreadsheet. For a single cell, we use its
KEY T
cell address,
address which is the cell’s column name (one or Cell address
more letters) followed immediately by its row number. Location name for a cell formed by its
column name and row number

338
For instance, each cell in Figure 9.3 displays its cell address.

Figure 9.3 Cells and their cell addresses

ER MS
When we want to refer to a rectangular range of cells,
we use a range address instead. A range address is the KEY T
top-left cell’s address followed by a colon (:) and the Range address
bottom-right cell’s address. Location name for a rectangular range
of cells formed by the top-left cell’s
Figure 9.4 shows the different range addresses for address, a colon (:) and the bottom-
various selections of cells in a spreadsheet. For instance, right cell’s address
“A1:C3” is the range address for a 3-by-3 range of cells in
the top-left corner of a spreadsheet.

Figure 9.4 Range addresses

339
 9.1.2 Formulas

In Python, calculations are performed by providing the computer with step-by-step instructions. In
spreadsheets, however, calculations are performed by telling the computer how cell values are related to
each other. This is done by entering a formula into a cell instead of a constant. In spreadsheets, a formula
is the result of entering an equals sign (=) followed by an expression that can contain functions, operators,
literal values as well as values from other cells.

To reveal the cells which have formulas, press ER MS

the Ctrl key followed by the grave accent (`) KEY T
key, i.e., Ctrl + `. The grave accent key is usually
located to the left of the digit 1 on the keyboard. Formula
Instead of showing the calculated result, cells A way to perform calculations on the data in a
with formulas will now show an equals sign spreadsheet; may contain functions, operators as
followed by the cell’s formula. To show the well as cell and range references
calculated values once more, press Ctrl + `
again.

For example, in Figure 9.5, cell A1 has a constant with a value of 2017 while cell A2 has a formula that
calculates the sum of 2000 and 17. Cell A3, on the other hand, has a constant with a text value of “2000+17”
and does not have a formula. This is because the expression “2000+17” was entered in cell A3 without an
equals sign in front.

Figure 9.5 Example of formulas and constants

Formulas use either literals or cell references to represent

values. The format for number literals is similar to the
ER MS format of int and float literals in Python while text
KEY T literals must be enclosed in double quotes (single quotes
are not allowed).
Cell reference
Description of a cell that can be On the other hand, cell references describe how cell values
used in a spreadsheet formula are related to each other by specifying the column and
row of another cell. There are two types of cell references:
relative cell references and absolute cell references.

340
 9.1.3 Relative Cell References

To make a relative cell reference, use a cell address or range address directly in a formula. For instance, in
Figure 9.6, cell A5 uses relative cell references to calculate the product of the values in cells A1, A2 and A3.

Figure 9.6 Using relative cell references

An important feature of relative cell references is that when a formula from one cell is copied to another cell,
all the relative cell references in the copied formula are changed so that they maintain the same relative
positions to the new cell.

In Figure 9.6, when cell A5 is copied to cell B5, the resulting formula in cell B5 automatically has its relative
cell references change from A1, A2 and A3 to B1, B2 and B3 respectively. This allows repetitive calculations
or relationships between cell values to be set up quickly by copying the formula from one cell to multiple
cells.

Suppose Bala is helping to manage the costs for the party described at the beginning of this chapter using
the spreadsheet shown in Figure 9.7. In row 2, the unit cost of balloons is $0.80 (cell D2), so the total cost for
balloons is $0.80 (cell D2) × 3 (cell B2) = $2.40.

Bala notices that the formula to calculate the total cost for subsequent rows follows the same pattern of
multiplying the quantity in column B by the unit cost in column D. Hence, instead of entering the formula
for rows 3 to 10 manually, he enters the formula “=B2*D2” into cell E2 and copies it into cells E3 to E9.

Figure 9.7 Using relative cell references to calculate the total cost of party items

341
 In Figure 9.7, the two relative cell references of B2 and D2 are automatically changed to B3 and D3 for
row 3, B4 and D4 for row 4, B5 and D5 for row 5, and so on for the subsequent rows. Note that this occurs
automatically to all relative cell references in the copied formula.

U
DID YO
?
KNO W
An alternative to copying and pasting formulas manually is to use the “fill” feature of a spreadsheet.
Select the cell you wish to copy from. Notice the square on the bottom-right corner of the cell. This is
called the fill handle. Drag this square to cover the adjacent cells that you wish to paste the formula
into.

Figure 9.8 Dragging the fill handle (indicated by the small square) to copy C2 into C3:C9

9.1.4 Absolute Cell References

Absolute cell references are used when we do not want the cell references in formulas to change when
copied to other cells. To make an absolute cell reference, type a dollar sign ($) before the column letter or
row number that should not change. This indicates that the particular column letter or row number will not
be automatically changed when the formula is copied into other cells. For instance, in Figure 9.9, “$A$1” is
an absolute cell reference that means the cell reference to cell A1 does not change even when the formula
is copied to another cell.

Figure 9.9 Using absolute cell references

342
Suppose Alex is helping to track the contributions made by the attendees for the class party using the
spreadsheet shown in Figure 9.10. The amount each attendee needs to contribute is $12.00 (cell C1).

Figure 9.10 Spreadsheet to track contributions from class party attendees

The number of additional family members attending for Aisha is 1 (cell B4), so the total contribution by
Aisha and her family is 2 (cell B4 + 1) × $12.00 (cell C1) = $24.00.

Without absolute cell references, Alex may enter the formula “=(B4+1)*C1” into cell C4. However,
Figure 9.11 shows that copying this formula into cells C5 to C17 will result in “=(B5+1)*C2” for row 5,
“=(B6+1)*C3” for row 6, “=(B7+1)*C4” for row 7, and so on. This is incorrect as the formulas in these cells
should only refer to the cell C1.


Figure 9.11 The cell reference to C1 should not change when copied to C5:C17

343
 9.1.5 Mixed Cell References

To prevent the row number 1 from changing in the cell reference “C1”, Alex should use the absolute cell
reference “C$1” instead. The dollar sign in front of “1” indicates that it will not be automatically changed
when the formula is copied into other cells. Hence, Alex can enter the formula “=(B4+1)*C$1” into cell C4
before copying the formula into cells C5 to C17, as shown in Figure 9.12. This results in the correct formulas
“=(B5+1)*C$1” for row 5, “=(B6+1)*C$1” for row 6, “=(B7+1)*C$1” for row 7, and so on. Note that since
the column letter C should not change as well, he can also use “$C$1”.

Figure 9.12 Using C$1 prevents the cell reference’s row number from being changed when copied to other cells

You can think of the dollar sign as a pin or

lock that prevents the column letter or row !"#$%&'"()"&$*++",&
number in the cell reference from being ,-'+...
changed when copied to other cells.

! " *!*"
U
DID YO
?
KN OW
# $% #*$%
& '( *&'(
While editing a cell reference, you can
press F4 to cycle through all the possible
relative and absolute versions of the .../01&'"()"&2)""&1-&
345+6"7
) + )+
cell reference. For instance, pressing
F4 while editing the formula “=C2” will
cycle through “=$C$2”, “=C$2”, “=$C2”
and back to “=C2” again.
Figure 9.13 The dollar sign acts like a pin to prevent the column letter
or row number from being changed

344
 9.1.6 Range References

Cell references only refer to one cell at a time. However, some functions may refer to a rectangular range
of cells. To do this, we use a range reference instead, which is the cell reference for the top-left cell in the
range followed by a colon (:) and the cell reference for the bottom-right cell in the range.

Like cell references, there are also relative and ER MS

absolute range references. For instance, “A1:C3” KEY T
is a relative range reference while “$A$1:$C$3”
is an absolute range reference. Range reference
Description of multiple cells that can be used in
a spreadsheet formula

9.1.7 Automatic Recalculation

The advantage of using cell and range references is that the spreadsheet can automatically recalculate the
formulas in all affected cells if any referenced cells are updated.

For instance, in Bala’s situation in Figure 9.14, updating the unit cost for balloons (cell D2) from $0.80 to
$0.50 will automatically recalculate the total cost for balloons (cell E2), which would change from $2.40
to $1.50. This is because the spreadsheet already understands the relationship between cells D2 and E2.

Figure 9.14 Changing D2 causes E2 to be automatically recalculated

Similarly, in Alex’s situation in Figure 9.15, updating the amount each attendee needs to contribute (cell C1)
from $12.00 to $10.00 will automatically recalculate all the cells from C4 to C17 using the new amount.
Once again, this is because the spreadsheet already understands how cell C1 is related to the calculations
in range C4:C17.

345
 Figure 9.15 Changing C1 causes C4:C17 to be automatically recalculated

QUICK
E CK 9 .1
CH
1. The spreadsheet in Figure 9.16 produces different multiplication tables.

Figure 9.16 Multiplication table spreadsheet

346
QUICK
E C K 9.1
CH
When Ctrl-` is pressed, the spreadsheet looks like this.

Figure 9.17 Multiplication table with formulas shown

a) Suggest how the equals sign may be entered as text in the range D4:D15.
b) Identify the cell(s) which use(s) absolute cell references.
c) Identify the cell(s) which would be recalculated if cell A4 is changed.
d) Identify the cell(s) which would be recalculated if cell C1 is changed.

347
 QUICK
E CKK 9
9 .11
.
HEC
CCH
2. The spreadsheet in Figure 9.18 calculates speeds in units of m/s.

Figure 9.18 Spreadsheet to calculate speeds

Suggest a formula for cell C2 that can be copied to range C3:C6 in order to complete the spreadsheet correctly.

9.2 Logical Operators and Functions

LEARNING OUTCOMES
3.2.1 Use logical functions to perform:
• Logical OR, AND or NOT
• Selection between two values based on a third logical value

Cells can contain logical values (i.e.,

TRUE or FALSE) which are similar to Operator Meaning
Boolean values in Python. Besides
using the explicit values of TRUE and < Less than
FALSE, logical values can also be
formed using the operators in Table <= Less than or equal to
9.1 to compare two values. While most
of these operators are the same as > Greater than
those in Python, the equality and non-
equality operators are not. >= Greater than or equal to

= Equal to (equality)

<> Not equal to (non-equality)

Table 9.1 Comparison operators

348
The comparison for text values is case-insensitive by default (e.g., ="a"="A" results in TRUE). In addition,
the <, <=, > and >= operators should only be used to compare values of the same type. Comparing values
of different types (e.g., ="1"<2) may result in unexpected and misleading results.

U
DID YO
?
KNOW
If needed, case-sensitive comparison of text values can be performed using the EXACT() function.
For example, =EXACT("a", "A") results in FALSE.

When the <, <=, > and >= operators are used to compare two values of different types, the actual
values are ignored and instead the two types are compared based on the following order:

Number < Text < Logical Value < Error Value

This explains why a formula such as ="1"<2 results in FALSE.

In Python, we typically use the logical operators (and, or, not) to combine Boolean values and make
decisions using if-else statements. In spreadsheets, however, we use logical functions instead, as
described in Table 9.2.

Function Syntax Description

AND() =AND(logical1, logical2, …) Returns TRUE when all the given logical
• Only the argument logical1 values (logical1, logical2, etc.) or
is compulsory. The arguments values in the given range/cell references
from logical2 onwards are are TRUE; otherwise, returns FALSE.
optional.
• logical1, logical2, etc. can
be either logical values or range/
cell references.

IF() =IF(logical_test, Returns value_if_true (i.e., the second

value_if_true, argument) when logical_test is TRUE
value_if_false) and value_if_false (i.e., the third
argument) when logical_test is FALSE.

NOT() =NOT(logical) Returns TRUE when logical is FALSE and

FALSE when logical is TRUE.

349
 Function Syntax Description

OR() =OR(logical1, logical2, …) Returns TRUE when any of the given

• Only the argument logical1 logical values (logical1, logical2,
is compulsory. The arguments etc.) or values in the given range/cell
from logical2 onwards are references is TRUE; otherwise, returns
optional. FALSE.
• logical1, logical2, etc. can
be either logical values or range/
cell references.

Figure 9.19 shows a spreadsheet that tracks the attendees for the class party. Suppose Bala would like to fill
column C with information that indicates whether each classmate is bringing more than three additional
family members. To do this, he could enter “=B2>3” into C2 and copy this formula into C3:C15.

Figure 9.19 Using comparison operators to produce logical values

In this case, using the greater than (>) comparison operator produces a logical value that is either TRUE or
FALSE. Now, suppose Bala wishes for the column to use “Yes” and “No” instead of “TRUE” and “FALSE”. He
could use the IF() function to accomplish this, as shown in Figure 9.20.

350
 Figure 9.20 Using IF() to return different values based on a condition

As explained in Table 9.2, the IF() function returns the second argument if the first argument is TRUE.
Otherwise, it returns the third argument instead.

Table 9.3 shows some examples of how IF(), AND(), OR() and NOT() may be used to determine and
display different information about each classmate. In each of these examples, note that the formula
quoted is for the student in row 2 only (i.e., Aisha) and should be copied into rows 3 to 15 to show the same
information for all classmates.

Example Information to display Formula row 2

1 Whether each classmate =IF(AND(B2>=2, B2<=4), "Yes", "No")

has between 2 to 4 (both This formula returns “Yes” when the number
inclusive) additional of additional family members attending is both
family members greater than or equal to 2 and less than or equal
attending to 4. Otherwise, it returns “No”.

Result must be either

“Yes” or “No” only

2 Whether each classmate

has less than 2 or more Option A
=IF(OR(B2<2, B2>4), "Yes", "No")
than 4 additional family
members attending This formula returns “Yes” when the number of
additional family members attending is either less
Result must be either than 2 or greater than 4.
“Yes” or “No” only Otherwise, it returns “No”.

351
 Example Information to display Formula row 2

2 Option B
=IF(AND(B2>=2, B2<=4), "No", "Yes")
Alternatively, since the information to display is
exactly the opposite of Example 1, we could also swap
the responses of “Yes” and “No” from the formula
given in Example 1.

Option C
=IF(NOT(AND(B2>=2, B2<=4)), "Yes", "No")
We could also use the NOT() function to negate the
condition in the formula for Example 1.

3 Whether each classmate =IF(B2<3, "Less than 3", IF(B2=3,

has less than, equal to or "Equal to 3", "More than 3"))
more than 3 additional This formula uses a nested IF() function.
family members If the number of additional family members attending
attending is less than 3, the formula returns “Less than 3”.
Otherwise, it performs another test for whether the
Result must be either number is equal to 3. If so, it returns “Equal to 3”.
“Less than 3”, Otherwise, it returns “More than 3”.
“Equal to 3” or (Besides this formula, there are multiple alternative
“More than 3” options to achieve the same result.)

QUICK
E C K 9.2
CH
1. Predict the result of the following formulas:

a) =2024="2024"
b) =NOT(2024<>"2024")
c) =AND(19<65, 20>=20, 20<=50)
d) =AND(TRUE, OR(FALSE, FALSE))
e) =OR(TRUE, AND(FALSE, FALSE))
f) =IF("Apple"<>"apple", "Case-sensitive", "Case-insensitive")

2. The following spreadsheet is used to track the number of tickets for an event ordered by students in a
class. Students who order several tickets equal to or greater than the value in cell C1 get a free gift, which is
supposed to be indicated by “Y” or “N” in C4:C17.

352
QUICK 2
C K 9.
CHE

Figure 9.21 Ticket orders spreadsheet

Suggest a formula for cell C4 that can be copied to range C5:C17 in order to complete the spreadsheet correctly.

3. The formulas below are two ways of comparing the value in cell A1 with the number 3. They both return a text
value of “Less than 3”, “Equal to 3” or “Greater than 3”.

=IF(A1<3, "Less than 3", IF(A1=3, "Equal to 3", "Greater than 3"))
=IF(A1<3, "Less than 3", IF(A1>3, "Greater than 3", "Equal to 3"))

Suggest two other formulas that can achieve the same result by rearranging the IF() functions, arguments
and/or changing the conditions.

353
 9.3 Mathematical and Statistical Operators and Functions

LEARNING OUTCOMES
3.2.2 Use mathematical and statistical operators and functions to perform:
• Addition, subtraction, multiplication, division, modulo or exponentiation
• Rounding (normal, up, down)
• Calculation of square roots
• Calculation of sums (normal, with condition)
• Calculation of average (normal, with condition)
• Calculation of median, mode, minimum value or maximum value
• Calculation of a value’s rank (ascending, descending)
• Calculation of n-th largest or smallest value
• Counting of values (numbers only, blank only, non-blank only, with condition)
• Generation of random numbers

Table 9.4 shows the arithmetic operators that can

be used to perform mathematical calculations in Operator Meaning
a spreadsheet. (Note that while most of these
operators are the same as those in Python, + Addition
there is no percent operator in Python and the
exponentiation operator is different. In Python, - Subtraction or Negation
the % symbol is instead used to perform the
modulus or remainder operation.) * Multiplication
The percent operator converts the number that / Division
comes before it into a percentage by dividing it
by 100. For example, the result of “=5%*20” is % Percent
the same result as “=5/100*20”, which is 1.
^ Exponentiation
Table 9.5 shows some common spreadsheet
functions for performing mathematical
calculations and generating random numbers. Table 9.4 Arithmetic operators

Function Syntax Description

CEILING.MATH() =CEILING.MATH(number) Returns number rounded up to the

Example: nearest whole number.
=CEILING.MATH(12.34)
This would return 13. (Other uses of this function are not
covered in this textbook.)

FLOOR.MATH() =FLOOR.MATH(number) Returns number rounded down to the

Example: nearest whole number.
=FLOOR.MATH(98.76)
This would return 98. (Other uses of this function are not
covered in this textbook.)

354
 Function Syntax Description

MOD() =MOD(number, divisor) Returns the remainder when number is

Example: divided by divisor.
=MOD(2024, 1000)
This would return 24. Similar to Python’s % operator

POWER() =POWER(number, power) Returns number raised to the exponent

Example: power.
=POWER(2, 8)
This would return 256

QUOTIENT() =QUOTIENT(number, divisor) Returns the quotient when

Example: number is divided by divisor.
=QUOTIENT(2024, 1000)
This would return 2.

RAND() =RAND() Returns a random number greater than or

Example: equal to 0 and less than 1.
=RAND()*2024
This would return a random floating-
point number between 0 (inclusive)
and 2024 (exclusive).

RANDBETWEEN() =RANDBETWEEN(lowest, highest) Returns a random number between the

Example: whole numbers lowest and highest
=RANDBETWEEN(1965, 2024) (both inclusive).
This would return a random integer
between 1965 and 2024 (both
inclusive).

ROUND() =ROUND(number, num_digits) Returns number rounded to num_digits

• Note that num_digits is a decimal place(s).
compulsory argument.
Example:
=ROUND(12.34, 0)
This would return 12.

SQRT() =SQRT(number) Returns the square root of number.

Example:
=SQRT(2025)
This would return 45.

355
 Function Syntax Description

SUM() =SUM(number1, number2, …) Returns the total of the given numbers

• Only the argument number1 (number1, number2, etc.) or numbers in
is compulsory. The arguments the given range/cell references.
from number2 onwards are
optional.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example 1:
=SUM(2, 0, 2, 4)
This would return 8.
Example 2:
=SUM(A1:C3)
This would return the total of all the
numbers in the range A1:C3.

SUMIF() =SUMIF(range, criteria, Returns the total of the numbers in

sum_range) sum_range where the corresponding
• sum_range is an optional value in range is equal to or satisfies the
argument. condition in criteria.
Suppose table A2:C40 is used to
record the test scores of students Any criteria that includes comparison
identified by registration number operators must be enclosed in double-
and group name. The table has quotes.
registration numbers in column A,
group names in column B and scores If sum_range is left out, then range is
in column C. used as sum_range.
Example 1:
=SUMIF(A2:A40, ">6", C2:C40)
This would return the total score of
students with registration numbers
greater than 6.
Example 2:
=SUMIF(B2:B40, "Computing",
C2:C40)
This would return the total score for
the group “Computing”.

Table 9.5 Common math functions

Table 9.6 shows some common spreadsheet functions for performing statistical calculations and gathering
statistical information.

356
 Function Syntax Description

AVERAGE() =AVERAGE(number1, number2, …) Returns the mean or average of the

• Only the argument number1 given numbers (number1, number2,
is compulsory. The arguments etc.) or numbers in the given range/cell
from number2 onwards are references.
optional.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example:
=AVERAGE(2, 0, 2, 4)
This would return 2 (which is the
average of the four numbers).

AVERAGEIF() =AVERAGEIF(range, criteria, Returns the average of the numbers in

average_range) average_range where the corresponding
• average_range is an optional value in range is equal to or satisfies the
argument. condition in criteria.
Suppose table A2:C40 is used to
record the test scores of students Any criteria that includes comparison
identified by registration number operators must be enclosed in double-
and group name. The table has quotes.
registration numbers in column A,
group names in column B and scores If average_range is left out, then range
in column C. is used as average_range.
Example 1:
=AVERAGEIF(A2:A40, ">6",
C2:C40)
This would return the average
score of students with registration
numbers greater than 6.
Example 2:
=AVERAGEIF(B2:B40,
"Computing", C2:C40)
This would return the average score
for the group “Computing”.

COUNT() =COUNT(range1, range2, …) Returns the number of cells that contain

• Only the argument range1 is numbers (including currencies, dates,
compulsory. The arguments times and percentages) in the given range
from range2 onwards are references (range1, range2, etc.).
optional.
Example: Empty cells and cells with text or logical
=COUNT(A1:C3) values are not counted.
This would return the number of
cells that contain numbers (including
currencies, dates, times and
percentages) in the range A1:C3.

COUNTA() =COUNTA(range1, range2, …) Returns the number of non-empty cells

• Only the argument range1 is in the given range references (range1,
compulsory. The arguments range2, etc.).
from range2 onwards are
optional. Empty cells are not counted while cells
Example: with any other data type (e.g., numbers
=COUNTA(A1:C3) and text) are counted.
This would return the number of
non-empty cells in the range A1:C3.

357
 Function Syntax Description

COUNTBLANK() =COUNTBLANK(range) Returns the number of empty cells in

Example: range.
=COUNTBLANK(A1:C3)
This would return the number of
empty cells in the range A1:C3.

COUNTIF() =COUNTIF(range, criteria) Returns the number of cells in range that

Example 1: are equal to or satisfy the condition in
=COUNTIF(A1:A20, ">6") criteria.
This would return the number of cells
in range A1:A20 that have a value Any criteria that includes comparison
greater than 6. operators must be enclosed in double-
Example 2: quotes.
=COUNTIF(B1:B20, "Computing")
This would return the number of cells
in range B1:B20 that match the text
“Computing”.

LARGE() =LARGE(range, k) Returns the k-th largest number in range.

Example:
=LARGE(A1:C3, 2)
This would return the second-largest
number in the range A1:C3.

MAX() =MAX(number1, number2, …) Returns the largest number out of the

• Only the argument number1 given numbers (number1, number2, etc.)
is compulsory. The arguments or the numbers in the given range/cell
from number2 onwards are references.
optional.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example 1:
=MAX(2, 0, 2, 4)
This would return 4.
Example 2:
=MAX(A1:C3)
This would return the largest of all
the numbers in the range A1:C3.

MEDIAN() =MEDIAN(number1, number2, …) Returns the median of the given numbers

• Only the argument number1 (number1, number2, etc.) or numbers in
is compulsory. The arguments the given range/cell references.
from number2 onwards are
optional.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example:
=MEDIAN(2, 0, 2, 4)
This would return 2 (which is the
average of the middle numbers 2 and
2 when the numbers are sorted in
ascending order).

358
 Function Syntax Description

MIN() =MIN(number1, number2, …) Returns the smallest number out of the

• Only the argument number1 is given numbers (number1, number2,
compulsory. The arguments from etc.) or numbers in the given range/cell
number2 onwards are optional. references.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example 1:
=MIN(2, 0, 2, 4)
This would return 0.
Example 2:
=MIN(A1:C3)
This would return the smallest of all the
numbers in the range A1:C3.

MODE.SNGL() =MODE.SNGL(number1, number2, …) Returns the smallest number out of the

• Only the argument number1 is given numbers (number1, number2,
compulsory. The arguments from etc.) or numbers in the given range/cell
number2 onwards are optional. references.
• number1, number2, etc. can be
either numbers or range/cell
references.
Example 1:
=MODE.SNGL(2, 0, 2, 4)
This would return 2 as it is most
repeated value out of all the arguments.
Example 2:
=MODE.SNGL(A1:C3)
This would return the most repeated
number in the range A1:C3.

RANK.EQ() =RANK.EQ(number, range, order) Returns the rank of number in range.

• order is an optional argument. Duplicate numbers are given the same
Example 1: rank and affect the ranks of subsequent
=RANK.EQ(A1, A1:C3) numbers.
This would return the rank of A1 in the
range A1:C3 as if the numbers were If order is zero, the function returns
sorted in descending order. the rank as if range were sorted in
Example 2: descending order.
=RANK.EQ(A1, A1:C3, 1)
This would return the rank of A1 in the If order is non-zero, the function returns
range A1:C3 as if the numbers were the rank as if range were sorted in
sorted in ascending order. ascending order

If order is left out, the function returns

the rank as if range were sorted in
descending order

359
 Function Syntax Description

SMALL() =SMALL(range, k) Returns the kth smallest number in

Example: range.
=SMALL(A1:C3, 2)
This would return the
second-smallest number in the range
A1:C3.

QUICK .3
CH E CK 9

1. The spreadsheet in Figure 9.22 is used to tabulate costs for a class party.

Figure 9.22 Costs for a class party

a) Suggest a formula for cell E2 that can be copied into range E3:E9 in order to complete the spreadsheet.
b) Suggest a formula for cell E10 to calculate the total cost from the subtotals.
c) Predict the value that will be returned for the following formulas:
i) =COUNTIF(B2:B9,">3")
ii) =IF(A6="Drinks", IF(B6<10, "A", "B"), "C")

2. The spreadsheet in Figure 9.23 is used to track the weight readings of students in a class.

Figure 9.23 Weight readings for a class

360
 QUICK
E C K 9.3
CH
a) Suggest a formula for cell C2 that can be copied to range C3:C15 in order to round the weight readings to one
decimal place.
b) Suggest a formula for cell E17 to count the number of students with a rounded weight reading that is less
than 60 kg.
c) Suggest a formula for cell E18 to calculate the range of rounded weight readings.

3. The following spreadsheet contains the timings obtained by participants in a race.

Figure 9.24 Race timings

a) Suggest a formula for cell B2 that can be copied to range B3:B15 in order to calculate the rankings.
b) Suggest a formula for cell B17 to average timing for only the top 10 participants.

9.4 Text Functions

LEARNING OUTCOMES
3.2.3 Use text functions to perform:
• Extraction of characters from the left end, middle or right end of text
• Calculation of text length
• Concatenation of texts
• Calculation of the first position of one text within another text (case sensitive, case
insensitive)

Table 9.4 shows the & operator that can be used to join or concatenate multiple text values to produce a
single text value. (In Python, the + symbol is used instead.)

361
 Spreadsheets also have many text functions to
manipulate and customise text. Table 9.8 shows Operator Meaning
some common functions for finding the length
of some text or extracting a subset of characters & Concatenation
from some text.

Table 9.7 Text operators

Function Syntax Description

CONCAT() =CONCAT(text1, text2, …) Returns the joined combination of the

• Only the argument text1 is given text (text1, text2, etc.) or text in
compulsory. The arguments from the given range/cell references.
text2 onwards are optional.
• text1, text2, etc. can be either
text or range/cell references.
Example 1:
=CONCAT("Hello", "World")
This would return “HelloWorld”.
Example 2:
=CONCAT(A1:C3)
This would return the joined
combination of all the text in the
range A1:C3 using a left-to-right, top-
to-bottom order.

FIND() =FIND(find_text, Returns the location of find_text in

within_text, start_num) within_text using a case-sensitive
• start_num is an optional search starting from start_num.
argument
Example 1: If start_num is left out, the search starts
=FIND("na", "banana") from the first character of within_text.
This would return 3.
Example 2:
=FIND("NA", "banana")
This would return an error.
Example 3:
=FIND("na", "banana", 4)
This would return 5.

LEFT() =LEFT(text, num_chars) Returns the first num_chars characters of

• num_chars is an optional the text value text.
argument
Example: If num_chars is left out, the function
=LEFT("Computing", 5) returns only the first character of text.
This would return “Compu”.

LEN() =LEN(text) Returns the number of characters in the

Example: text value text.
=LEN("Computing")
This would return 9.

362
 Function Syntax Description

MID() =MID(text, start_num, Returns num_chars characters starting

num_chars) from position start_num of the text value
Example: text.
=MID("Computing", 3, 4)
This would return “mput”. (Note that, unlike Python, the position of
the first character is 1, not 0.)

RIGHT() =RIGHT(text, num_chars) Returns the last num_chars

• num_chars is an optional argument characters of the text value text.
Example:
=RIGHT("Computing", 5) If num_chars is left out, the function
This would return “uting”. returns only the last character of text.

SEARCH() =SEARCH(find_text, within_text, Returns the location of find_text in

start_num) within_text using a case-insensitive
• start_num is an optional argument search starting from start_num.
Example 1:
=SEARCH("na", "banana") If start_num is left out, the search starts
This would return 3. from the first character of within_text.
Example 2:
=SEARCH("NA", "banana")
This would return 3.
Example 3:
=SEARCH("na", "banana", 4)
This would return 5.

QUICK
EC K 9. 4
CH
1. Predict the result (which may be an error) of the following formulas:

a) =CONCAT("alpha", "beta" & "gamma", "delta")

b) =LEFT("Hello, world!", 2)
c) =MID("Hello, world!", 2, 4)
d) =RIGHT("Hello, world!", 4)
e) =FIND("ODG", "Hodgepodge")
f) =FIND("odg", "Hodgepodge")
g) =FIND("odg", "Hodgepodge", 3)
h) =SEARCH("Hodgepodge", "ODG")
i) =SEARCH("ODG", "Hodgepodge")

363
 QUICK
C K 9. 4
CH E
2. The following spreadsheet contains addresses obtained from a web site:

Figure 9.25 Addresses obtained from a web site

All the addresses have the following format where placeholders are in angled brackets:

Blk <block number> <street name> #<unit number> Singapore <postal code>
It is guaranteed that the addresses follow the above format strictly with no extraneous spaces or characters.

a) All postal codes are 6 digits long. Suggest a formula for cell B2 that uses RIGHT() and can be copied to the
rest of the column B to extract the postal codes from column A.
b) Unit numbers can vary in length and contents. However, they always start with a pound sign (#) and come
just before “Singapore” and the postal code. Suggest a formula for cell C2 that can be copied to the rest of
the column C to extract the unit numbers (including the pound sign) from column A. (It is guaranteed that
other parts of the address do not contain the pound sign or the word “Singapore”.)
c) Block numbers can vary in length and contents. However, they never contain a space and come just after
“Blk”. Suggest a formula for cell D2 that can be copied to the rest of the column D to extract the block numbers
from column A.
d) Street names may contain any text (including spaces) except the pound sign and the word “Singapore”.
Suggest a formula for cell E2 that can be copied to the rest of the column E to extract the street names from
column A. You may assume that columns B to D have been filled in correctly such that you can reuse previous
results to simplify your formula.

364
 9.5 Lookup Functions

LEARNING OUTCOMES
3.2.4 Use lookup functions to perform:
• Lookup of values from an unsorted vertical or horizontal table using exact matching
• Lookup of values from a sorted vertical or horizontal table using approximate matching
• Classification of values based on range using approximate matching and a secondary
table
• Lookup of values at the intersection of a particular row and column of a cell range
• Calculation of the relative position of a value in a cell range

In spreadsheets, we often need to find the row in which a particular value is located. Suppose we have a
class register list with registration numbers in the first column, birthdays in the second column and student
names in the third column, as shown in Figure 9.26. If we want to know the name of the student with
registration number 10, we will perform the following steps:

Search vertically down the first column of class register list A2:C15 (called the lookup
Step 1: table) for the registration number 10. The value to look for is called the lookup value.
(In this case, the lookup value is 10.)

If the lookup value is found, output the matching row’s third-column value. The
Step 2: column number is the column to output. (In this case, the column number is 3.)

Otherwise, if no matching row is found, output an error.

Figure 9.26 The lookup value of 10 and column number of 3 are used to find
the value “Mei Ling”

365
 Spreadsheets provide a VLOOKUP() function to perform these steps if you provide a lookup value, a
lookup table and a column number. For spreadsheets that use rows for fields and columns for records,
the HLOOKUP() function can be used instead. Note that VLOOKUP() performs a vertical search while
HLOOKUP() performs a horizontal search.

However, VLOOKUP() and HLOOKUP() are limited in that the value that is returned must be located to the
right of the lookup value for VLOOKUP() or below the lookup value for HLOOKUP(). If we want to know the
birthday of the student with name “Bala”, we will perform the following steps instead:

Search vertically down the name column C2:C15 for the name “Bala”. The value to
Step 1: look for is called the lookup value. (In this case, the lookup value is “Bala”.)

If the lookup value is found, get the position or row number of “Bala” in C2:C15. (In
Step 2: this case, the position or row number of “Bala” in C2:C15 is 3.)

Otherwise, if the lookup value is not found, output an error.

Output the value at that same row number from the birthday column B2:B15. (In this
Step 3: case, the value at row number 3 of B2:B15 is 5/5/2002.)

Figure 9.27 The lookup value of Bala is at position 3 and this is used to find the value “5/5/2002”

Spreadsheets typically provide a MATCH() function to perform the first two steps if you provide a lookup
value and a range. For the third step of returning the value at a given row number of a range, the INDEX()
function can be used instead.

These functions are described in Table 9.9.

366
 Function Syntax Description

HLOOKUP() =HLOOKUP(lookup_value, Looks for lookup_value in the first row of

table_array, row_index_num, table_array and returns the value in row
range_lookup) row_index_num of the matching column.
• range_lookup is an optional
argument and can be either If range_lookup is TRUE, the function
TRUE or FALSE performs an approximate match.

If range_lookup is FALSE, the function

performs an exact match.

If range_lookup is left out, the function

performs an approximate match.

INDEX() =INDEX(array, row_num, Returns the value located at row row_num

column_num) and column column_num of array.
• row_num is required if
column_num is left out If row_num is left out, the function returns
• column_num is required if the entire row.
row_num is left out
If column_num is left out, the function
returns the entire column.

MATCH() =MATCH(lookup_value, Returns the position of lookup_value in

lookup_array, match_type) lookup_array.
• match_type is an optional
argument and can be either -1, If match_type is 1 or left out, the function
0, or 1 finds the largest value that is less than or
equal to lookup_value.

If match_type is 0, the function performs

an exact match.

If match_type is -1, the function finds the

smallest value that is greater than or equal
to lookup_value.

VLOOKUP() =VLOOKUP(lookup_value, Looks for lookup_value in the first

table_array, col_index_num, column of table_array and returns the
range_lookup) value in column col_index_num of the
• range_lookup is an optional matching row.
argument and can be either
TRUE or FALSE If range_lookup is TRUE, the function
performs an approximate match.

If range_lookup is FALSE, the function

performs an exact match.

If range_lookup is left out, the function

performs an approximate match.

Table 9.9 Lookup functions

367
 9.5.1 range_lookup

The last argument to both HLOOKUP() and VLOOKUP() is a logical value named range_lookup that
determines whether an exact match or approximate match is used.

An exact match looks for the exact lookup value and returns the error value #N/A if no match is found. An
approximate match, on the other hand, looks for the largest value that is less than or equal to the lookup
value. It only returns an error value if all the values in the first column or row of the lookup table are larger
than the lookup value.

When using an exact match, the only way to make sure that all lookups are successful is to include all
possible lookup values in the lookup table. This can make the lookup table very long. Using an approximate
match lets us shorten the lookup table as the table does not need to contain the exact lookup values. This
is useful if a single row or column of the lookup table is used for multiple lookup values, as explained in the
following examples.

Figure 9.28 shows how VLOOKUP() is used to look up the names of three students based on their registration
numbers (5, 6 and 9).

Figure 9.28 Using VLOOKUP() to look up names based on registration number

In this case, the first argument for each use of VLOOKUP() is a registration number in column E (that is, E2,
E3 or E4). This is the lookup value that we are searching for.

The second argument for all three uses of VLOOKUP() refers to the class list in A2:C15. This is the lookup
table that we wish to search through and retrieve a value from. (An absolute range reference is used here to
make copying the formula to new rows easier.)

Finally, the third argument of 3 tells VLOOKUP() to return the value in the third column of the lookup table
if it finds a match. This is because the names we wish to retrieve are in the third column of the lookup table.

368
Figure 9.29 shows how VLOOKUP() can also be used to convert rounded percentage scores into grades
(such as A1, A2 and B3). However, the lookup table in this case is very long as it includes every possible
rounded percentage score from 0 to 100.

Figure 9.29 Using VLOOKUP() to convert rounded percentage scores into grades

However, notice that the entries in the second column of the lookup table are the same for every value
in the first column from 0 up to but not including 40, from 40 up to but not including 45, from 45 up to
but not including 50, and so on. This indicates that we can achieve the same result using approximate
matching and a shorter lookup table that only includes the boundaries for the different grades in the first
column. This works because approximate matching looks for the largest value that is less than or equal to
the lookup value.

Approximate matching is used by default when the fourth argument is left out. Nonetheless, to make it
clear that we wish to use approximate matching, we shall include the fourth argument and set it to TRUE,
as shown in Figure 9.30.

369
 Figure 9.30 Using approximate matching to convert rounded percentage scores into grades

Figure 9.30 shows how VLOOKUP() with approximate matching can be used to convert rounded percentage
scores into grades (such as A1, A2 and B3). For example, the formula in cell B2 looks for the largest value in
column D (the first column) that is less than or equal to the lookup value of 98, i.e., the value of 75, which is
found in row 10. It then returns the value found in column E (the second column) of the same row, i.e., “A1”.
Hence, the formulas in column B can return valid results even though most of the values in column A do not
have exact matches in column D.

On the other hand, if the fourth argument is set to FALSE, VLOOKUP() will perform an exact match instead,
as shown in Figure 9.31. In this case, only the scores of 65 and 40, which exactly match rows 8 and 3 of range
D2:E10 respectively, will have a valid result. The other cells will return the error value #N/A.

Figure 9.31 Using exact matching returns an error value if an exact match cannot be found

370
Figure 9.32 shows how VLOOKUP() with approximate matching can be used to group BMI values into the
Underweight, Acceptable and Overweight categories.

Figure 9.32 Using VLOOKUP() with approximate matching to categorise BMI values

Suppose that the lookup table D2:E4 is now “flipped” such that rows are used for fields and columns
are used for records instead. This means that the BMI values of 0, 16 and 27 are now on row 1 and the
categories “Underweight”, “Acceptable” and “Overweight” are now on row 2. In this case, we will have to
use HLOOKUP() instead of VLOOKUP() to perform the same task.

Figure 9.33 Using HLOOKUP() with approximate matching to categorise BMI values

When using HLOOKUP(), the first two arguments still refer to the lookup value and lookup table range
respectively. However, the third argument is now a row number instead of a column number. In this case,
the row number is still 2 as the groupings we want to retrieve are on the second row of the lookup table.

Note that for approximate matching to work, the first column (for VLOOKUP()) or row (for HLOOKUP()) of
the lookup table must have its values arranged in increasing order. For instance, the values in the ranges
D2:D10 in Figure 9.30 and D2:D4 in Figure 9.32 are arranged in increasing order from top to bottom.

371
 Similarly, the values in range E1:G1 in Figure 9.33 are arranged in increasing order from left to right. Exact
matching, on the other hand, does not require the values in the first column or row of the lookup table to
be arranged in increasing order.

9.5.2 match_type

The last argument to MATCH() is a number named match_type. It is similar to range_lookup

for VLOOKUP() and HLOOKUP() as it determines whether an exact match is used. However, while
range_lookup is either TRUE or FALSE, there are three options for match_type: -1, 0 and 1.

Setting match_type to 0 performs an exact match that looks for the exact lookup value and returns the
error value #N/A if no match is found. For instance, Figure 9.28 shows how INDEX() and MATCH() is used
to look up the birthdays of three students based on their names (“Bala”, “Henry” and “Siti”). Note that the
result for “Henry” is #N/A as the name is not found in the table.

Figure 9.34 Setting match_type to 0

Setting match_type to 1 or leaving it out instead looks for the largest value that is less than or equal to the
lookup value. Figure 9.28 shows that with match_type set to 1, the result for “Henry” is “22/7/02”, which
corresponds to “Gopi” as the largest value less than or equal to the lookup value “Henry”. Note that when
match_type is set to 1, the range used for MATCH() must be arranged in ascending order.

Figure 9.35 Setting match_type to 1

372
Finally, setting match_type to -1 looks for the smallest value that is greater than or equal to the lookup
value. Figure 9.28 shows that with match_type set to -1, the result for “Henry” is “19/3/02”, which
corresponds to “Irfan” as the smallest value greater than or equal to the lookup value “Henry”. Also note
that the table has been reversed because when match_type is set to -1, the range used for MATCH() must
be arranged in descending order instead.

Figure 9.36 Setting match_type to -1

U
DID YO
N O W ?
K
By default, the VLOOKUP(), HLOOKUP(), and MATCH() functions do not perform exact matches. If
your data isn’t sorted as needed, these functions might return incorrect results without warning.

To avoid this, you may wish to instead use the newer XLOOKUP() and XMATCH() functions that
use exact matching by default. The use of these functions is not covered in this textbook.

373
 QUICK
E C K 9.5
CH
1. The following is a spreadsheet of different disposable plate options, sorted by brand:

Figure 9.37 Disposable plate options

Predict the result (which may be an error) of the following formulas:

a) =VLOOKUP("Fiesta", $B$2:$D$7, 2)
b) =VLOOKUP("Dinnerdeluxe", $B$2:$D$7, 3)
c) =VLOOKUP("Dinnerdeluxe", $B$2:$D$7, 3, TRUE)
d) =VLOOKUP("Dinnerdeluxe", $B$2:$D$7, 3, FALSE)
e) =MATCH("EcoWorld", $B$2:$B$7)
f) =MATCH("Happy Food", $B$2:$B$7)
g) =MATCH("Happy Food", $B$2:$B$7, 0)
h) =MATCH("Happy Food", $B$2:$B$7, 1)
i) =INDEX($A$2:$D$7, 3, 2)
j) =INDEX($A$2:$D$7, MATCH("Red", $D$2:$D$7, 0), MATCH("Product Code", $A$1:$D$1, 0))

374
 QUICK
C K 9.5
CHE
2. The following is a spreadsheet of different disposable cutlery options, sorted by unit price:

Figure 9.38 Disposable cutlery options

Predict the result (which may be an error) of the following formulas:

a) Explain why the formula =VLOOKUP("CS20", $A$2:$C$7, 3) does not correctly return the unit price of
cutlery with the product code “CS20” and suggest how the formula can be modified to return the correct
result.
b) Explain why the formula =MATCH(0.14, $C$2:$C$7, -1) does not correctly return the position of the
smallest unit price that is larger than or equal to $0.14 and suggest how the spreadsheet should be arranged
to obtain the correct result.
c) Suggest a formula that uses VLOOKUP() to return the unit price of cutlery for the brand “Elegance Catering”.
The formula should always return the correct result even if the rows are rearranged.
d) Suggest a formula that uses INDEX() and MATCH() to return the product code of cutlery for the brand “Party
Perfect”. The formula should always return the correct result even if the rows are rearranged.

9.6 Date Functions

LEARNING OUTCOMES
3.2.5 Use date functions to perform:
• Determination of the current date or the current date and time
• Calculation of the number of days between two dates

Table 9.10 shows some common date functions.

375
 Function Syntax Description

DAYS() =DAYS(end_date, Returns the number of days

start_date) between the two dates.

NOW() =NOW() Returns the current date and

time.

TODAY() =TODAY() Returns the current date.

Table 9.10 Date function

U
DID YO
?
KNOW
Note that the values returned by NOW()
and TODAY() are updated every time
formulas are recalculated (e.g., whenever
the spreadsheet is opened).

Dates and times are represented by “serial numbers”

equal to the number of days since midnight on the
fictional date 0 Jan 1900. Fractional parts of a day
are used to represent time. For example, the serial
number 1.5 represents 12 noon on 1 Jan 1900 and
the serial number 2.75 represents 6pm on 2 Jan 1900.
Whether a number or a date is displayed in a cell is
determined by the cell’s number format.

QUICK .66
E CKK 9
9 .
CH
C HEC
1. The following spreadsheet displays dates for significant events:

Figure 9.39 Dates for significant events

Suggest a formula for cell C2 that can be copied to range C3:C4 so that each time the spreadsheet is opened it
will calculate the number of days since each historic event.

376
 9.7 Goal Seek

LEARNING OUTCOMES
3.1.2 Use the Goal Seek feature to determine the value needed in a cell for another cell to reach a
specified target value.

After defining the relationships between cells in a spreadsheet, we may wish to adjust the value in one cell
so that another cell can reach a particular value (i.e., the goal). Goal Seek automatically solves the problem
using a trial-and-error approach by plugging in guesses until the goal is reached.

For instance, suppose we have a spreadsheet that calculates the BMI (cell C2) of a student, given his height
(cell A2) and weight (cell B2) using the formula:

BMI = (Weight in kg) / (Height in m)2

Figure 9.40 Spreadsheet to calculate a student’s BMI

As shown in Figure 9.40, the student is currently overweight

as his BMI is above 27 (based on the BMI guideline for 15- to
16-year-olds). To find out what his weight must be to reach
an acceptable BMI of 27, we can use the Goal Seek feature
that can be found under the “What-If Analysis” menu of the
“Data” tab.

Figure 9.41 shows what the Goal Seek window looks like
when it is first opened.

Figure 9.41 Goal Seek window when initially

opened

377
 In this case, we want to set the BMI
(cell C2) to 27 by changing the weight
(cell B2). Figure 9.42 shows how these
requirements can be entered into the
Goal Seek window.

Figure 9.42 Using Goal Seek by keying in the required values

After clicking on “OK”, the spreadsheet

automatically calculates the value
of 56.77 for the student’s weight in
order to achieve a BMI of exactly 27, as
shown in Figure 9.43. This means that
the student with a height of 1.45 m can
only reach a BMI of 27 by reducing his
weight to 56.77 kg.

Figure 9.43 The solution (weight in kg) is found by Goal Seek

The spreadsheet in Figure 9.44 collates the expected attendance, funding and costs for the class party
described at the beginning of this chapter.

Formulas are used to calculate the total funding (cell C18) based on the amount each attendee is expected
to contribute (cell C1).

Formulas are also used to calculate the total cost (cell I12) based on the quantities of items needed to
organise a potluck. In particular, the formula in cell I1 shows whether the class party makes a net profit or
loss by calculating the difference between total funding and total cost.

378
 Figure 9.44 Spreadsheet for class party finances

Suppose the organisers of the class party wish to change the contribution per attendee so that the class
party breaks even exactly and neither makes nor loses money. This means that we should set the difference
between total funding and total cost (cell I1) to 0 by changing the contribution per attendee (cell C1), as
shown in Figure 9.45.

Figure 9.45 Using Goal Seek by setting the value for cell I1 to 0

379
 After clicking on “OK”, the spreadsheet automatically calculates that each attendee should contribute $3.00
in order to break even, as shown in Figure 9.46.

Figure 9.46 The solution (contribution per attendee) found by Goal Seek

QUICK
E C K 9.7
CH
1. The following spreadsheet converts temperatures from Celsius to Fahrenheit using the formula:

Fahrenheit Temperature = (Celsius Temperature × 9/5) + 32

Figure 9.47 Temperature conversion spreadsheet

380
 QUICK
EC K 9.7
CH
When Ctrl + ` is pressed, the spreadsheet looks like this.

Figure 9.47 Temperature conversion spreadsheet

You wish to use the Goal Seek feature with this spreadsheet to find out the Celsius temperature that is
equivalent to a Fahrenheit temperature of 95.

a) To do this, suggest what should be entered into the Goal Seek window for:
i. Set cell
ii. To value
iii. By changing cell
b) Perform the Goal Seek and determine the required Celsius temperature.

9.8 Conditional Formatting

LEARNING OUTCOMES
3.1.3 Use the Conditional Formatting feature to automatically update cell formatting based on
one or more rules.

ER MS
KEY T
When trying to understand a large set of data, it is often
useful to highlight important details to make them more
noticeable. This can be done using a spreadsheet feature
called conditional formatting. Conditional Formatting
Spreadsheet feature which
Conditional formatting allows a cell to automatically vary automatically formats a cell based on
its appearance based on its contents, ranking, relation to given criteria
the average, uniqueness or some other formula.

To use conditional formatting, we need to create rules. Each rule specifies three pieces of information:
1 Which range of cells the rule is applied to
2 What condition is being tested
3 What formatting should be applied if the condition is true

381
 To create a rule, select the range of cells that the
rule should be applied to, then choose “Conditional
Formatting” under the “Home” tab, as shown in Figure
9.49.

The most common cases and options for using

conditional formatting are listed under this menu. More
complex rules can be created using the “New Rule…”
option while existing rules can be edited or removed
using the “Manage Rules…” and “Clear Rules” options
respectively.

For instance, suppose we wish to highlight the cells in

the second column of the spreadsheet in Figure 9.50 that
contain a number that is less than 3.

To do this, we would follow the following instructions. Figure 9.49 Conditional formatting menu under the
“Home” tab

Figure 9.50 Spreadsheet for tracking class party contributions by each classmate’s family

382
 Select the range of cells that the new rule should be applied to.

Step 1:

In this case, we select the cells containing numbers under the second column (range
B4:B17).

Select “Conditional Formatting” under the “Home” tab.

Since we wish to highlight the cells that contain a number that is less than 3, we can
select “Highlight Cells Rules” and choose the “Less Than…” option.

Step 2:

Besides varying the font, fill and border of cells, we can also use the “Data Bars”, “Color
Scales” and “Icon Sets” (outlined in green) options to display tiny bar graphs called
data bars, or icons that update automatically based on each cell’s contents.

383
 Complete the “LESS THAN” condition by entering a value of 3.

(Note that cell references can also be used here instead of constants. Relative cell
references will be changed relative to the top-leftmost cell of the range when applied
to other cells in the range.)

The box outlined in green describes how cells that match the condition will be
formatted. Besides the default options, we can also choose the “Custom Format…”
option to customise the formatting.
Step 3:

In this case, we will use the default option of “Light Red Fill with Dark Red Text” and
click “OK”.

The cells under the second column which contain a number less than 3 are now
highlighted using a light red fill and dark red text, as expected.

Result:

Note that changing the contents of highlighted cell to a number that is 3 or more will
automatically remove its highlighting. Similarly, changing the contents of a
non-highlighted cell in B4:B17 to any number that is less than 3 will automatically
highlight it.

384
If we select the “Manage Rules…” option under the conditional formatting menu while the highlighted cells
are selected, we see that there is now a rule applied to range $B$4:$B$17 that formats any cells in this range
containing a value less than 3 with a light red fill and dark red text, as shown in Figure 9.51.

Figure 9.51 Conditional formatting rules can be edited and/or deleted in the Conditional Formatting Rules
Manager window

To remove a conditional formatting rule, select it in the Conditional Formatting Rules Manager window and
click “Delete Rule”. To change the rule’s range, condition and/or highlighting style, select it and click “Edit
Rule…”.

Sometimes, none of the common cases and options under the “Conditional Formatting” menu are suitable
for what is required. In that case, we will need to create a custom rule by selecting “New Rule…” under the
conditional formatting menu.

Suppose we wish to highlight the cells in the first column of the spreadsheet in Figure 9.52 that contain a
name starting with the letter M.

Figure 9.52 Spreadsheet where conditional formatting is needed

385
 To do this, we would follow the following instructions:

Select the range of cells that the new rule should be applied to.

Step 1:

In this case, we select the cells containing the names under the first column (range
A4:A17).

Select “Conditional Formatting” under the “Home” tab.

We wish to highlight the cells that contain names starting with the letter M.
Unfortunately, none of the common options under the “Conditional Formatting”
menu appear suitable for this requirement.

This means that we will need to create a custom rule by selecting “New Rule…”.

Step 2:

386
 The New Formatting Rule window will appear.

Step 3:

None of the first five options appear suitable for checking whether any cells start with
the letter M, so we will select the last option: “Use a formula to determine which cells
to format”

Now we need a formula that will produce a logical value based on whether the first
letter of the name in each cell is the letter M.

We always write custom formulas for conditional formatting rules relative to the top-
left cell of the range. In this case, it is cell A4.

To check whether the first character of A4 is the letter M, we use the formula
“=LEFT(A4, 1)="M"”.

We then customise the formatting to have a yellow fill and click “OK”.

Step 4:

387
 Cells under the first column containing a name which starts with the letter M are now
highlighted using a yellow fill.

Result:

QUICK
C K 9.8
CHE
1. The following is a spreadsheet of heights and weights for students in a class:

Figure 9.53 Heights and weights for students in a class

a) Use conditional formatting on column A so that names that include a space are displayed with a yellow
background.
b) Use conditional formatting on column C so that heights of male students that are strictly above the average
height of male students in the class are displayed with orange text.
c) Use conditional formatting on column C so that heights of female students that are strictly above the average
height of female students in the class are displayed with blue text.

388
QUICK .8
CH E CK 9

d) The BMI of a student is calculated using the formula:

BMI = Weight / (Height / 100)2

Use conditional formulating on column D so that the weights of students with a BMI between 16 and 27
(inclusive) are displayed with green text.

W
REVIE N
E S
STTIIOON
QU
Q UE
1. The following spreadsheet outlines the legs taken during a world tour:

Figure 9.54 Legs taken during a world tour

a) Suggest a formula for cell F2 that can be copied into range F3:F12 to calculate the average speed for each leg
in km/h.
b) The overall average speed of the tour is the total distance travelled divided by the total time taken. Suggest
a formula for cell F14 to calculate the overall average speed of the tour in km/h.
c) Suggest a formula for cell F15 to calculate the number of legs which took longer than a week.
d) Suggest a formula for cell F16 to calculate the total distance travelled in km during those legs which took
longer than a week.

389
 W
REVIE
U E ST ION
Q
2. The spreadsheet in Figure 9.55 is used to tabulate the monthly interest earned and money withdrawn from a
bank account.

Figure 9.55 Interest earned and money withdrawn from a bank account

When Ctrl-` is pressed, the spreadsheet looks like this:

Figure 9.56 Spreadsheet for bank account with formulas shown

Create this spreadsheet with the formulas shown in Figure 9.56 using a spreadsheet program, then answer the
following questions:

a) Suggest a formula for cell C2 that can be copied into range C3:C13 in order to calculate the interest earned
for each month before money is withdrawn, using the interest rate that is specified in cell G1.
b) In the program, use Goal Seek to determine what the monthly interest rate must be to have $4,200 left at the
end of December. Give your answer to two decimal places.
c) In the program, use conditional highlighting to automatically colour the text of cells in column D red if they
show a withdrawal amount greater than $100.

390
ANSWER

Pg. 346-Quick Check 9.1

1. a) By entering an apostrophe in front of the equals sign, i.e., '=
b) C4:C15
c) E4 only
d) C4:C15 and E4:E15

2. =A2/B2

Pg. 352-Quick Check 9.2

1. a) FALSE
b) FALSE
c) TRUE
d) FALSE
e) TRUE
f) Case-insensitive

2. =IF(B4>=$C$1, "Y", "N")

3. Possible formulas (accept any possible answer):

• =IF(A1>3, "Greater than 3", IF(A1=3, "Equal to 3", "Less than 3"))
• =IF(A1>3, "Greater than 3", IF(A1<3, "Less than 3", "Equal to 3"))
• =IF(A1=3, "Equal to 3", IF(A1<3, "Less than 3", "Greater than 3"))
• =IF(A1=3, "Equal to 3", IF(A1>3, "Greater than 3", "Less than 3"))

Pg. 360-Quick Check 9.3

1. a) =B2*D2
b) =SUM(E2:E9)
c) i. 6
ii. B

2. a) =ROUND(B2, 1)
b) =COUNTIF(C2:C15, "<60")
c) =MAX(C2:C15)-MIN(C2:C15)

3. a) =RANK.EQ(A2, $A$2:$A$15, 1)
or alternatively
=COUNTIF($A$2:$A$15,"<"&A2)+1
b) =AVERAGEIF(B2:B17, "<=10", A2:A15)

391
 ANSWER

Pg. 363-Quick Check 9.4

1. a) alphabetagammadelta
b) He
c) ello
d) rld!
e) #VALUE!
f) 2
g) 7
h) #VALUE!
i) 2

2. a) =RIGHT(A2, 6)
b) =MID(A2, FIND("#", A2), FIND("Singapore", A2) - FIND("#", A2) - 1)
or
=MID(A2, FIND("#", A2), LEN(A2) - FIND("#", A2) - 16)
c) =MID(A2, 5, FIND(" ", A2, 5) - 5)
d) =MID(A2, LEN(D2) + 6, FIND("#", A2) - LEN(D2) - 7)

Pg. 374-Quick Check 9.5

1. a) 0.18
b) White
c) White
d) #N/A
e) 2
f) 5
g) #N/A
h) 5
i) Elegance Catering
j) FIESTA-R

2. a) VLOOKUP() uses approximate matching by default and requires the first column of the lookup table
to be arranged in increasing order. However, the first column A of the lookup table $A$2:$C$7 is not
arranged in increasing order, so the formula does not work correctly. To obtain the correct result, we
can modify the formula to use exact matching instead of approximate matching by setting the last
range_lookup argument to FALSE: =VLOOKUP("CS20", $A$2:$C$7, 3, FALSE)
b) When the last match_type argument of MATCH() is set to -1, the range provided must be arranged in
decreasing order. However, the provided range of $C$2:$C$7 is instead arranged in increasing order, so
the formula does not work correctly. To obtain the correct result, the spreadsheet can be sorted so that
the range in column C is arranged in decreasing order.
c) =VLOOKUP("Elegance Catering", $B$2:$C$7, 2, FALSE)
d) =INDEX($A$2:$A$7, MATCH("Party Perfect", $B$2:$B$7, 0), 1)

Pg. 376-Quick Check 9.6

1. =DAYS(NOW(), B2)
or
=DAYS(TODAY(), B2)

Pg. 380-Quick Check 9.7

1. a) i. B2
ii. 95
iii. A2
b) The required Celsius temperature is 35.

392
ANSWER

Pg. 388-Quick Check 9.8

1. a) Add the following rule to A2:A15:

(The formula =SEARCH(" ", A2) > 0 may also be used.)

b) Add the following rule to C2:C15:

c) Add the following rule to C2:C15:

393
 ANSWER

c) Add the following rule to D2:D15:

The resulting spreadsheet should look like this:

Pg. 389-Review Questions

1. a) =D2/(E2*24)
b) =SUM(D2:D12)/(SUM(E2:E12)*24)
c) =COUNTIF(E2:E12, ">7")
d) =SUMIF(E2:E12, ">7", D2:D12)

394
ANSWER

2. a) =$G$1*B2
b) 0.67%
c) c)The required conditional formatting settings for D2:D13 are as follows:

395