Instructor: Alrighty, so let's wrap up this section
with a demo that kind of ties together
all these different stats functions
we've been talking about.
We're gonna put into work count, count if, sum product,
and apply those to an actual real world analytics
and BI use case, something called data profiling.
Now, when I talk about data profiling,
what I mean is generating an understanding of the quality
or distribution or characteristics of a data set.
Okay, so go ahead and open up your section four workbook.
You should see a new tab
at the very end called data profiling.
If you don't see that tab, just jump all the way back
to the download course resources lecture.
In the intro, download a fresh copy of this workbook,
and you should see that tab appear.
Now, what we're looking
at here is a very simple small sample of customers.
We've got 10 different customer names here,
along with their city, state, and zip code,
and for this demo,
the zip code field is the one we really care about.
That's the column that we're gonna profile here.
And what you'll notice is if you select F3 through F12,
I've named that range zips,
and that's just gonna make our formula
a little bit more intuitive
and a little bit more human readable.
I've also built this little template here
with some of the data profiling metrics
that we're gonna use,
along with the world's tiniest bar charts
just to help us visualize how many
of our records fall into each of these categories.
Now, keep in mind there are many more data profiling metrics
that we could use here.
We're gonna focus on some of the most common ones
like the row count, distinct records,
blanks, numbers, and valid records.
So we've already started by populating the total row count,
and for that, we're using the rows function,
which we'll talk a little bit more in depth
about in the lookup reference section of the course.
But basically, all you need to know is that
rows will count every single row in that reference
regardless of whether it's blank, numbers, text, and so on.
So press enter, we've got 10 records in our sample.
Now, I'm gonna skip past distinct for now,
because it's gonna be our most challenging case
for this demo, and let's just bang out the blanks,
numerical values, and valid record section,
because those are a little bit simpler
and quite a bit more straightforward.
So to count the number of blanks in that zip code range,
lucky for us, Excel has a very helpful formula
called count blank, and all we need to do is feed it
our zips range, press enter, boom, we get three,
'cause there are blanks in rows four, nine, and eleven.
Next up, we've got numerical values,
and the reason we're counting this and treating it
like a bad thing is because remember,
zip codes are not really treated like numbers.
We're never gonna be aggregating them or adding
or subtracting or multiplying zip codes together,
so in this case, if someone enters a zip code
as a number, that's a bad thing.
So to count the number of numerical values in a range,
we're gonna use the simplest function of all, just a count.
Remember, counts the number of cells
in a range that contain numbers, exactly what we want.
Let's feed it that zips range, press enter,
and we get a one, because this one zip code all the way
at the end, looks like Ivan entered it as a number, right?
And all of our other zip codes here are treated like text.
In fact, we actually used the special formatting category
for zip codes specifically, same thing.
So last but not least,
in this little section, we wanna calculate valid records.
And if we're defining valid as non-blank, non-numerical,
so we want a text field that contains something, anything,
we'll call that a valid record.
There are two different ways we could do it.
In fact, there are many more than two, but two common ones.
For one, if we know the total row count
and we know the blanks and numericals,
we can simply subtract three and one, and arrive
at the valid records of six, which is correct and accurate.
Instead, I'm gonna show you a really helpful little shortcut
which is using a wild card in a count if function.
So let's use count if, our range is our zips range,
and check this out.
Our criteria, remember, we're looking
for text that contains anything, non-blank text.
What I can do is open a quotation mark
and use the asterisk wild card.
That's the wild card that basically can account
for any number of characters no matter what they are.
Close the parenthesis, or the quotation mark
then the parenthesis, press enter, there we go.
There are six valid records, one, two, three,
four, five, and six.
We don't count this value in row 12,
and that gives us the exact number that we need.
Now it's time to move on to the trickiest one of all,
which is the distinct records value.
This one's funny, because it sounds
like it's something that should be quite easy to do.
In fact, your instinct might be
to start typing something like this, count distinct zips.
problem is you get the name error,
because Excel doesn't have a function called count distinct.
I wish it did, but unfortunately, it does not.
Now, if you're using Office 365 and you have access to some
of the newer dynamic array functions, there's some formulas
like unique that will make tasks like this much easier,
but for now with the tools that we have, unfortunately,
counting distincts is a pretty tricky exercise,
but we can make it work.
So let's go ahead and delete that.
What we're gonna do is take a similar approach
to the one we took counting duplicates
in our Ben and Jerry example.
Remember we combined sum product and count if,
and we iterated through those lists,
and counted the number of instances of each value.
Well, we're gonna start with the same approach,
and then make a few little modifications to suit our needs,
so follow along closely here.
We're gonna build this up step by step.
I'm gonna start with sum product, and the array
of values that we wanna sum up is gonna be generated
by account if, just like we did in our duplicate example.
And what we're doing here is we want to count the zip codes
in our zips range, and the criteria
that we want to evaluate is basically iterate
through zip by zip, and tell me how many times
that zip appears in the criteria range.
And because both arguments are referring
to the same range here,
we just need to reference zips one more time,
close two parentheses, and let's start here
and press enter, and see what we get.
So shows nine distinct records,
and even just at first glance, we know that's incorrect,
'cause there are fewer than nine distinct records here.
We've got three blanks, and it looks
like this zip code 07040 appears twice here
in rows five and seven,
so nine is clearly over counting the number
of distinct records.
So if you remember,
sum product works like an array function.
It iterates through each row in a range, and generates
an array that you can then perform operations against.
And the best way to troubleshoot and diagnose these types
of formulas is using the evaluate formula tool.
So if you're on a PC, go ahead and click evaluate formula
from the formulas tab, and let's see what's going on here.
So sum product count if zips zips,
we're gonna start by evaluating that range zips,
which just refers to F3 through F12.
Those are the values we're gonna iterate through,
and what are we gonna check for?
Well, criteria is our zips range,
and when we break that out, we'll see each
of the 10 values in that zips range broken
into an array inside of the formula.
Now, there's one very important thing to note here, which is
that any of these blank rows have been converted to zeros,
not text, not blanks, but numerical values equal to zero.
And that might seem like, you know, a trivial thing,
but it's a very important, very nuanced thing
that's going to come back and become a very important factor
as we continue with this formula.
So put that in the back of your mind, and we'll keep an eye
on how those zeros are gonna affect things.
So at this point, if we evaluate this entire count if,
remember we're iterating through zip by zip,
and we're returning the count of those zips
in the entire array that we're looking at here.
So we evaluate, and we see 10 numbers,
one, zero, two, one, two, one, zero, one, zero, one, okay?
Zip code 62031 in the first position of the array returns
a one because it appears one time, right?
07040, which appears in the third position
of the array, returns a two
because it appears twice in that range.
Here's where it gets a little weird.
You might expect a three in positions two,
seven, and nine, because there are three blanks,
but remember, we're not looking at blanks.
We're counting the times where that blank row is equal
to the number zero, which never happens,
and that's why we get zeros in those positions.
So at the end of the day, when we take the sum product,
which in this case, is really just adding these numbers up
'cause we're not multiplying by another array,
then we'll just add those numbers
and get the nine that we see right there in cell I4.
So let's close this out, and think about how we need
to modify this to suit our needs.
Well, first thing that you'll notice is
that any case that appeared or any zip code
that appeared two times gets double counted, right?
Because in our resulting array, we showed a two
for this zip code, and a two for the duplicate version,
which is completely inflating our number
of distinct records when we add those numbers up.
In fact, if there are two instances of a zip code,
what we want is for each of these zip codes to take a value
of one half in our resulting array
so that when you add both together, you get a one,
because the two of them represent one distinct value.
In fact, if there were three instances of the same zip code,
we'd want each to take a value of one third so that
all three when added up represent one distinct record.
So in order to make that work,
what we're gonna do is click right before the count if,
we're gonna add a numerator,
one divided by that count if array,
and let's just start there and press enter.
You'll see we got a div zero error,
which means we're not quite done yet, but to diagnose,
we're gonna go back to our trusty evaluate formula tool
and see what's going on.
So again, our zips range, F3 through F12,
our zips array, we're gonna perform our count if,
get that same array of one, zero, two, one, two, and so on,
and now we're dividing one by each value
in those, or in that array.
That's gonna turn the twos for zip code 07040
into one halves, which is exactly what we want.
The problem is that the zeros are gonna throw our errors,
and then when we try to take the sum product
and add those up, we're gonna get a div zero,
which you see in I4.
So we're getting closer, but we still need to deal
with those zeros which are produced by the blank rows,
so here's a little trick here.
Instead of looking at the zip code values as is,
as our criteria array, all I'm gonna do is use an ampersand
and concatenate a blank empty space there,
and what that's gonna do is tell Excel,
"Hey, don't just give me the list of values,
those 10 zip codes in the array.
I want you to append a blank to the end,"
and that's gonna turn it into text.
It's not gonna change the number
or add a character or anything, it's just telling Excel,
"Treat the values in that array as text."
So press enter, and hey, look at that.
Now we've got seven, we're getting closer, right?
And if we evaluate formula, F3 through F12,
we evaluate our zips just like we did before,
and now when we add or concatenate
that little blank string at the end,
watch what happens when we evaluate that.
Now instead of zeros in those positions of the blank rows,
now we get a blank text string,
which is exactly what these cells represent.
F4 is not a zero, it's a blank text string, right?
Same with F9, F 11, and so now when we run this count,
here's where we see three instances of the blanks, okay?
Instead of zeros, and when we take the one,
divide it by each value in that array,
we get one, one third, one, one half, and so on,
so that now when we add them up,
three blanks become one distinct value.
The two zip codes that were duplicated become
one distinct value.
Add it all up, and we get a seven.
So we're almost there, right?
And if you want to treat, you know, a blank zip code
as a distinct value, then you're done, this is accurate,
but if there never should be a blank value there,
and you don't want to treat the blank as a distinct record,
there's one other tip I can show you
to help you account for that, and basically,
what we want to do here is multiply the resulting range
that we got, the array that we produced, by another array
that will essentially allow us to filter out
or ignore any rows here in our range that are blank.
And so to do that, I'm gonna replace the one
that we had written with a logical statement here.
I'm gonna open up a new parenthesis,
reference my zips array,
and I'm gonna say zips not equal to blank,
close the parenthesis, and then press enter.
There we go, now we're not counting the blanks,
and we get the proper distinct record value of six.
Okay, so what's happening here?
Why did we put that criteria or that argument there
in place of the one that we had before?
Well, you know the drill.
Let's evaluate our formula.
This time, we're gonna start at that step,
and watch what happens.
We evaluate that zip range,
we apply the logical operation, iterating through,
checking which ones are not equal to blank.
That gives us a range of trues and falses,
and remember, because when Excel multiplies arrays together,
trues are gonna count as one,
falses are gonna count as zero.
So when we continue with this process
just like we did before, we're gonna end up
with something like this, which is that array of values,
the one, three, two, one, two, one, three
like we had before,
we're gonna multiply that by this new array
that we just created with that logical statement,
and any case where we have a false,
those records are gonna be just excluded right out,
because we're multiplying by a zero,
and this is the resulting array that we get,
which sums up to six.
So definitely a little bit tricky here,
not really an intuitive case,
but that's gonna give us the exact count
of distinct records that we need.
So hopefully, that was a good taste of how we can use
some of these tools like count if, like count blank,
sum product to really answer some common
and useful analytics cases,
in this case, like data profiling.
Hope that helps.