Oz du Soleil shows how to write formulas in Get & Transform in Excel 2016. This video provides an example of nesting an AND inside an IF.
- [Instructor] Let's nest an and inside an if in Get & Transform. Here we've got applicant data and we're trying to get a list of who we're going to interview and who's going to be declined. And the criteria, there are two. The portfolio and the application scores have to sum to at least 13 and each score has to be at least five. So, look at Applicant 2. There is a total of 13, but because the application score is below five, that person is going to be declined.
Sorry. Okay, let's jump into this and make this formula. Data, From Table. Now, why are we going to do a formula here? Because we would need so many other columns in order to get this right, so we're going to write one custom column, rather than do this in a bunch of steps. So, here we go. We're going to add the custom column. Add, Custom Column.
Call it Decision. Now, we're going to do an if and we have to think in terms of if, then, else. We're also going to use and, so that we can insert our three criteria. If, Portfolio Score, greater than, equal to five and Application Score greater than, equal to five and Application Score plus Portfolio Score greater than, equal to 13, then, interview.
Else, decline. All right, there's a lot going on there. And notice we didn't need parentheses. The if statement, it's looking all the way across all of our and criteria until it sees the then and then it looks across for the else. So, let's click OK. Got it. Look at that. So, let's look. Applicant 2, correct, declined.
The four is below the requirement of five and, therefore, there is a decline, even though that person is above the total score of 13. And we easily did that without a bunch of extra columns, because we wrote a nested if/and formula.
- What is Get & Transform?
- Querying data
- Working with columns
- Using formulas
- Pivoting and unpivoting data
- Grouping data
- Appending a query
- Merging/combining data with joins