Present Remotely
Send the link below via email or IM
CopyPresent to your audience
Start remote presentation Invited audience members will follow you as you navigate and present
 People invited to a presentation do not need a Prezi account
 This link expires 10 minutes after you close the presentation
 A maximum of 30 users can follow your presentation
 Learn more about this feature in our knowledge base article
Excel Functions v2
No description
by
TweetCarolina Valenzuela
on 6 March 2017Transcript of Excel Functions v2
Excel Functions
I feel an
'IF'
lurking around
the corner
tests a single condition:
true or false?
Let's talk about
TRUE
FALSE
=IF(logical test, value if true, value if false)
Does cell A3 of the 'Data' worksheet equal 10?
=IF(A3=10, "True","False")
TRUE
FALSE
these are called 'arguments'
logical test
value if true
value if false
Beyond
the basics
NESTING
'Nesting' is when a function lives inside another function
like Russian dolls
Formula 1
Formula 2
Nesting formulas lets you
specify more than
2 outcomes
For example: should I eat this biscuit?
A
comma
tells Excel that you
'
ve
finished writing an argument
I've
finished!
A
comma
tells Excel you've
finished writing an argument
If it has less than 30 calories 
yes!
If it has 30 calories 
maybe
If it has more than 30 calories 
sadly, no
This means
you can assess
complicated situations
=IF(B2<30,"Yes",IF(B2=30,"Maybe","Sadly no"))
AND now...
CHALLENGE
Write an IF function containing a 2nd, nested IF function. In the cell A2 introduce any number. The function should produce the following results:
If cell A2 is less than 60

Good
If cell A2 is less than 70

Okay
If cell A2 is 70 or more

Bad
OR
NOT
tests multiple conditions:
true or false?
All
conditions true?
AND function
returns value 'TRUE'
Any
conditions false?
AND function
returns value 'FALSE'
AND
One strike and
you're out!
=AND(condition1,condition2, condition3,...)
similar to logical test in IF function
(they must produce a TRUE / FALSE result)
you can
use
up to
30
conditions!
CHALLENGE
giving up
CHALLENGE
Let's take it a step further
by nesting an AND function
in an IF function...
It started snowing! You'll be allowed to go home early (hooray!) if there are already 3 or more inches of snow on the ground, and if at least 2 more inches of snow are forecast. (Insert this information in cells D2 and E2 of your worksheet.)
Write an IF function containing a nested AND function which describes the above conditions. The function should produce either the result 'Go home' or the result 'Stay in work'.
 whether cell C2 of a worksheet is more than 5
=AND(C2>5,C3>20)
Write an AND function which tests:
 whether cell C3 (of the same worksheet) is more than 20
tests multiple conditions:
true or false?
Any
conditions true?
OR function
returns value 'TRUE'
All
conditions false?
OR function
returns value 'FALSE'
Only sees what's true!
=OR(condition1,condition2, condition3,...)
conditions must produce TRUE / FALSE result
go big
go home
we're
O
R
like a
mirr r
argument produces
result
TRUE
it gives you the reversed logical value of its argument
NOT function
gives
result
FALSE
Ready for a really BIG challenge?
How about taking an IF function....
IF
CONGRATULATIONS, YOU MADE IT TO THE TOP!
You've mastered:
IF function
Nesting
AND function
OR function
NOT function
AND function
IF function
OR function
NOT function
CHALLENGE
Write an OR function which tests the following:
Is cell A6 less than 70?
Is cell B3 more than 32?
Does cell C2 equal 10?
Is cell D2 more than or equal to 3?
=OR(A6<70,B3>32,C2=10,D2>=3)
NESTING?
Ready for
another
spot of
CHALLENGE
Karl has decided he'll plant courgettes this year:
if his crop last year was 15 or more, or
if courgettes cost more than 45p in the shops
(Record this information in cells G2 and H2)
Write an IF function with a nested OR function describing this. The function should produce either the result 'Plant' or the result 'Don't plant'.
=NOT(logical value)
CHALLENGE
Camilo uses a spreadsheet to keep track of whether he needs to order new chairs for his employees (their chairs must be comfy!).
Write an IF function with a nested NOT function which Camilo could use to check whether he needs to order a new chair for each employee.
In cells J1, J2 and J3 write if the chairs are Comfy or Not Comfy.
=NOT(A7="Cookie")
A7 contains "Cookie"
Result:
FALSE
A7 contains anything else
Result:
TRUE
...and nesting
3
more functions in it?
AND
OR
IF
=
FINAL CHALLENGE!
You're helping a friend to decide whether to go skiing, to the seaside or to a city for their holiday. Write an IF function containing nested AND, IF and OR functions to help with this decision, based on the following criteria:
Available travel time is more than 75 minutes and temperature less than 1:
Ski
Temperature is more than 19 or season is summer:
Sea
Otherwise:
City
EXCEL
CHAMPION
Full transcriptI feel an
'IF'
lurking around
the corner
tests a single condition:
true or false?
Let's talk about
TRUE
FALSE
=IF(logical test, value if true, value if false)
Does cell A3 of the 'Data' worksheet equal 10?
=IF(A3=10, "True","False")
TRUE
FALSE
these are called 'arguments'
logical test
value if true
value if false
Beyond
the basics
NESTING
'Nesting' is when a function lives inside another function
like Russian dolls
Formula 1
Formula 2
Nesting formulas lets you
specify more than
2 outcomes
For example: should I eat this biscuit?
A
comma
tells Excel that you
'
ve
finished writing an argument
I've
finished!
A
comma
tells Excel you've
finished writing an argument
If it has less than 30 calories 
yes!
If it has 30 calories 
maybe
If it has more than 30 calories 
sadly, no
This means
you can assess
complicated situations
=IF(B2<30,"Yes",IF(B2=30,"Maybe","Sadly no"))
AND now...
CHALLENGE
Write an IF function containing a 2nd, nested IF function. In the cell A2 introduce any number. The function should produce the following results:
If cell A2 is less than 60

