45 8.1 Nested IF Functions

the IF Function

In Chapter 3.2, we used the IF function to make logical comparisons between student scores and a threshold for student success. In its simplest form, the IF function translated into something like this: IF the value in a cell is what you expect (TRUE), THEN do this, ELSE If not (FALSE) do something else. Let’s say that we are looking for a university with more than 15% of students enrolled in business programs.  We can have an IF function evaluate over records for institutions in our College Scorecard dataset and automate that process for me. Form the Data Dictionary that came with the College Scorecard data,  we know that we have to evaluate the contents of the PCIP52 field. Let us use a logic function to do this quickly.

Remember, the IF function has three arguments:

1. Logical test – Here, we can test to see if the value in a selected cell is what we expect. Does this institution have more than 15% of its students enrolled in business programs [@PCIP52]>.15?
2. Value_if_true – If the requirements in the logical test are met, the value is [@PCIP52]>.15, then it is said to be TRUE. For this argument, you can type in text – “True”, or “High Business!” Depending on your value, you could insert a calculation.
3. Value_if_false – If the requirements in the logical test are not met, the value is [@PCIP52] is lesser than or equal .5, then it is said to be FALSE. For this argument, you can type in text – “FALSE”, or “Not That High.” If you want Excel to ouput nothing at all in the cell, then type “” (two quotes) and your result will be a blank cell.

NESTING AN IF FUNCTION

We can use an output such as the above to count fields where our criteria was TRUE for and make a decision based upon that. We can filter for “High Business” or “Not That High”. However, if we want more than one outcomes, then we can nest an IF function inside another IF function to have Excel evaluate all our criteria to output values accordingly. We can set criteria based on ranges of values we might find relevant for our category. The following are proprietary ranges that were setup at random.

We want to build an IF function that will have three outcomes: “High”, “Some”, “None” based on thresholds that align with values such as above 15% for “High”, greater than 0% but less than15% for “Some”, 0% for “None”.

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 BUSINESS %.
4. Start your expression by typing in =IF( and then click into the first cell in the PCIP52 column and set your criteria, then set your output for TRUE (“High”). Next, nest an IF inside your existing IF and add your second logical test followed by the third as the FALSE output for this portion.
5. Click ENTER and see how the formula populates the PCIP52 column.

Attributions:

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