Oz du Soleil shows how to write an IF statement in Get & Transform in Excel 2016.
- [Instructor] Right time to work with if statements in Get & Transform, and if you want to follow along, pull up the exercise file called IF Formula. Here I've got a list of candidates who are trying to get into this special program, and we've reviewed their portfolio and the application package and we want to interview anybody who gets 13 or more. So we would like to have a column that tells us interview or decline, so let's do that in Get & Transform, select the data set, Data, From Table.
The first thing we need to know is what is the total? So for candidate one, a six for portfolio, and a four for application, that's a total of 10. We've got to get a sum. So we need to know that candidate one scored a total of 10, 6 +4, and then we have to see where that score is relative to the number 13. There are a number of ways we can do this. We could do a sum column and then do a conditional column, that might be what you're thinking but no, let's just write a formula and be done with it.
Okay add column, add custom column. Let's call it result. Now we're going to write the IF statement. We want to add the two columns together, get that total, and then see where it is in relation to 13. So let's do that. If (portfolio score + application score), greater than or equal to 13, then interview.
Else decline. And notice this format. We have to think in terms of if, then, else. Also this is case sensitive. If we were in native Excel this would be a capital If. Now watch this. We have an error and I'm going to say show me the error. That's not the error. You've got to be aware of that kind of weird stuff in Get & Transform.
So I'm going to go back and fix this. Okay now in our results we can see that it's working. Candidate 10 has a 7 and a 6 that total 13 and our column says interview candidate 10. Now I just want to reiterate that we could have done a conditional column to get the decline interview, fill in that column, but we would have had to add a sum column in order to get that.
So by being able to write a formula we were able to do this all in one step.
- 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