Creating Blank Cells For Good Reasons with IfError Formula and if(isBlank) Formula

About this Tutorial

There are surprisingly few times you want to have blank cells, or even create blank cells. In this video I'll show you the good reasons you want blank cells.

Video Transcript

<div> Hi. In this video we're gonna be talking about two formulas that I think are really important when showing data , especially with money or really with anything. So I've created this little basically test here. We're showing revenue every single month, expenses profit. Nice little summary page. But two things that perhaps might be of used to. Understanding, what each of these numbers means is we'd like to know each month, what is a profit margin? What percentage are we making above our revenue or from expenses, right?

And also we'd like to understand on a rolling basis, month to month from the start of the year, how much are we making per month, right? So just I wanna know. I already see a trend right here with this number , but I wanna see an average, right? We're making so much in January, and then down a little bit and up and down. And if these numbers are up and down so much, I wanna see a rolling average to really see is our work paying off? So I wanna see a rolling number here, but there's two problems with these formulas. So let me do these formulas and I'll show you the problems.

So the profit margin, It'd be pretty simple profit divided by revenue. And I don't want to have to come here. Oh, I wanna make that a percentage, right? I don't really wanna come back here and have to fill out this column every single month, or even if you're doing this like week by week, that's 52, 12 times a year isn't so bad, but 52 times, or if you're doing daily you just saving. Having to do this one formula every single day you'll wanna just, quickly copy and paste this, right?

But you have this very ugly divide by zero problem because we've, we haven't gotten to August yet, right? Maybe you're even creating this chart with nothing. If you haven't, if you're starting creating this in December and you're just gonna have all these divide by zero, very ugly numbers to. So if error helps us here. So we wrap this in an if error parenthesis. And you see here it says if error , value, and value of error . So just wrap this and we say, okay, comma and what we're gonna do is we're not gonna fill out anything. You could put a message here. No numbers.

And see, it doesn't change the ones that have an issue, but it does have these, this text, and this text is just needless it's unnecessary. So what I prefer to do actually, is to just do nothing here. Just click, enter, copy and paste this down. And now we have blanks. Now if we go and fill this information out, let's say we had a really good month and we had we had a lot of expenses. But our problem margin, oh, 31 pretty damn good, right? And it automatically fills it out once you have this information because now there's no error .

And if there's an error , it's just gonna be blank. And this is really good if you do not need to know that there's an error . If there's gonna be an error and you wanna just get rid of it, then this is, if error is probably the best thing to wrap your formula around. But sometimes there are numbers.  and we just don't wanna see the numbers until we have something filled out. So that happens when we wanna see revenue per month.

So revenue per month formula is gonna be equals let's say it's at least some of this, so the end of this, but we wanna do it month to month. So we just want to do at least happens b2. For January, we are, we're just taking January. We're dividing it by, let's say count all. Cause we'll eventually have some count. This is, guess just gonna be one and we're gonna do the same B2 to b2. Now, as we copy and paste this, we want the second part of this these ranges to increase. We don't want this one to increase, so we'll just say, we'll hook we'll put the dollar.

In front of this first two to make sure that stays. And then see now we have a rolling average. So now this average is going to be this added up divided by th divided by the count number . So it's gonna be nine. Thousand plus 12,000 divided by two. And see each one. The second number is increasing. And we're getting a nice ruling. Yeah. We know we're going down cause we're not doing much revenue, but we're gonna come back up pretty damn soon. And now we're doing really well. But what happens is when we get down to month, we have not done yet. It's the same.

And so we may want to do this, we wanna may want to set this up. Again, December, and these will all be zeros and that's okay, right? Like it, it's fine to have this number , but it's unnecessary because this number doesn't tell you any information. We'd rather have a blank here until we have these filled out. So that's where if is blank comes in really handy. So you go, if is blank and if and is blank is a formula and we wanna. Let's say we don't want to know until we get this number B 10 and if it's blank.

So what's gonna happen is we'll put in, if it's blank, we'll put in something and if it's false, if this actually is not blank, it'll do this formula . So again, here I like to do nothing, so don't put in value there and it's blank again. You can say, you can put any message need a revenue number . And now you'll have a message. And once we have a revenue number there, we got a number . But really, here's a funny thing, right? We have this number now, but we don't have expenses. If this number were say, profit per month or expenses per month, you wanted to add more and more.

Right now we only need to have revenue, so we just need that. But if you want to say, okay, I want revenue and expenses.  Or revenue and profit say, what you can do is wrap it again. So you can say if, do another if is blank. And put in here, say the expenses column. And now again, I'm gonna do a little comma, comma. And we have. Oh, and now we just need to wrap this, I think. And oh, that's why. Cuz it's D . That's D See there, now it's blank, right? And now it's gonna be blank until we put in a number here. So it needs both of these. Need revenue number so you can even do.

A little bit. If you know what order they have to go in, you can say something like, need expense need expenses. So you can go, okay, we're going to, we know we're gonna know our expenses before we know our revenue. Oh, now we need our revenue. Okay. That's why truly, I don't want any of these numbers cause I know I'm gonna, I'm gonna put in those numbers. So I really want nothing. So I just want these messages to go away. Oops. Just want the messages to go away. I'm gonna leave 'em blank.

And so now they're just blanks and these numbers will magically appear when October rolls around and I put in a number . , I put in this number . Boom. We have it right. We just put in any old numbers here and we have these rolling out revenue per month. We can see we started the year with $12,000 a month. We ended the year at $15,000 a month. Fantastic. Great. Oh, we got need some more numbers here. Even better.

21,000 Great year.  and so this is, shows you how, if you have, if you know you're gonna get an error , you can use if error formula to keep 'em blank until you have those numbers or if you know you're gonna have a number and that number doesn't matter. You can use if is blank and the if is blank is actually two formulas together. It's the is blank, which is a true false and an if. And the true and false, depending on if is blank, is true or false. It'll either be the first one if it's it is blank. True, or it'll be the second argument .

, or sorry, the second, third argument , if it's true and you just put your formula there, keep your formula there, and now you have a really, what looks like a complicated if statement if heuristic, but it helps you very nicely keep all of this information neat. If we were just starting in, January and had all of this this looks terrible. . Yes. Looks great. And now these numbers will magically appear once you fill out revenue and expenses and profit. Hope that helps you figure out and make your sheets better and more aesthetically pleasing. In advance. Thanks. Bye bye.</div>