CS 100 (Learn) — CS 100 (Web) — Module 05
NOTE: If your internet access is restricted and you do not have access to YouTube, we have provided alternate video links.
Note: This video transcript has been slightly modified. Corrections are marked with strikethrough, and alternative wording has been placed in [square brackets] to correct some of the awkward or confusing phrasing in the videos.
In this video, we are going to learn how to use some statistical functions that also use Boolean logic.
We have [entered] a bunch of names and addresses and the amount of their order from our fictitious company.
Your boss comes to you and she wants to know: "how many of those orders are over $1,000?".
[Your reaction is:]: "How am I going to calculate that?"
I am going to use the fancy function called COUNTIF because that does exactly what she wanted us to do: count how many cells are above $1,000. If they are above $1,000, we want to count them.
I am going to [enter in the cell] =COUNTIF, then I am going to enter the range (again if you look at [the help that appeared when we] started typing COUNTIF, it showed us that it is looking for range [for] the first parameter.
The next thing is going to be [the] criteria, and I am going to say [in quotes] ">1000". This is a little unusual: we do not just want to put >1000 because then it is going to try and interpret it like a formula.
=COUNTIF(E4:E103,">1000")
The way COUNTIF works is: if you want to specify a logical condition such as this [(greater than 1000)], you put it inside quotations.
Now we [can calculate that] we have 51 orders that are greater than a thousand dollars.
Now your boss wants to know: "how many of the orders come from Ontario?" We could do a similar [approach]... we could say COUNTIF and then this time I am going to use the range to be the provinces, and then I can see my criteria is going to be Ontario.
=COUNTIF(C4:C103,"ON")
If I do not specify a greater than or less than, it assumes equalty... so now I am asking how many in this column are equal to Ontario, [and] we can see that 37 of the orders come from Ontario.
Now your boss wants to know how many from [each] province [there] are, so let's regroup and start to do a bit more of a sophisticated approach (let's just delete what we have so far).
We are going to list [the] provinces... Alberta BC [etc.]. These are the provinces [of] the customers we have in our database ([and] I [added] a header).
[First, we will] count how many were in Alberta. I enter a formula using the function COUNTIF and for the range I am going to use an absolute reference so I can copy it down. I am going from $C$4:$C$103.
[For the criteria, instead of] specifying [within] quotes as I did before, I am going to specify "how many in that range are equal to what the contents of G4 (is which is Alberta)". Now I can just simply fill this down... this tells me how many in that range are equal to Saskatchewan.
Now the boss also wants to know what is the average order from [each] province [is].
Now I am going to do a function called AVERAGEIF, so now when I specify AVERAGEIF I am going to specify the province, and then my criteria is still going to be that it is equal to Alberta... but now I am going to say: "do not take the average of the provinces... that does not make sense... take the average of this range". Now we can see that the average order from Alberta is just over a thousand dollars and it is probably going to be very similar for all the other ones as well (let's format the dollars).
Now we can see what is the average order of those from Alberta and those from Ontario, and we have a nice little summary table of our data.
Now your boss wants something a little bit more sophisticated, and your boss wants to know how many orders are greater than a thousand in each province.
To do this one we are going to use the final function we are going to introduce. So far, we have introduced COUNTIF and AVERAGEIF... there [are] other functions [with] similar names.
If you want to see some of these functions, they are under formulas... and then statistical functions: you can see that there is a bunch of different functions that that you might find interesting. For example [COUNTBLANK] counts how many blanks are in a spreadsheet [range].
The one we are going to use is called COUNTIFS.
In this [example] we want to know how many orders from each province are greater than a thousand dollars. [Similar to] COUNTIF, except there [are] multiple criteria so it [is] plural (ifS).
It lets us specify multiple criteria: I am going to specify that [the first] range is going to be equal to (I should make that absolute reference)... that range is going to be Alberta, and then I want to make sure that the order is going to be greater than $1,000 so I specified multiple criteria and now [we can see] the count for each province.
For example, 21 of the 37 orders from Ontario are greater than $1,000.
This [was] an example of using the advanced statistical functions that use Boolean criteria. Functions we looked at where COUNTIF, COUNTIFS, AVERAGEIF and there [are] other functions out there such as SUMIF.