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
Do you really want to delete this prezi?
Neither you, nor the coeditors you shared it with will be able to recover it again.
Make your likes visible on Facebook?
You can change this under Settings & Account at any time.
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