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:

1. Must have a Graduate program.
2. Must have a Business program.
3. Must not put you into a debt greater than $20,000.
4. 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.

1. Graduate program related field and condition: [@HIGHDEG]=4
2. Business program related field and condition: [@PCIP52]>0
3. Debt related field and condition: [@[GRAD_DEBT_MDN_SUPP]]<20000
4. 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.

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.

1. Open the College Scorecard Data Excel file you used for Chapter 6. (You can download a fresh copy from here.)
2. 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.
3. 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 percentage of business students. Rename the column TEST.
4. Start your expression by typing in =AND(, click into the first cell in the HIGHDEG column and set your criteria, then click into the PCIP52 column, and set your criteria for that field. You will see descriptive names due to the Excel Table structure instead of cell references. Press enter, and the column will autofill with your output as seen in Figure 8.2.1.
   

   

5. Alternatively, you can use the function library route of populating your formula with your arguments as seen in Figure 8.2.2 below.
   

   

6. You can now filter the data to show those institutions that meet your criteria. Complete the following practice exercises to add more logical tests to your formula.

nesting and, or, not inside an if function

Using AND, OR, NOT in conjunction with the IF function will let you return custom outputs or run calculations. You can automate the evaluation of logical tests by NESTING the AND, OR, NOT functions inside a single IF function. This means that if we have multiple conditions but we want to return a single output, we can nest any of the conjunction functions inside an IF and specify outputs accordingly.

Let us use the IF function and nest and AND function inside it to test which institutions meet ALL our four previous criteria (HIGHDEG=4, PCIP52>0, GRAD_DEBT_MDN_SUPP<20000, MD_EARN_WNE_P10>60000).  We nest the AND inside the IF function so that we can have an output other than TRUE or FALSE. In this instance, we want an output that says “This could be it!” if ALL our conditions are met, we want an output that say “No.” if any of my conditions are not met. 

1. Let us go back to our College Scorecard Data Excel file with an Excel table already inserted in it. Leaving columns from earlier practices will not impact how your formulas work, so there is no need to delete anything.
2. Start your expression by typing in =IF(, then immediately after that, add the AND( and add all your criteria. You will see descriptive names to make adding the criteria you want in your graduate school.
   “=IF(AND([@PCIP52]>0.15,[@HIGHDEG]=4,[@[GRAD_DEBT_MDN_SUPP]]<20000,[@[MD_EARN_WNE_P10]]>60000),”This is it!”,”No”)”
3. Press enter, and the column will autofill.
4. You can now filter the data to show those institutions that meet your criteria. Complete the following practice exercises to add more logical tests to your formula.

Attributions:

Chapter 8 by Emese Felvégi and Robert McCarn.  CC BY-NC-SA 3.0.