In the Feb 2016 release of Office 365, a plural IFS function. No more nested IF statements, with one oddity where the old IF function used to have value_if_false.
Transcript of the video:
Learn Excel from MrExcel podcast, Episode 1964. The IFS Function.
At last, podcast about the Black Excel, that was just crazy.
Let's get back to something really, really good here.
So one problem we've had for a long, long time, is when we have to nest IF statements, we have a bonus program. If you sell greater than 1000$, you get 100$.
Greater than 2000$, you get 200$. Greater than 3000$, 250$, greater than 5000$, 500$.
So we have FOUR IF statements nested! Boy, there used to be this really horrible limit of 7, now you can go to 32, but you should never go to 32, you should switch over to doing the TRUE, the range version of VLOOKUP.
Alright, so go look up A4 in this table, when you can't find the 5432, give us the value that is just less.
Both of those have been around forever.
But in the brand-new version of Excel that just came out, Office 365, came out February 2016, they are offering another new function: The plural of IF, the IFS function! Now here's the one thing that is a tiny bit weird about IFS.
So you start out with a logical test, IF the sales are less than 1000$, then you get 0, alright?
We THEN launch into the next logical test, so we want to check to see if the sales are less than 2000$.
Then you get 100$.
And you just keep going on, adding logical tests, and what to do if that's TRUE, logical test, and what to do if that's TRUE, logical test, and what to do if that's TRUE...
The thing that got really weird for me, as I was building this function, is when I got down to the end, I wanted to have that final, otherwise, do this, and instead, you have to put what that otherwise is.
So in this case anything greater than 5000$ gets the 500$! It felt very much like a regular IF function, I want to just be able to put a final single argument there, but instead, you have to specify what it is.
And now, of course, I'd be really careful, I couldn't just say Greater than 5000$, because then if someone told exactly 5000$, it would not know what to do! In fact, I just paused the video because, I wondered what it would do, if I hadn't done the >=5000, and had =5000?
We get an #N/A error, saying that none of these conditions are TRUE! I guess I could wrap it in an IF #N/A, but boy, that's a hassle.
Anyway, I applaud the Excel team, I know this is Joe, and his crew came up with the beautiful IFS function, and what a great, great new feature! So again we are looking for Office 365, you have to be in the First Release program, or have Home or Student.
You're looking for that version there to get this new functionality.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
At last, podcast about the Black Excel, that was just crazy.
Let's get back to something really, really good here.
So one problem we've had for a long, long time, is when we have to nest IF statements, we have a bonus program. If you sell greater than 1000$, you get 100$.
Greater than 2000$, you get 200$. Greater than 3000$, 250$, greater than 5000$, 500$.
So we have FOUR IF statements nested! Boy, there used to be this really horrible limit of 7, now you can go to 32, but you should never go to 32, you should switch over to doing the TRUE, the range version of VLOOKUP.
Alright, so go look up A4 in this table, when you can't find the 5432, give us the value that is just less.
Both of those have been around forever.
But in the brand-new version of Excel that just came out, Office 365, came out February 2016, they are offering another new function: The plural of IF, the IFS function! Now here's the one thing that is a tiny bit weird about IFS.
So you start out with a logical test, IF the sales are less than 1000$, then you get 0, alright?
We THEN launch into the next logical test, so we want to check to see if the sales are less than 2000$.
Then you get 100$.
And you just keep going on, adding logical tests, and what to do if that's TRUE, logical test, and what to do if that's TRUE, logical test, and what to do if that's TRUE...
The thing that got really weird for me, as I was building this function, is when I got down to the end, I wanted to have that final, otherwise, do this, and instead, you have to put what that otherwise is.
So in this case anything greater than 5000$ gets the 500$! It felt very much like a regular IF function, I want to just be able to put a final single argument there, but instead, you have to specify what it is.
And now, of course, I'd be really careful, I couldn't just say Greater than 5000$, because then if someone told exactly 5000$, it would not know what to do! In fact, I just paused the video because, I wondered what it would do, if I hadn't done the >=5000, and had =5000?
We get an #N/A error, saying that none of these conditions are TRUE! I guess I could wrap it in an IF #N/A, but boy, that's a hassle.
Anyway, I applaud the Excel team, I know this is Joe, and his crew came up with the beautiful IFS function, and what a great, great new feature! So again we are looking for Office 365, you have to be in the First Release program, or have Home or Student.
You're looking for that version there to get this new functionality.
Hey, I want to thank you for stopping by, we'll see you next time for another netcast from MrExcel!
