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