Use the AND function, as in =IF(AND(B1>0,C1<9),"OK",0) for compound logical tests; use OR for similar constructions. Use AND and OR together for more complex constructions; use NOT to test for the non-existence of a condition. Use Alt+Enter when editing to introduce line breaks that improve readability.
- [Voiceover] Sometimes when you're using the "IF" Function,…you may need to check on multiple criteria…before deciding different results.…Let's imagine in this worksheet called "Compound IF",…we're about to check to see if a job rating…is greater than four or five and a status is Full Time.…Now you can use a function called "AND"…sometimes it's a stand-alone function.…Here's how it might work that way…=and(…I wanna check to see if someone's status,…for example, in the cell right here equals full time.…
When you're checking for text entries,…we use double quotes to embed the text.…Now the spacing is absolutely critical,…oddly enough the upper/lowercase isn't,…although why not use upper/lowercase as we see it here?…But it's the space that's most critical.…Since Full Time as you see it in column H…does have a space between Full and Time,…we want to check to see if this is true;…comma…and if cell J2 is greater than three;…and we could have other criteria in here as well.…
The AND Function can be followed by…not just two but many, many.…
Author
Dennis Taylor
Released
5/26/2016- Understanding how the hierarchy of operators affects formula results
- Knowing when to use absolute vs. relative references
- Using Formula Builder for unfamiliar functions
- Displaying a worksheet's formulas and highlighting formula cells
- Converting formulas to values
- Creating 3D formulas to gather data from multiple sheets
- Creating and expanding nested IF functions; using AND, OR, and NOT with IF
- Looking up information with VLOOKUP, HLOOKUP, MATCH, and INDEX
- Analyzing data with the statistical functions: MEDIAN, MODE, etc.
- Using the power functions: COUNTIF, SUMIF, COUNTIFS, AVERAGEIFS, and more
- Calculating financial data such as payments
- Performing basic math
- Calculating dates and times
- Editing text with functions
- Using array formulas and functions
- Referencing data in other cells and files
- Extracting information from Excel worksheets
Skill Level Intermediate
Duration
Views
-
Introduction
-
Welcome50s
-
Exercise files25s
-
-
1. Formula and Function Tools
-
2. Formula and Function Tips and Shortcuts
-
3. IF and Related Functions
-
4. Lookup and Reference Functions
-
Use MATCH and INDEX together4m 40s
-
5. Statistical Functions
-
6. Power Functions
-
7. Selected Financial Functions
-
8. Math Functions
-
9. Date and Time Functions
-
10. Text Functions
-
11. Array Formulas and Functions
-
12. Reference Functions
-
13. Information Functions
-
Conclusion
-
Next steps25s
-
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: Create compound logical tests: AND, OR, and NOT with IF