Spreadsheet Functions


A function is a predefined formula provided by a spreadsheet application.

Functions are written in the following format:

                =FunctionName(parameter1, parameter2, …, parameterN) 


=sum(e3, d4, r5),



a function with a single parameter is shown below:



=SUM(F3:F4)  (note this is 1 parameter, a range)


Note that a function’s  actions can be accomplished with a formula:

e.g =SUM(D3:F3) becomes =D3+E3+F3

Absolute addressing – prevents a cell’s row number or column letter from being updated when a formula is copied to another cell.

e.g          $C4        – prevents the column letter from being updated

                C$4        – prevents the Row number from being updated

                $C$4      – prevents both column letter and row number from being updated.

Secret tip!!!!!!!!  – use F4 to set a cell’s address to absolute addressing.

Class Demo

Download the excel file here:


StudentRoll NoMathFrenchSpanishTotalsAverageBonus ScorePass/Fail?
Jim1105520710Work harder
Mary2752051003350Good Job
Tobias Reiper3125 17913Work harder
Mario4135523812Work harder
Student Number4
Max Average33
Min Average7
# Marks < 303
Number of spanish marks entered3
Number of spanish marks Missing1

Sheet with formulas

StudentRoll NoMathFrenchSpanishTotalsAverageBonus ScorePass/Fail?
Jim11055=SUM(C3:E3)=AVERAGE(C3:E3)=(1+$B$10)*G3=IF(G3<30, “Work harder”, “Good Job”)
Mary275205=SUM(C4:E4)=AVERAGE(C4:E4)=(1+$B$10)*G4=IF(G4<30, “Work harder”, “Good Job”)
Tobias Reiper3125 =SUM(C5:E5)=AVERAGE(C5:E5)=(1+$B$10)*G5=IF(G5<30, “Work harder”, “Good Job”)
Mario41355=SUM(C6:E6)=AVERAGE(C6:E6)=(1+$B$10)*G6=IF(G6<30, “Work harder”, “Good Job”)
Student Number=COUNT(B3:B6)
Max Average=MAX(G3:G6)
Min Average=MIN(G3:G6)
# Marks < 30=COUNTIF(G3:G6, “<30”)
Number of spanish marks entered=COUNTA(E3:E6)
Number of spanish marks Missing=COUNTBLANK(E3:E6)


Give examples of the following functions:

Sum, average, max, min , count, counta, countif , date , if 

© 2019  Vedesh Kungebeharry. All rights reserved. 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s