1 / 23

Excel Formulas II

Excel Formulas II. 3-D Formulas. 3-D Formulas are used to reference data on another spreadsheet in the same workbook Syntax: =[ name of sheet ] ! [ cells to reference ] Reference: Office 3D Formula. Create 3-D Formula (step 1). Click on worksheet tab where you want 3-D formula

wherry
Download Presentation

Excel Formulas II

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Excel Formulas II

  2. 3-D Formulas • 3-D Formulas are used to reference data on another spreadsheet in the same workbook • Syntax: • =[name of sheet]![cells to reference] • Reference: Office 3D Formula

  3. Create 3-D Formula (step 1) • Click on worksheet tab where you want 3-D formula • Select cell • Type =

  4. Create 3-D Formula (step 2) • Click worksheet tab for first reference • Select desired cell • Enter arithmetic operator

  5. Create 3-D Formula (step 3) • Click worksheet tab for second reference • Select desired cell • Press the Enter key

  6. Create 3-D Formula (step 4) • You should have a 3-D reference on the worksheet you started with

  7. Excel Logical Functions • IF • AND • OR • Reference: Office Logical Functions Know these Symbols!

  8. IF function condition What to show if TRUE What to show if FALSE • Excel IF function has three arguments: • A condition • What to do if condition is met • What to do if condition is not met • Reference: Office IF Function

  9. Create IF Function (step 1) • Click in cell where IF statement goes • Formulas ribbon • In the Function Library group: • click Logical▼ • IF

  10. Create IF Function (step 2) • In Function Arguments dialog box: • Logical_test • Use logical operator =, <, <=, >, >=, <> • Value_if_true • What to do if logical condition is True • Value_if_false • What to do if logical condition is False • Click OK

  11. Create IF Function (step 3) • The IF will have two results: • For TRUE • Logical condition was valid • For FALSE • Logical condition was invalid

  12. AND Function • AND function requires all logic statements to be TRUE to return TRUE • If one statement is FALSE, then the result is FALSE • If both statements are FALSE, then the result is FALSE • Reference: Exploring Office 2013 Volume 2 Excel Chapter 7 P. 312

  13. Creating AND Function (step 1) • Select cell • Formulas ribbon • In the Function Library group: • click Logical▼ • AND

  14. Creating AND Function (step 2) • In Function Arguments dialog box: • Logical1 • Enter the first logical condition • Logical2 • Enter second logical condition • Logical3 • Not needed • Click OK

  15. Creating AND Function (step 3) • If both numbers are smaller than 10 • TRUE • If the number in column A is bigger than 10 but the number in column B is smaller than 10 • FALSE • If the number in column B is bigger than 10 but the number in column A is smaller than 10 • FALSE • If both numbers are bigger than 10 • FALSE

  16. OR Function • OR is like the opposite of AND • As long as one logic statement is TRUE, the result is TRUE • Reference: Exploring Office 2013 Volume 2 Excel Chapter 7 P. 313

  17. Creating OR Function (step 1) • Select cell • Formulas ribbon • In the Function Library group: • click Logical▼ • OR

  18. Creating OR Function (step 2) • In Function Arguments dialog box: • Logical1 • Enter the first logical condition • Logical2 • Enter second logical condition • Logical3 • Not needed • Click OK

  19. Creating OR Function (step 3) • If both numbers are smaller than 10 • TRUE • If the number in column A is bigger than 10 but the number in column B is smaller than 10 • TRUE • If the number in column B is bigger than 10 but the number in column A is smaller than 10 • TRUE • If both numbers are bigger than 10 • FALSE

  20. Nested IF function • Nested means putting an IF function in another IF function • Nested IF functions are used to make three or more comparisons • Reference: Office Nested IF

  21. Creating Nested IF Function (step 1) • Select cell • Formulas Ribbon • In the Function Library group: • click Logical▼ • IF

  22. Creating Nested IF Function (step 2) • In the Function Arguments dialog box: • Logical_test • Type in one logical test • Value_if_true • You may type additional IF function here (see slide 8) • Value_if_false • You may type additional IF function here (see slide 8)

  23. Creating Nested IF Function (step 3) • In the example to the right: • If both numbers in columns A and B are less than 10 • it prints “Both values less than ten” • If the number in column A is bigger than 10 • It print “First value bigger than ten” • If the number in column B is bigger than 10 • It prints “Second value bigger than ten” • Reference: Exploring Office 2013 Volume 2 Excel Chapter 7 P. 310

More Related