1 / 18

Excel: Logical Tests

Excel: Logical Tests. Some text and examples used with permission from: http://www.jegsworks.com Note: We are not endorsing or promoting religious doctrine, but simply taking advantage of this website for educational purposes . Computer Information Technology Section 6-17. Objectives.

ikia
Download Presentation

Excel: Logical Tests

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: Logical Tests Some text and examples used with permission from: http://www.jegsworks.com Note: We are not endorsing or promoting religious doctrine, but simply taking advantage of this website for educational purposes. Computer Information Technology Section 6-17

  2. Objectives • The student will • Understand conditional expressions and how they are used. • Know the format for the “IF” function in Excel. • Understand the logical operators. • Understand how to set conditional formatting in a cell

  3. Excel: Logical Tests • Some functions do not calculate values but instead do logical tests using logical comparisons like =, < , and > or the combinations <=, >=, <>. • Such a test allows you to do one thing when the comparison is TRUE and something different when it is FALSE.

  4. Excel: The IF function • The IF function is the logical test that is used the most. It has three arguments inside parentheses which are separated by commas: • the comparison statement • the cell value to use when the comparison is true • the cell value to use when the comparison is false. • This is also know as IF – THEN – ELSE • If it is true then do this, else do that

  5. Excel: The IF function • The general form of an IF function is: =IF(logical comparison, value if TRUE, value if FALSE) • A value can be a number, text within double quotes, a cell reference, a formula, or another logical test.

  6. Excel: Logical Operators

  7. Excel: Logical Operators

  8. Excel: Nesting Statements • You can nest up to 7 If statements to create complex tests. For example, to calculate your letter grade based on your percent score I use the following statement: =IF(F4>0.895,"A",IF(F4>0.795,"B",IF(F4>0.695,"C",IF(F4>0.595,"D","F"))))

  9. Conditional Formatting • Conditional formatting: uses a logical test to apply one format for a cell when the test is true and a different format when it is false. • For example, you could format positive amounts with a green cell fill and negative amounts with a red fill.

  10. Excel: Conditional Formatting • If I want to automatically put a red fill in all cells for students who are failing my first period class. • First step is to select the cells containing the grades

  11. Excel: Conditional Formatting • In the Style group on the Home tab click Condition Formatting • This brings up the conditional formatting menu:

  12. Excel: Conditional Formatting • In this case we want to highlight the cells with a certain value. • Choose Highlight Cells Rules • Choose Equal to

  13. Excel: Conditional Formatting • Once a value (F) is entered for Format cells that are EQUAL TO: the formatting is applied. • There are a number of build in formats, or create a custom format. • I want a bright red background

  14. Excel: Conditional Formatting • Formatting Dialog box pops up • Click on Fill • Choose Red • Click OK

  15. Excel: Conditional Formatting • Click OK • All Fs will now be red

  16. Excel: Conditional Formatting • You can add other formatting conditions. • I can shade all As green putting in second condition using the same steps. • The file now looks like this

  17. Summary • The IF function is used to set values or formulas based on conditions • The format for the IF function is: • Conditional formatting is used to set the formatting based on a certain condition. • =IF(logical comparison, value if TRUE, value if FALSE)

  18. Rest of today • From the Hancock website download: Call attention to the good or the bad.xls Homework 6-17 • Do Exercises 1 and 2 in Homework 6-17 – Save the file as Homework 6-17 • Do not print – Show me the file on your screen to get credit. • Mavis Beacon for 15 minutes

More Related