Spreadsheet Functions

Functions

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

Functions are written in the following format:

=FunctionName(parameter1, parameter2, …, parameterN)

e.g

=sum(e3, d4, r5),

=max(a5,a27,b44)

=SUM(D3,D4,D5,F3:F4)

a function with a single parameter is shown below:

=FunctionName(parameter)

e.g.

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

=ABS(-44)

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.

Download the excel file here:

Student	Roll No	Math	French	Spanish	Totals	Average	Bonus Score	Pass/Fail?
Jim	1	10	5	5	20	7	10	Work harder
Mary	2	75	20	5	100	33	50	Good Job
Tobias Reiper	3	12	5		17	9	13	Work harder
Mario	4	13	5	5	23	8	12	Work harder



Bonus	50%
Student Number	4

Max Average	33
Min Average	7

# Marks < 30	3
Number of spanish marks entered	3
Number of spanish marks Missing	1


Student	Roll No	Math	French	Spanish	Totals	Average	Bonus Score	Pass/Fail?
Jim	1	10	5	5	=SUM(C3:E3)	=AVERAGE(C3:E3)	=(1+$B$10)*G3	=IF(G3<30, “Work harder”, “Good Job”)
Mary	2	75	20	5	=SUM(C4:E4)	=AVERAGE(C4:E4)	=(1+$B$10)*G4	=IF(G4<30, “Work harder”, “Good Job”)
Tobias Reiper	3	12	5		=SUM(C5:E5)	=AVERAGE(C5:E5)	=(1+$B$10)*G5	=IF(G5<30, “Work harder”, “Good Job”)
Mario	4	13	5	5	=SUM(C6:E6)	=AVERAGE(C6:E6)	=(1+$B$10)*G6	=IF(G6<30, “Work harder”, “Good Job”)



Bonus	0.5
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