### 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

No description

by

Tweet## Emma Fooks

on 24 April 2013#### Transcript of Excel Functions

Excel Functions Let's start with

a bang! Use a bang to display or

use information from a

cell in another worksheet = NameOfWorksheet I feel an

'IF'

lurking around

the corner tests a single condition:

true or false? Let's talk about This is a bang (basically,

an exclamation mark) B5 CHALLENGE Write a function to make the information in cell A2 of a worksheet called 'Data' appear in another worksheet = Data!A2 CHALLENGE Write a function which adds the numbers in cell

A3 and cell A4 of a worksheet called 'Data' = Data!A3+Data!A4 Want to learn how make this quicker (and even use information from another workbook)? CHALLENGE Using the images below, show how you would create a function in the 'Functions' worksheet which adds the numbers in cell A3 of the 'Data' worksheet (Workbook 1) and cell A1 of the 'Data 2' worksheet (workbook 2). TRUE FALSE =IF(logical test, value if true, value if false) Does cell A3 of the 'Data' worksheet equal 10? =IF(Data!A3=10, "True","False") TRUE FALSE CHALLENGE Write an IF function with the following arguments:

Logical test - is the data in cell A5 of the 'Data' worksheet less

than 60?

Value if true - Good

Value if false - Bad = IF(Data!A5<60,"Good","Bad") 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. The function should produce the following results:

If cell A6 is less than 60 - Good

If cell A6 is less than 70 - Okay

If cell A6 is 70 or more - Bad =IF(A6<60,"Good",IF(A6<70,"Okay","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 in 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... =IF(AND(Data!D2>=3,Data!E3>=2),"Go home","Stay in work") It's 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 there are at least 2 more inches of snow are forecast. (This information is recorded in cells D2 and E2 of the 'Data' 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 called 'Data' is more than 5 =AND(Data!C2>5,Data!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?

AND function

returns value 'TRUE' All conditions false?

AND 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(AND(Data!M2>75,Data!N2<-1),"Ski",IF(OR(Data!N2>19,Data!O2="Summer"),"Sea","City")) IF CONGRATULATIONS, YOU MADE IT TO THE TOP! You've mastered: Using a bang

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 (all cells found in the 'Data' worksheet):

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(Data!A6<70,Data!B3>32,Data!C2=10,Data!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 =IF(OR(Data!G2>=15,Data!H2>45),"Plant","Don't Plant") (This information is recorded in cells G2 and H2 of the 'Data' worksheet.) 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 =IF(NOT(Data!J2="Comfy"),"Order new chair","Keep chair") Dan uses a spreadsheet to keep track of whether he needs to order new chairs for his colleagues (their chairs must be comfy!). Write an IF function with a nested NOT function which Dan could use to check whether he needs to order a new chair for each colleague. See 'Data' worksheet, cells J1:K6 =NOT(Data!A7="Mauve") A7 contains "Mauve"

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 transcripta bang! Use a bang to display or

use information from a

cell in another worksheet = NameOfWorksheet I feel an

'IF'

lurking around

the corner tests a single condition:

true or false? Let's talk about This is a bang (basically,

an exclamation mark) B5 CHALLENGE Write a function to make the information in cell A2 of a worksheet called 'Data' appear in another worksheet = Data!A2 CHALLENGE Write a function which adds the numbers in cell

A3 and cell A4 of a worksheet called 'Data' = Data!A3+Data!A4 Want to learn how make this quicker (and even use information from another workbook)? CHALLENGE Using the images below, show how you would create a function in the 'Functions' worksheet which adds the numbers in cell A3 of the 'Data' worksheet (Workbook 1) and cell A1 of the 'Data 2' worksheet (workbook 2). TRUE FALSE =IF(logical test, value if true, value if false) Does cell A3 of the 'Data' worksheet equal 10? =IF(Data!A3=10, "True","False") TRUE FALSE CHALLENGE Write an IF function with the following arguments:

Logical test - is the data in cell A5 of the 'Data' worksheet less

than 60?

Value if true - Good

Value if false - Bad = IF(Data!A5<60,"Good","Bad") 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. The function should produce the following results:

If cell A6 is less than 60 - Good

If cell A6 is less than 70 - Okay

If cell A6 is 70 or more - Bad =IF(A6<60,"Good",IF(A6<70,"Okay","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 in 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... =IF(AND(Data!D2>=3,Data!E3>=2),"Go home","Stay in work") It's 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 there are at least 2 more inches of snow are forecast. (This information is recorded in cells D2 and E2 of the 'Data' 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 called 'Data' is more than 5 =AND(Data!C2>5,Data!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?

AND function

returns value 'TRUE' All conditions false?

AND 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(AND(Data!M2>75,Data!N2<-1),"Ski",IF(OR(Data!N2>19,Data!O2="Summer"),"Sea","City")) IF CONGRATULATIONS, YOU MADE IT TO THE TOP! You've mastered: Using a bang

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 (all cells found in the 'Data' worksheet):

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(Data!A6<70,Data!B3>32,Data!C2=10,Data!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 =IF(OR(Data!G2>=15,Data!H2>45),"Plant","Don't Plant") (This information is recorded in cells G2 and H2 of the 'Data' worksheet.) 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 =IF(NOT(Data!J2="Comfy"),"Order new chair","Keep chair") Dan uses a spreadsheet to keep track of whether he needs to order new chairs for his colleagues (their chairs must be comfy!). Write an IF function with a nested NOT function which Dan could use to check whether he needs to order a new chair for each colleague. See 'Data' worksheet, cells J1:K6 =NOT(Data!A7="Mauve") A7 contains "Mauve"

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