Benefits of NA Values

Quick reference

• 00:05 What I want to focus on now is building for the future, and
• 00:08 what happens in a specific scenario.
• 00:11 Let's go and delete this data for just a second here.
• 00:15 And I want you to think about how this works when you're working from January all
• 00:19 the way through June and you keep adding data to your chart it builds out, and
• 00:22 it starts to look like this.
• 00:24 And this is what we'd like to see, because let's face it, it's focusing on January to
• 00:27 June, these are the only pieces of the year that have happened yet so
• 00:30 everything looks pretty good.
• 00:31 And I'm gonna bring back the data.
• 00:33 I'm going to say all right, but I need you to build your chart so
• 00:37 that it will actually take data all the way to December.
• 00:40 So what you'd probably do is build out your data, you'd use some if statements in
• 00:44 here to get the data, or even, let's pretend it just doesn't exist yet.
• 00:48 But you've built it out with a framework.
• 00:51 And this chart doesn't look so good.
• 00:53 Because now, even though we don't have data to fill in these gaps,
• 00:57 we still see July through December plotted on the axis and this is not ideal.
• 01:01 But how do you go back and say, well yeah, I need to get rid of this stuff until it
• 01:06 actually happens if you're building for future.
• 01:08 And this is the challenge that I wanna deal with now.
• 01:09 It's how do we build this chart so that it shows just the current range and
• 01:13 extends automatically as we add new data to it?
• 01:17 So, the first thing that I wanna call out before we do this,
• 01:19 I wanna drive this based on the results of this cell.
• 01:22 So I wanna be able to say, hey we're gonna do our forecast up to this point.
• 01:26 Just for May, going forward, is gonna be blank and
• 01:29 I wanna suppress the entire thing.
• 01:31 Now I made a small change to the index function that you saw in the last video.
• 01:36 Instead of picking up my index of the range of dates from down below here,
• 01:42 these guys I wanna change up dynamically,
• 01:44 which means I can't index them if they don't exist.
• 01:47 So, what I've done is I've pointed this to a different area.
• 01:50 We have another area at the top here that actually has my original dates.
• 01:53 These are not going to be supressed.
• 01:55 They're always going to exist in my data.
• 01:57 People aren't necessarily going to look at them, but
• 01:59 they're always going to be there.
• 02:00 So that's where I pointed my index across and said give me the first row, and
• 02:04 tell me what's the matching data point for H35, which is 5,
• 02:07 that's why it's gonna bring back May.
• 02:11 With that in place, I can now start toggling and
• 02:14 playing around with the data that we have down here.
• 02:16 Now, I want to show you something kind of interesting.
• 02:20 If I go and delete the two data points in December,
• 02:24 you'll notice that the December data points don't plot anymore.
• 02:27 Of course if I were to put in zeros, they do, but they plot as zeroes.
• 02:31 So I definitely don't want to see that happening because that's inferring that
• 02:35 there's no sales where, in fact, it just means they haven't happened yet, so
• 02:39 we'll just undo that for a second.
• 02:41 But how do I make December go away?
• 02:44 And the secret to doing this is to trigger one of everyone's favorite errors.
• 02:48 And basically we do it like this.
• 02:51 =NA().
• 02:52 And when you hit enter, you'll notice that this triggers in the cell a #NA error.
• 02:59 Nobody likes these.
• 03:01 Except your chart.
• 03:03 Notice that December just disappeared.
• 03:06 So that's pretty cool because it doesn't have any data, so we can actually use
• 03:10 an if function to return a #NA error which will prevent the data from being plotted.
• 03:16 When it hasn't happened yet.
• 03:18 So let me just roll that back and here's what we're gonna do.
• 03:21 We're gonna select January through December.
• 03:25 And instead of just saying =B4 in January here, what we're going to do is we're
• 03:30 gonna say, equals if B4 is greater than,
• 03:37 the value that we actually have sitting over here for our Forecast To.
• 03:42 Then, what we're gonna do is, we're gonna say, NA().
• 03:48 If it's not greater than, then we're gonna go with B4.
• 03:53 Now, important though, of course, that we make all of this stuff absolute and
• 03:57 relative the way that we need.
• 03:59 B4 is sitting up at the top up here, so
• 04:01 we're checking, is January greater than G35?
• 04:06 This guy, we're going to lock in and
• 04:09 make him absolute, because I want to apply the same formula all the way across.
• 04:13 I'm perfectly happy with B4 becoming C4, D4, E4.
• 04:16 But I definitely don't want the G35 moving around.
• 04:20 Now that I've got the set, of course I selected all of my values first.
• 04:23 I'm writing my formula from the active cell.
• 04:25 I'll hold down Control, press the Enter key, and
• 04:28 it will commit the formula all the way along.
• 04:31 And isn't that a thing of beauty?
• 04:32 Look at this, my chart now shows only data to May.
• 04:36 If I go and say, change this to June, it now gives me June.
• 04:40 If I go and say, show me data all the way to October, boom, look at that.
• 04:44 It's working beautifully.
• 04:46 So, this is an awesome way that you can suppress the headers.
• 04:50 So if there's no data in there, you can return an NA, using an if function.
• 04:54 And the beautiful thing about it too,
• 04:55 and this is something that I don't think is worth glossing over at all.
• 04:59 I really want to see this.
• 05:01 I did not modify my index match functions at all.
• 05:05 All I've modified is this piece.
• 05:07 Why does this work?
• 05:09 Well, because when you look at the formula for my index match, I'm looking for
• 05:14 a match based on a value that's returning NA.
• 05:17 Whenever there's an NA value error, that cascades through the rest of the formula,
• 05:21 setting the rest of the data points to NAs.
• 05:23 Now these look Ugly, they look really ugly.
• 05:27 But at the end of the day the cool thing here, when I go and now click on this guy
• 05:31 here and say ten, at this point any one of those #NAs doesn't chart.
• 05:36 And am I gonna leave a user staring at this?
• 05:38 No way, I'm gonna cut my chart, I'm gonna take it over to my dashboard page,
• 05:43 paste it, and do my little resizing to get this to look exactly right.
• 05:48 And guess what?
• 05:49 My user isn't even gonna be seeing what's going on on background at all,
• 05:53 because instead they get a chart that's looking very nice and
• 05:57 doesn't have those #NA things staring them in the face.

Lesson notes are only available for subscribers.