1) Absolute and relative references:
a. relative referencing: a formula as =A3*B3
b. absolute referencing (using the $ in $B$3) to reference the same cell in a replicated formula
2) Name cells and ranges: when an individual cell or an area of a spreadsheet is going to be used several
times within the formulae of a spreadsheet, it is often a good idea to give it a name
Highlight and Right click on cell/range → Define Name… which will open the New Name window
3) Functions you have to remember:
▪ SUM: sum a range of values
=SUM(A1:A4) or =SUM(A1, A2, A3, A4)
▪ AVERAGE: calculate the average in a range of values
=AVERAGE(A1:A4)
▪ MAX / MIN: find the largest / smallest number in a range
=MAX(A1:C20) / MIN(A1:C20)
▪ ROUND: round a number to a certain number of decimal places
=ROUND(A4, 1) – take the value in A4 and round it to 1 decimal place
▪ COUNT: count the number of cells containing a number
=COUNT(A1:B8) –(empty cells or cells containing text are ignored)
▪ COUNTA: count the number of cells containing a number OR text
▪ COUNTIF: count the number of cells containing a number that meets a certain condition
=COUNTIF($B$3:$B$10, “dog”) – count how many cells in the $B$3:$B$10 range contain the
word dog.
=COUNTIF(grades, “>=6”) count the number of cells in the range “grades” which are over 6
▪ IF: contains 3 parts: a condition (is the cell A5 equal to 5?), a True when the condition is met (display
A2*0.05) and a False when the condition is not met (display “No discount”)
=IF(A1=5,A2*0.05,"No discount")
▪ Nested IF: Nested functions means having one function inside another one.
=IF(C3>=10, "Very experienced", IF(C3>=5,"Experienced","Not experienced")).
If the value for experience is:
>=10 then display ‘Very experienced’
>=5 then display ‘Experienced’
Otherwise (<5) then display “Not experienced”
▪ SUMIF: sum the values in a range IF the cells in another range meet a certain condition
=SUMIF($B$3:$B$21, D4 , $C$3:$C$21) – sum the values in the $C$3:$C$21 range IF the
corresponding cell in the $B$3:$B$21 range contains the same value as A35.
▪ VLOOKUP: perform a vertical lookup of data: search for a lookup value in the left-most column of a
lookup array (which is a range, normally in a different table or sheet), and gives as output the fields
contained in the column of the selected column_index_number
= VLOOKUP (lookup_value, lookup_array, col_index_num, FALSE)
=VLOOKUP(B3, client.csv!$A$2:$B$8, 2, FALSE)
Remember that the result of a formula or function is a number or text, which can be used in other
calculations.
Example:
=10 + IF(A1>1, 5, A1*2) means that the result of the IF function will be added to 10, so if A1 contains a
number greater than 1 (the logical test / criteria), THEN the result will be 10 + 5 (the value if true),
OTHERWISE the result will be 10 + A1*2 (the value if false).
❖ CONDITIONAL FORMATTING
