Select a Function to learn:
Basic Formulas: Count Formulas: IF Formulas
MUTIPLICATION COUNT IF
SUBTRACTION COUNTA NESTED IF
DIVISION COUNTIF
AVERAGE
MAXIMUM SUM (Adding) Formulas LOOKUP Formulas
MINIMUM SUM LOOKUP
INT SUMIF HLOOKUP
VLOOKUP
CLICK ABOVE
BACK TO MAIN MENU
This is the 'Function Bar'
Select a function from the list
Or you can type the Function you want
Click 'OK' when you are finished
BACK TO MAIN MENU
2
5
Used to multiply all the numbers in a
range of cells. 10
=H11*H1
2
BACK TO MAIN MENU
50 10
10 50
Used to take numbers away from
each other.
40 -40
=H11- =H11-
H12 H12negative results
Be careful about
negative results
BACK TO MAIN MENU
50
Divides numbers in a range of cells. 10
5
=H11/
H12
BACK TO MAIN MENU
1
2
3
4
5
10
7
Used to find the HIGHEST number in a
8
set of values 9
10
=MAX(H4:H12)
BACK TO MAIN MENU
1
2
3
4
5
10
7
Used to find the LOWEST number in a 8
set of values. 9
1
=MIN(H4:H12
)
BACK TO MAIN MENU
1
1
3
4
5
9
6
Returns the AVERAGE of a set of numbers. 8
8
5
=AVERAGE(H4:H12)
BACK TO MAIN MENU
Removes decimals from numbers or calculation results
6.1
5.7 5.7
5 34
=INT(D12 =INT(H4*H12)
)
BACK TO MAIN MENU
1 1
2 2
3 3
4 4
5 5
Used to ADD all of the numbers 6 6
in a range of values 7 7
8 8
9 9
45 45
=SUM(F4:F12) =I4+I5+I6+I7+I8+I9+I10+I11+I12
BACK TO MAIN MENU
1
2
3
4
5
6
Used to count the amount of cells that p <<< This letter will not be counted
contain numbers 8
(cells containing words are not included) 9
8
=COUNT(H4:H12)
BACK TO MAIN MENU
MARCH
APRIL
MAY
JUNE
AUGUST
Used to count the amount of cells that SEPTEMBER
contain numbers OR text OCTOBER
(empty cells are not included) NOVEMBER
8
=COUNTA(H4:H12)
BACK TO MAIN MENU
MARCH
APRIL
MAY
JUNE
JUNE
Used to count the number of cells that AUGUST
SEPTEMBER
contain particular data OCTOBER
(in this example we are counting the numberNOVEMBER
of times "June" appears)
2
=COUNTIF(G4:G12,"June")
BACK TO MAIN MENU
D 1
B 2
C 3
D 4
E 5
D 6
Used to ADD together numbers that meet G 7
criteria that you set H 8
(in this example we are adding all numbers I 9
that are next to the letter "D")
11
=SUMIF(H4:H12,"D",I4:I12)
BACK TO MAIN MENU
DISCOUNT?
Yes
You get a discount
=IF(D10="Yes","You get a discount", "You do not get a discou
Used to produce ONE OF TWO OUTCOMES
based on an IF QUESTION AGE
18
(tested cells can contain numbers or text)
You are old enough to drive
Use speech marks for letters or words
=IF(L12>=17,"You are old enough to drive", "You are not
unt", "You do not get a discount")
nough to drive", "You are not old enough to drive")
BACK TO MAIN MENU
Example:
Football games have ONE of THREE possible outcomes:
1) Home win
2) Away win
3) Draw
HOME TEAM GOALS AWAY TEAM GOALS
Used where you have THREE OR MORE POSSIBLE
4 4
OUTCOMES based on an IF QUESTION
(multiple IF functions nested together)
RESULT: Draw
=IF(H7>J7,"Home win", IF(H7<J7,"Away win", "D
ossible outcomes:
Y TEAM GOALS
J7,"Away win", "Draw"))
BACK TO MAIN MENU
Item Monitor Speakers Keyboard
Stock No 4 11 8
Price £87.99 £17.99 £4.95
Used to LOOK UP data that is stored in ROWS Price of a Keyboard: £87.99
=HLOOKUP(G6,F6:I8, 3, FALSE)
The "H" in HLOOKUP stands for "Horizontal"
BACK TO MAIN MENU
Item Stock No Price
Monitor 5 £78.00
Speakers 14 £12.99
Keyboard 32 £4.95
Mouse 23 £19.95
Motherboard 18 £70.00
Hard Drive 6 £65.00
Flash Memory 45 £12.00
Used to LOOK UP data that is stored in COLUMNS Laptop 4 £299.00
DVD ROM 9 £9.99
The "V" in VLOOKUP stands for "Vertical"
Stock No of Laptops: 4
=VLOOKUP(H11,H3:J12,2,FALSE)
2,2,FALSE)
