Send the link below via email or IMCopy
Present to your audienceStart 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
Transcript of Excel Functions
a bang! Use a bang to display or
use information from a
cell in another worksheet = NameOfWorksheet I feel an
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
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?
returns value 'TRUE' Any conditions false?
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
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?
returns value 'TRUE' All conditions false?
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
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
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
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