Chapter Demonstrable Skills
After completing the Excel Chapter 7 portion of the Excel & Data Analysis unit, students will be able to:
| Demonstrable Skill | Learning Activities & Informal Assessments |
Formal Assessments (excluding Exams) |
|---|---|---|
| Write nested IF functions. |
|
|
| Use AND, OR, and NOT functions. |
|
|
Chapter Notes
Nested IF statements
In Excel Chapter 2, we first learned about IF statements [1]. Chapter 7 adds some complexity [2]. Instead of a simple IF statement, we put some additional tests in place. A simple IF statement looks like this-
=IF(A1>59.5, "Pass", "Fail")
A nested IF statement will allow you to make a much more complex evaluation. Instead of simply doing pass/fail, you can evaluate for a particular letter grade.
=IF(A1>89, "A", IF(A1>79, "B", IF(A1>69, "C", IF(A1>59, "D", "F"))))
Note that all we do is "nest" another test and statement in until the end - when all tests have been exhausted, we just add an option (the "F") for when all other tests have not netted a result [2].
Tips & Tricks
Nested IF statements
Be sure to close the parentheses for all of the test statements in a nested IF statement [3].
References
- M. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 1. Upper Saddle River, NJ: Pearson Education, 2013.
- M. A. Poatsy, K. Mulberry, C. Krebs, L. Hogan, A. Rutledge, and E. Cameron, Exploring Microsoft Office 2013, 1st ed., vol. 2. Upper Saddle River, NJ: Pearson Education, 2013.
- “Use nested functions in a formula,” Microsoft Office Support. Available: https://support.office.com/en-us/article/Use-nested-functions-in-a-formula-9d7c966d-6030-4cd6-a052-478d7d844166?ui=en-US&rs=en-US&ad=US. Accessed: Apr. 29, 2016.