Good
If cell A2 is less than 70

Okay
If cell A2 is 70 or more

Bad
OR
NOT
tests multiple conditions:
true or false?
All
conditions true?
AND function
returns value 'TRUE'
Any
conditions false?
AND function
returns value 'FALSE'
AND
One strike and
you're out!
=AND(condition1,condition2, condition3,...)
similar to logical test in IF function
(they must produce a TRUE / FALSE result)
you can
use
up to
30
conditions!
CHALLENGE
giving up
CHALLENGE
Let's take it a step further
by nesting an AND function
in an IF function...
It started snowing! You'll be allowed to go home early (hooray!) if there are already 3 or more inches of snow on the ground, and if at least 2 more inches of snow are forecast. (Insert this information in cells D2 and E2 of your worksheet.)
Write an IF function containing a nested AND function which describes the above conditions. The function should produce either the result 'Go home' or the result 'Stay in work'.
 whether cell C2 of a worksheet is more than 5
=AND(C2>5,C3>20)
Write an AND function which tests:
 whether cell C3 (of the same worksheet) is more than 20
tests multiple conditions:
true or false?
Any
conditions true?
OR function
returns value 'TRUE'
All
conditions false?
OR function
returns value 'FALSE'
Only sees what's true!
=OR(condition1,condition2, condition3,...)
conditions must produce TRUE / FALSE result
go big
go home
we're
O
R
like a
mirr r
argument produces
result
TRUE
it gives you the reversed logical value of its argument
NOT function
gives
result
FALSE
Ready for a really BIG challenge?
How about taking an IF function....
IF
CONGRATULATIONS, YOU MADE IT TO THE TOP!
You've mastered:
IF function
Nesting
AND function
OR function
NOT function
AND function
IF function
OR function
NOT function
CHALLENGE
Write an OR function which tests the following:
Is cell A6 less than 70?
Is cell B3 more than 32?
Does cell C2 equal 10?
Is cell D2 more than or equal to 3?
=OR(A6<70,B3>32,C2=10,D2>=3)
NESTING?
Ready for
another
spot of
CHALLENGE
Karl has decided he'll plant courgettes this year:
if his crop last year was 15 or more, or
if courgettes cost more than 45p in the shops
(Record this information in cells G2 and H2)
Write an IF function with a nested OR function describing this. The function should produce either the result 'Plant' or the result 'Don't plant'.
=NOT(logical value)
CHALLENGE
Camilo uses a spreadsheet to keep track of whether he needs to order new chairs for his employees (their chairs must be comfy!).
Write an IF function with a nested NOT function which Camilo could use to check whether he needs to order a new chair for each employee.
In cells J1, J2 and J3 write if the chairs are Comfy or Not Comfy.
=NOT(A7="Cookie")
A7 contains "Cookie"
Result:
FALSE
A7 contains anything else
Result:
TRUE
...and nesting
3
more functions in it?
AND
OR
IF
=
FINAL CHALLENGE!
You're helping a friend to decide whether to go skiing, to the seaside or to a city for their holiday. Write an IF function containing nested AND, IF and OR functions to help with this decision, based on the following criteria:
Available travel time is more than 75 minutes and temperature less than 1:
Ski
Temperature is more than 19 or season is summer:
Sea
Otherwise:
City
EXCEL
CHAMPION