Home // Video Tutorials // 3 - 6 (Intermediate) // 6 // Missing Data in Spreadsheets

Missing Data in Spreadsheets

Creating a check for missing data in an Excel list.

Missing Data in Spreadsheets

In spreadsheets it’s often useful to have the spreadsheet check itself to see if there is any missing information in a number of data sets.

In this example, I have a set of information here & for simplicity it’s just days of the week, & we have a LOOKUP here which sees if this day of the week is a week day. What you’ll see is, here is our categories, you have a Monday, Tuesday, Wednesday, Thursday, & now lookup has found a few errors where it cannot find any information. What we would like the spreadsheet to do is to tell us which items are missing so that we can easily update this data set. The first thing we want do is to see if all these items exist here. Now a useful feature to do this is the COUNTIF function in excel, so if I activate my FUNCTION WIZARD, & find the COUNTIF function, say ok. What the COUNTIF does is it will look at the set & say how many times did I find a number. So at this case in the range, we want it to look at is this range here, because we are going to copy it down, I need to make this absolute, we’ll do that by pushing the F4 key, & the criteria what it must count is sitting over here. So what we are saying is how many times do you find this item, the Monday, in this data set. When I click ok , you’ll see it tells me it finds it one time &n when I pull it down, you’ll see it says Tuesday I find once, Thursday I find once, Friday I don’t find at all & similar with Saturday & Sunday. Now having one’s & zero’s is not that nice, so what we’re going to do is just build a little IF statement, using the FUNCTION WIZARD, & basically all it’s going to say is, if this equals one, if that’s true I’m happy it’s in the list, so I’m going to say “ok” & if it’s false I want it to say check. And when I say ok, you’ll see there it says ok & when I copy it down, you’ll see there’s a couple of items where I’m warned to check. You’ll notice that at the bottom here, we’re getting checks even though there are blanks here. So just in anticipation of this we’re going to set up a check that says, if there’s a blank here don’t bring through anything. So over here I’m going to make use of the IF function, so I use the FUNCTION WIZARD, go to IF, say ok. And what we want to say now is if there’s something here, then say ok. So if that cell doesn’t equal blank as indicated by 2 inverted comma’s, if that is true say ok, if it is false we’ll just put a dash in there, then I say ok, click on ok then I can pull that down. And what you’ll see we’ve got ok’s & then where there are blanks we’ve got nothing. Now we could leave things here because already in this column we are told which are the problem cells. But what would be useful is if we can set up a check as well that says if you’ve told me about the instance before don’t tell me again. So for example over here we were told that there’s a problem with Saturday & if you come down a little bit lower you’ll see that Saturday again is highlighted. The way we can do that is to do another little test that says have you already seen this item before, & again it involves COUNTIF. So if I go to my FUNCTION WIZARD, say COUNTIF, now what we want to do is the range that we’re going to look at, I’m going to highlight this cell & the one above it. Now what we want to do is as we copy down we want it to basically expand this, so in this case I’m going to just make this one absolute, dollar signs, the G20 will increase as it copies down, & the criteria I want to use is this cell itself. Click in it, when I say ok, you’ll see says it finds one in that range. As I copy down, you see as we go down eventually Monday tells us it is found twice, Tuesday twice, Saturday twice. Just to see what it’s looking at, you’ll see it’s going looking at Saturday going up & saying how many times have I seen Saturday before. Again the one’s & two’s are not that useful so I just going to build in a little IF statement, go IF, ok. The logical test in this case is if this equals one, then we’re happy with that, so I’m going to say ok, & if it’s false I’m going to say check. Say ok, copy it down. Now that we have all these checks let’s combine them into one single column & just look at one column. The way we;\’re going to do that is to look & see what conditions are met for each check & decide us whether it should warn us or not. The function to use is the IF function again but now in this case we are going to also make use of the AND function, cause we’ve got a couple of criteria’s that must be met. Over here I’m going to type in AND, & here’s the first thing. So we know here’s the first thing; so we look at the blank and we say is this a proper input or is it a blank in this case so we want that to equal ok, & if that is a valid input then let’s see does it exist & what we’re looking for here is does that equal check, & if there’s a check here then are we sure this is the first time we’ve seen it or is it the second time. So what we want is to click here, & say is this the first instance, which in this case is shown by an ok, so I’m going to put an ok. And if all those conditions are met then I want it to say check. So if it is a valid input we’ve got a problem with it’s existence but it’s the first time we’ve seen it then tell me about it otherwise say ok. Then I click ok, you’ll see I can now copy it down & what I get is I get a few ok’s Then I’m told that the Friday’s missing & we can see Friday’s not here, we can also see that it’s first time we’ve seen it. The next one tells us Saturday, again it’s missing, it’s the first time we seen it. Over here you’ll see there’s Saturday, but because it’s not the first time we’ve seen it & we’ve already got a warning for it & it’s not shown, & then Sunday again is not shown. And by simply looking at this we now know we’re missing Friday, so I’m just going to copy that & put it here, you’ll see that it immediately goes to zero, so we can set that up to be a week day & I now know I need to copy these two items, past & the Sunday, a& this we’ll call week-end, copy it down. And in a very quick way we can identify all the items that are missing & you can see that our error check has told us that there’s no problems.