Formulae and functions

Formulae

Used to perform calculations on cell contents

• In order to distinguish between text and formulae a symbol = needs to be typed in first

Functions

Specialised calculation that the spreadsheet software memorised. A function must start with an = sign and it must have a range of cells to which it applies in brackets after it.

• AVERAGE: =AVERAGE(A3:A10)
• MAXIMUM: =MAX(D3:J3) displays the largest number in all the cells from D3 to J3 inclusive
• MINIMUM: =MIN(D3:J3) displays the smallest number in all the cells from D3 to J3 inclusive
• MODE: =MODE(A3:A15) most frequenct number in the cells
• MEDIAN: =MEDIAN(B2:W2)
• SUM: =SUM(E3:P3) displays total of all cells
• ROUND: rounds a number correct to a number of digits that you specify, ROUND(number, number of digits) where the number is the number you want rounded off and the number of digits is the number of decimal places e.g, =ROUND(3.56678,2) = 3.57

• ROUNDUP: ROUNDUP(number, number of digits) always rounds the number up e.g, ROUNDUP(3.1,1) will give 4
• RANK: =RANK(A3,A1:A10) gives the rank of the number in cells A3 for all the numbers in cells from A1 to A10
• COUNT: =COUNT(C3:C30) count the number of numeric entries in the range
• COUNTA: =COUNTA(C3:C30) count a numebr of items or names of people we need to be able to count text entries
• IF: logical function because it makes the decision to do one of two things based on the vaue it is testing. Very useful because you can use it to test a condition and then choose between two actions based on whether the condition is true or false. e.g, =IF(B3>=50,"Pass","Fail"), this function tests to see if the number in cell B3 is greater than or equal to 50
• LOOKUP: - VLOOKUP: vertical table - HLOOKUP: horizontal table

Absolute and relative cell referencing

• Absolute reference always refers to the same cell. A reference to a cell used in a formula where, when the formula is copied to a new address, the cell address does not change
• Relative cell referencing refers to a cell that is a certain number of rows and columns away. When a cell is used in a formula and the formula is coped to a new address, the cell address changes to take account of the formula's new position