How to use an IS function in Excel

Intro:
Hello, and welcome to the CPA Australia podcast, your weekly source for accounting education, career, and leadership discussion.

Neale Blackwood CPA:
In the March 2021 article, I covered a set of functions that are commonly referred to as the IS functions. So these functions all start with the word IS, and they allow you to identify types of cells in Excel. So you can identify, for example, if a cell is blank, using a function called ISBLANK. And that's the one I use as an example with the VLOOKUP function. So VLOOKUP can return an error if the cell that it's looking up is blank. And so we can use the ISBLANK function to identify a blank cell. From a standard VLOOKUP function you add an IF function to use ISBLANK to identify if the cell you're looking up is blank first. And if it is for example, you can return a zero. If it's not blank, you can then do the B LOOKUP.

Neale Blackwood CPA:
So the formula would be equals if open bracket is blank and A1, for example, close the bracket, comma, and then you would put, let's say, a zero, comma, and then we'd do the VLOOKUP after that. And then close the bracket on the IF after the VLOOKUP function. The ISBLANK and all of the IS functions, actually, can only return true or false. So that means they work really well with IF functions in terms of the logical test, which is the first part of the IF function. Because it returns true or false, so you don't need to go ISBLANK, open bracket, A1 AND close the bracket equals true. You can just leave the equals true bit out because all of the IS functions return true or false. Even if they refer to a cell that has an error in it, they still only return true and false.

Neale Blackwood CPA:
Usually a function will show the same error that is in the cell, but in the case of the IS functions, they still only return true or false. In the example with the VLOOKUP, I also added the IFNA and the IFERROR functions to the formula to handle pretty much everything that can get thrown at a VLOOKUP. So funnily enough, the IF error and the IFNA have sort of developed from the ISERROR and an ISNA function. So in the old days before, IFERROR, and IFNA, we had to use ISERROR, ISNA, and there was another one called IS E double R, so ISRR. And those three were used in combination to handle the different errors. Nowadays, IFERROR, which was added in Excel 2007, and IFNA, which was added in Excel 2013, they allow you to handle errors a lot more easily than in the old days.

Neale Blackwood CPA:
I also covered some of the other IS functions. So there's ISNUMBER. Now ISNUMBER will identify a number. Now, just a heads up, dates and time are also treated as numbers by Excel. However, a text number, so sometimes when you import data, the numbers come in as text. And in that case ISNUMBER, when referring to a text number, will return false because if a text number, usually text numbers are left-aligned. So if there's no formatting been applied, anything that's left-aligned in Excel is treated, or Excel is telling you that as far as Excel is concerned, it's text. Now, a trick you can do with ISNUMBER is if you want to know if something could be a number, because if you've got a text number and you multiply a text number by one, that actually converts it into a real number. It's an easy way to convert a text number into a real number.

Neale Blackwood CPA:
And so what you can do, if you want to find out if an entry could be a number, you could use equals, ISNUMBER, open bracket, let's say A1 times one, close the bracket. And that will figure out if that number or that entry could be a number in Excel in terms of a calculation that you perform. The only thing that doesn't work with that technique is a blank cell, because when you multiply a blank cell by one, it actually returns a zero, which is a number. And so you might want to use ISBLANK with that as well. Just a heads up, some formulas can return a blank cell. So you can use an IF function and return a blank cell by using two double quotation marks together. And that will return a blank cell. That's actually a bit of a blind spot for the ISBLANK function.

Neale Blackwood CPA:
Because if a cell has a formula in it, ISBLANK returns false because there's something in that cell. ISBLANK only returns true for totally empty cells. So it should be called, ISEMPTY. The way around that. So if a cell has a formula in it that returns a blank cell, so it actually looks like a blank cell, you can use another way to do that. And I've given an example in the article and the video, basically, as an example, equals A1 equals, and then you use the two double quotes together again. So the same thing you use to display a blank, you can also identify a blank cell. The downside with using equals the double quotes is that that will generate an error if that cell does have a divide by zero error or a REF error or something like that. Whereas the good old ISBLANK just ignores errors and just returns false.

Neale Blackwood CPA:
There's an ISTEXT function. So if you want to identify if something has text in it, an ISFORMULA, the trick with the ISFORMULA is that the formula may be the same value, no matter what happens. So for example, if you have a formula that's equals one plus one, that's never going to change in terms of the formula, but it is still a formula because it starts with the equal sign. So anything that starts with the equal sign is treated as a formula by the ISFORMULA function. There's also one for ISLOGICAL. So that identifies cells that return true or false. Everything else returns false for ISLOGICAL. So a true and a false will both return true for ISLOGICAL. Also, I did cover some examples of IFERROR and IFNA and how you can use them together. And I finished off with the NOT function.

Neale Blackwood CPA:
Now, the NOT function can mess with your head a little bit in terms of, what it does is it switches the trues and the false around. So if you've got a function that's returning true, and you want to switch it to false, you can use the NOT function. So you can go equals NOT, open bracket ISBLANK, open bracket, A1, and close both brackets. And that will switch the result of the ISBLANK. Now it can be hard to follow sometimes using the NOT function. I know programmers use NOT a lot, but us mere mortals might need to maybe do it another way. And the easy way to do it is to compare the ISFUNCTION with false. So for example, you can go equals ISBLANK, open bracket, A1, close the bracket, equals false. Now by doing equals false, you actually do the same thing as the NOT.

Neale Blackwood CPA:
So you switch the results. So if it was true, that gets converted to false. If it was false, it gets converted to true. Why would you want to switch? The main reason is for things like conditional formatting. A conditional formatting formula must return true for the formula to trigger the conditional format that you want to display. And so you've got to set your formula up so that it equals true. And so, depending on what you are using, you might need to that equals false, or the NOT function to get it to be true. Hope you found that useful. Thanks for listening.

Outro:
Thanks for listening to the CPA Australia podcast. For more information on today's episode, please visit the show notes at www.CPAaustralia.com.AU/podcast. Never miss an episode by subscribing to our podcast on Apple Podcasts, Spotify, or Stitcher.