46 8.2 NESTING AND, OR, NOT FUNCTIONS INSIDE AN IF FUNCTION
46 8.2 NESTING AND, OR, NOT FUNCTIONS INSIDE AN IF FUNCTION
Emese Felvegi; Noreen Brown; Barbara Lave; Julie Romey; Mary Schatz; Diane Shingledecker; and Robert McCarn
Let us suppose you are in the process of deciding which university to attend for your Master Degree Program. You have a list of criteria that must be met, or you will not choose that school. You may have a list similar to the one below:
The university you want to attend:
- Must have a Graduate program.
- Must have a Business program.
- Must not put you into a debt greater than $20,000.
- Must lead to earnings of at least $60,000 per year after 10 years.
In our data dictionary, we can locate fields in our data set that will provide us with useful information.
- Graduate program related field and condition: [@HIGHDEG]=4
- Business program related field and condition: [@PCIP52]>0
- Debt related field and condition: [@[GRAD_DEBT_MDN_SUPP]]<20000
- Earnings-related field and condition: [@[MD_EARN_WNE_P10]]>60000
If you think about it, these conditions boil down to a few simple logical tests, like ones covered in Chapter 3 when we used the IF function to see if someone has passed or failed a test. Here, we have 4 logical tests that ALL must be TRUE before you can pick from a list of institutions that all meet your criteria. In our College Scorecard dataset and its related Data Dictionary, we have fields that we can use to filter an Excel table or a Pivot Table to narrow down our choices. However, in this chapter, we will look at using logical functions to find an answer.
and, or, not functions
IF you were fond of using logical functions, THEN you may find the use of the following functions in combination with our IF function. On their own, the AND, OR, NOT are logical functions that will help you evaluate up to 255 conditions and return a TRUE or FALSE value. The AND logical function determines if ALL conditions in a test are TRUE. The OR logical function determines if ANY conditions in a test are TRUE. The NOT logical function makes sure one value is not equal to another.
The syntax for these three functions are as follows:
=AND(logical1,[logical2], …)
=OR(logical1,[logical2], …)
=NOT(logical1,[logical2], …)
Let us use the AND function to test which institutions meet our the first two conditions from above, namely, that they have a graduate program (HIGHDEG=4) and they have more than 0 under the average % of enrolled students in their business program (PCIP52>0). We will use more criteria as we move along in this chapter.
- Open the College Scorecard Data Excel file you used for Chapter 6. (You can download a fresh copy from here.)
- Convert your data set into an Excel table so that your formula will use your field names (column headings) and will be easier to check for accuracy or to interpret.
- Considering you have over 120 columns in this data set, you can select, right-click, and hide columns you do not use for the moment. Insert a column next to the PCIP52 column that shows the pe