ICT CHAPTER 18 SPREADSHEETS (PRACTICAL TASKS)
Background knowledge for the practical tasks
Work Sheet: It is a grid of cells in which you input, analyze and store data. It is made up of
horizontal rows and vertical columns. It is also called Spread sheet.
Workbook: It is a collection of several work sheets; it’s the file in which you work and store your
data.
Rows: A row is a horizontal line of cells. It is represented by numbers. Rows are horizontal boxes
that divide the table in segments.
Columns: A column is a vertical line of cells. It is represented by letters. Columns are vertical
boxes that divide the table in segments
Cells: A space for data to be entered in a table; a box formed by the intersection of columns and
rows.
Cell Address: It is the location of a cell as defined by the column and the row it is in. If a cell is in
column A and row 5, the cell address will be A5.
Active cell: This is a cell with a thick black border around it.
Ranges: A group of cells in a table.
Named cell is a cell in a table that is given a name and can be referred to using that name.
Named range is a group of cells in a table that are given a name and can be referred to using that
name.
Formulae: are mathematical operators defined by the user to perform a function. Formulas are
typed in by the user. They include simple mathematical operators like +, -, *, /, or can be as
complex as the user wants. All formulae starts with the “equal to ” sign. =A1 + B1 + C1.
A formula can contain a function. Formulas can be simple calculations/mathematical operation.
Formulas can be typed directly into the formula bar
Function: functions are predefined logical and mathematical operations or formulae a user can
use in a spreadsheet. It is a predesigned code/formulae that calculates specific values, e.g. SUM,
AVERAGE, MIN, MAX, VLOOKUP. While functions can be used inside formulas, formulas cannot
be used inside functions.
Function is a special type of formula/complex formula. Functions are built into the
software/spreadsheet. Functions can be used to simplify complicated calculations. They can have
built in commands. Function has a pre-defined name/reserved word.
Nested formulae/function: A formula or function used inside another formula, or function as an
argument.
Relative cell referencing: is made when the cell referenced is supposed to change, when the cell
referencing it is changing. When a formula/ function is copied, and relative referencing is used, the
cell referenced changes with the cell that the function is in.
Absolute cell referencing: is made when the cell referenced stays constant when the cell
referencing it is changing. Absolute referencing is used when the cell referenced needs to stay
the same, even when the formula/ function is copied.
If the formula is dragged down the column/replicated, the cell reference (associated with the $)
does not change.
1
Spreadsheet formulae for the following mathematical operators:
(a) Addition: +
(b) Subtraction: -
(c) Multiplication: *
(d) Division: /
(e) To the power of/exponential/indices: ^
(f) Is not equal to: <>
SPREADSHEET FORMULAE AND FUNCTION TABLE
DESCRIPTION EXPRESSION /FORMULA
Addition, Subtraction, =B2+B10, =B3 – B6 , =B5*B6 , =B2/B12
Multiplication and Division (Cell
B1 value is greater than A1).
SUM - For Adding a range of =SUM(B2:A15)
numbers
AVERAGE – Finds the =AVERAGE(B2:B15)
average/mean
MIN – Finds the lowest/smallest =MIN(B2:B15)
value
MAX – Find the highest/biggest =MAX(B2:B15)
value
MODE- Find the most appearing =MODE(B2:B15)
Number.
INT – Converts to whole number ( =INT(A3)
integer)
ROUND – Rounding numbers =ROUND(A11,3)
The 3 means 3(three) decimal places
ROUNDUP – Rounding numbers =ROUNDUP(A18,2)
up
The 2 means 2(two) decimal places
ROUNDDOWN – Rounding =ROUNDDOWN(A24,2)
numbers down
The 2 means 2(two) decimal places
SQRT – Finds the Square root of =SQRT(A3)
a number.
A3 is the cell address of the number.
INDICES(POWER)- used to raise =POWER(A12,2), =POWER(A12,3), =POWER(A12,4)
a selected number to a given
Calculates a number raised to Power 2, 3, and 4.
power.
LOOKUP - LOOKUP allows for LOOKUP
horizontal and vertical
=LOOKUP(lookup value, lookup range, result range)
searching.
=LOOKUP(C16,$C$2:$C$11,$B$2:$B$12)
LOOKUP returns data to the left
and also to the right of the C16 is the cell to lookup/check.
searched column. C2:C11 is the array/range of cells to look in. ($C$2:$C$11,for Absolute
It usually only works when the referencing)
data is sorted. B2:B12 is the array/range of cells where value or results will be return
from.
2
VLOOKUP - Looking up what VLOOKUP 1
particular values means from a
=VLOOKUP(C16, $A$1: $B$11 , 2 , FALSE)
table arranged vertically.
C16 is the cell to check.
It only returns data to the right of
the searched column A1:B11 is the array/range/table of cells to look in. ($A$1: $B$11,for
Absolute referencing)
VLOOKUP works even when the
data is not sorted. 2 means column 2 from A1:B11 , (returns the value in column 2 in that
section).
VLOOKUP requires a column
index value FALSE returns exact value/match.
User can select either an …………………………………………………………………………………
approximate or exact match to =VLOOKUP(C16,$A$2:$C$11,3,FALSE)
the lookup value
=D16*F16
VLOOKUP 2
=VLOOKUP(B13,GradeBoundaries,2,TRUE)
B13 is the cell to check.
GradeBoundaries ( a Named Range ) is the array/range/table of cells to
look in.
2 means column 2 from GradeBoundaries , (returns the value in column 2
in that section).
TRUE returns approximate value/match.( values between each grade
range).
Difference between LOOKUP and VLOOKUP
LOOKUP VLOOKUP
LOOKUP allows for horizontal and vertical VLOOKUP allows for vertical searching
searching
LOOKUP does not require an index value/only VLOOKUP requires a column index value
works on the second row/column
LOOKUP returns data to the left and also to the VLOOKUP only returns data to the right of the
right of the searched column searched column
LOOKUP usually only works when the data is VLOOKUP works even when the data is not
sorted sorted
User can select either an approximate or exact
match to the lookup value
HLOOKUP - Looking up what =HLOOKUP(C8 ,$A$2:$K$3 , 2 , FALSE)
particular values mean from a
C8 is the cell to check.
table arranged horizontally
,$A$2:$K$3is the array/range/table of cells to look in.( $ sign for Absolute
referencing)
2 means row 2 from , $A$2:$K$3, (returns the value in row 2 in that
section).
FALSE returns exact value/match.
IF – If statements are helpful to =IF(A7>=60,"PROMOTED", "FAIL")
return one value if the statement
IF the value in A7 is greater than or equal to 60, write/display
is true and another if the
PROMOTED, if not write/display FAIL.
statement is false
= IF (C19="Abuja", "Correct", "Wrong")’
3
Nested IF =IF(A31>=80, "Distinction", IF(A31>=60, "Credit" , "Fail"))
Students who get a mark of 80 or above get a Distinction.
Those who get a mark below 60 get a Fail , but those who get above 60
marks, and lesser than 80 marks get a Credit.
=IF(A45>=75,"Making progress", IF(A45>=50, "Improving", IF
(A45>=0, "Needs improvement")))
If the mark is 75 or more “Making progress”
If the mark is 50 to 74 marks “Improving”
If the mark is 0 to 49 marks “Needs improvement”
For % Discount over item bought
=IF(A59>=21,(B59*$F$56),IF(A4>=11,($B59*$F$55),(B59*$F$54)))
Price of item after Discount
=B59-C59
IF statement (to make sure that if =IF(B74="", "",IF(B74="Male", "M","F"))
no data is entered in a cell then
nothing is displayed in formula (to make sure that if no data is entered in the Gender column (B74), then
cell.) nothing is displayed in formula cell.)
IF and VLOOKUP Functions for the Branch Name:
=IF(C18="","",VLOOKUP(C18,$A$2:$E$11,2,FALSE))
Functions for the Expected Profit %:
=IF(C18="","",IF(VLOOKUP(C18,$A$2:$E$11,3,FALSE)=0,"",
VLOOKUP(C18,$A$2:$E$11,3,FALSE)))
Functions for the Special Profit %:
=IF(E18=1,VLOOKUP(C18,A$2:E$11,4,FALSE),
IF(E18=2,VLOOKUP(C18,$A$2:$E$11,5,FALSE),""))
COUNT – Finds how many =COUNT(A2:A11)
numbers are in a range
COUNTA – Counts the number =COUNTA(B2:B11)
of items in a range
COUNTBLANK- Counts =COUNTBLANK(B2:B11)
blank/empty cells
COUNTIF - Counts the number of = COUNTIF(C2:C11,H17)
cells in a range that satisfy the
• The range is C2:C11
given criteria
• The criterion cell reference H17
• It searches through the range C2:C11
• Counts the number of cells/values….. that have a value equal to
contents of H17/number of times H17 occurs
• Returns/displays that count
=COUNTIF(C2:C11,"Joshua")
=COUNTIF(D2:D11,">4")
Checks and count the number of cells that have a value greater than 4.
4
COUNTIFS – it applies criteria to =COUNTIFS($A$2:$A$11,C16,$B$2:$B$11,">"&5)
cells across multiple ranges and
• $A$2:$A$11 – criteria range 1
counts the number of times all
criteria are met. • C16- criteria 1 (a number, expression, cell reference, or text that define
which cells will be counted)
• C16,$B$2:$B$11 – criteria range 2
• ,">"&5 – criteria 2
SUMIF – adding up specific =SUMIF($B$2:$B$25,F6,$D$2:$D$25)
values in a range
• Looks through the range (contents of cells) B2 to B25
• See if they are equal to (the contents of cell) F6
• It totals the contents of the corresponding cells/range from D2 to D25
• Produces the result.
Average IF – it searches for =AVERAGEIF($B$2:$B$25,F13,$D$2:$D$25)
criteria and makes an average of
$B$2:$B$25 is the range, F13 is the criteria, $D$2:$D$25 is the average
the range according to the criteria
range.
SUMIFS – is a function to sum =SUMIFS($D$4:$D$12,$B$4:$B$12,F8,$D$4:$D$12,">"&200000)
cells that meet multiple criteria. It
can be used to sum values when
corresponding cells meet criteria • $D$4:$D$12- sum range - The range to be summed.
based on dates, numbers, and • $B$4:$B$12 - range1 - The first range to evaluate.
text. • F8 – criteria 1 - The criteria to use on range1.
• $D$4:$D$12 - range 2 - The second range to evaluate.
• ">"&200000 - criteria 2 - The criteria to use on range 2.
Average IFS – calculates the =AVERAGEIFS($D$4:$D$12,$B$4:$B$12,F15,$D$4:$D$12,">"&200000)
average of all numbers in a given
• $D$4:$D$12 - Average range – This is one or more cells that we wish
range of cells, based on multiple
to average.
criteria.
• $B$4:$B$12 - range1 - The first range to evaluate.
• F15 - criteria 1 - The criteria to use on range1.
• $D$4:$D$12 - range 2 - The second range to evaluate.
• ">"&200000 - criteria 2 - The criteria to use on range 2.
MATCH – Returns the relative =MATCH(B13,A5:E5,0)
position of an item in an array
B13 is the cell to check.
that matches a specified value in
a specified order. A5:E5 is the range of the (row or column) cells ,( $A$5:$E$5,for
Absolute referencing)
0 returns exact value/match.
INDEX – Returns a value or =INDEX(A5:E8,2,3)
reference of the cell at the
A5:E8 is the table array/range
intersection of a particular row
and column, in a given range. 2 is the row position/index.
3 is the column position/index.
INDEX AND MATCH =INDEX($A$35:$C$41,MATCH(A52,$B$35:$B$41,0),
MATCH($C$51,$A$35:$C$35,0))
• INDEX($A$35:$C$41) - is the table array/range.
• MATCH(A52,$B$35:$B$41,0) - is the row position/index.
• MATCH($C$51,$A$35:$C$35,0) - is the column position/index.
MATCH and VLOOKUP =VLOOKUP(H25, $A$5:$E$8, MATCH(G25,$A$5:$E$5,0),FALSE)
5
Text Functions
Calculations can be performed on text to extract parts of text, join text together, calculate the length of text
or change the case of text.
Function Purpose Example
CONCATENATE Joins together text values. =A1&B1&C1
=CONCATENATE (A1,B1,C1)
=A1&"."&B1&"@" &C1 & ".it.com"
LEFT Extracts the furthest left characters. =LEFT(A1,4)
RIGHT Extracts the furthest right characters. =RIGHT(B1,2)
UPPER Converts text into uppercase. =UPPER(LEFT(A1,4))
LOWER Converts text into lowercase. =LOWER(C1)
LEN Calculates the length of a string. =LEN(A1)
MID Returns the characters from the middle of a =MID(C1,5,4)
text string, given a starting position and length =MID(D1,2,3)
FIND Calculate the Numeric position of the letter E =FIND("E",E1,1)
in the text in cell E1
Date and Time functions
Calculations can be performed on date and time.
Function Purpose Example
DAY Calculates the day part of a date. =DAY(B1)
MONTH Calculates the month part of a date. =MONTH(B1)
YEAR Calculates the year part of a date. =YEAR(B1)
DATE Calculates the date from a given year, =DATE(B4,B3,B2)
month and day.
HOUR Calculates the hour’s part of a time. =HOUR(B8)
MINUTE Calculates the minute’s part of a time. =MINUTE(B8)
SECOND Calculates the second’s part of a time. =SECOND(B8)
TIME Calculates the time from given hours, =TIME(B9,B10,B11)
minutes and seconds
NOW Gives the current date and time (can be =NOW()
formatted for just date or just time).
Calculates the number of seconds in a =HOUR(B8)*3600+MINUTE(B8)*60+SECOND(B8)
TIME
WEEKDAY Extracts the WEEKDAY number from a =WEEKDAY(B1)
Date
Calculates how many years a person =INT((F5-F4)/365.25)
lived from Birth to Death.
