Select a Function to learn:

Basic Formulas: Count Formulas:

MUTIPLICATION COUNT
SUBTRACTION COUNTA
DIVISION COUNTIF
AVERAGE
MAXIMUM SUM (Adding) Formulas
MINIMUM SUM
INT SUMIF
IF Formulas
IF
NESTED IF

CLICK ABOVE
LOOKUP Formulas
LOOKUP
HLOOKUP
VLOOKUP
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

MULTIPLICATION
2
5
Used to multiply all the numbers in a
range of cells.
10
=H11*H12
BACK TO MAIN MENU

SUBTRACTION
50
10
Used to take numbers away from
each other.
40
=H11-H12
 10
50

-40
=H11-H12
Be careful about negative results
BACK TO MAIN MENU

DIVISION
50
Divides numbers in a range of cells. 10

5
=H11/H12
BACK TO MAIN MENU

MAXIMUM 2
3
4
5
10
Used to find the HIGHEST number in a 7
8
set of values 9

10
=MAX(H4:H12)
BACK TO MAIN MENU

MINIMUM 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

AVERAGE
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

INT
Removes decimals from numbers or calculation results
6.1

5.7 5.7

5 34
=INT(D12) =INT(H4*H12)
ation results

(H4*H12)
BACK TO MAIN MENU

SUM 1
2
3
1
2
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+I1
+I6+I7+I8+I9+I10+I11+I12
BACK TO MAIN MENU

COUNT 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 9
included)
8
=COUNT(H4:H12)
<<< This letter will not be counted
BACK TO MAIN MENU

COUNTA
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

COUNTIF MARCH
APRIL
MAY
JUNE
JUNE
Used to count the number of cells that AUGUST
contain particular data SEPTEMBER
OCTOBER
(in this example we are counting the NOVEMBER
number of times "June" appears)
2
=COUNTIF(G4:G12,"June")
BACK TO MAIN MENU

SUMIF D
B
C
1
2
3
D 4
E 5
D 6
Used to ADD together numbers that G 7
meet criteria that you set H 8
(in this example we are adding all I 9
numbers that are next to the letter "D")
11
=SUMIF(H4:H12,"D",I4:I12)
4:H12,"D",I4:I12)
BACK TO MAIN MENU

DISCOUNT?

IF
Yes
You get a discount

=IF(D10="Yes","You get a discou

discount")
Used to produce ONE OF TWO
OUTCOMES AGE
based on an IF QUESTION 18
(tested cells can contain numbers or You are old enough to drive
text)for letters or words
Use speech marks
=IF(L12>=17,"You are old enou
t a discount

s","You get a discount", "You do not get a

enough to drive
17,"You are old enough to drive", "You are not old enough to
drive")
BACK TO MAIN MENU
Example:
Football games have ONE o

NESTED IF 1) Home win

2) Away win
3) Draw

HOME TEAM G
Used where you have THREE OR MORE POSSIBLE
OUTCOMES based on an IF QUESTION
(multiple IF functions nested together)

=IF(H7>J7,"Home wi
"Draw"))
mple:
ball games have ONE of THREE possible outcomes:
Home win
Away win
Draw

HOME TEAM GOALS AWAY TEAM GOALS

4 4

RESULT: Draw

IF(H7>J7,"Home win", IF(H7<J7,"Away win",

Draw"))
BACK TO MAIN MENU

HLOOKUP
Used to LOOK UP data that is stored in ROWS

The "H" in HLOOKUP stands for "Horizontal"

Item Monitor Speakers Keyboard
Stock No 4 11 8
Price £87.99 £17.99 £4.95

Price of a Keyboard: £87.99

=HLOOKUP(G6,F6:I8, 3, FALSE)
BACK TO MAIN MENU

VLOOKUP
Used to LOOK UP data that is stored in COLUMNS

The "V" in VLOOKUP stands for "Vertical"

 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
Laptop 4 £299.00
DVD ROM 9 £9.99

Stock No of Laptops: 4
=VLOOKUP(H11,H3:J12,2,FALSE)