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 explore how to use Boolean logic functions such as IF, AND and OR.
We have already set up a preliminary spreadsheet that is very straightforward. We have HST defined here as 13%, [and] we also have some customer orders, the taxes (as we would expect the order times the HST amount) and the total simply [adds] up the order plus the tax.
We are going to expand [upon] this [spreadsheet] and make it more complicated, but the first thing we are going to do is introduce the concept of a Boolean function and a Boolean operator.
You have been using Boolean operators your whole life... you probably just have not thought about [them as operators].
I want to keep track of big orders, so I am going to create a new named cell called big that has $100 in it.
I want to keep track of orders that are over $100.
I am going to [add a new column:] Is this a big order? (I forgot to name [the big] cell, so I am going to name it big.
I can enter a new formula that says: "is this total greater than big?". [This first example] is true, and if I fill this down we can see it [has] determined [which orders] are big orders [that] are greater than $100, and [which] ones are not big orders.
This is an example of an operator... in this case [the] "greater than (>)" [operator] that produces a Boolean value.
Greater than (>), less than (<), less than or equal than (<=) equal to (=) are all functions that consume numbers but produce a Boolean result.
That was one example of using [the] greater than (>) operator to determine a Boolean value: true or false.
Let's see another way we can use Boolean operators, and in this case I am going to add a new column (insert a brand new column) and for [this] example, customers [that] are in a different province (or they have special tax status) I am going to [add a cell that corresponds to] "do they pay tax?"
Bob pays tax but Sally does not and Frank does not but Joe does pay tax.
What we want to do is change our tax calculation: what we want to say is: "we are only going to charge tax if that cell [in the Pay Tax? column] is true for that customer".
I am going to modify [the tax] calculation [to] say "IF the contents of D7 are true, then it is going to be C7 times HST. Otherwise, it is going to be zero".
I am going to enter this cell and we are going to go back and explore how it works (I am going to fill the cell down).
Now we see what has happened: the people who were supposed to pay tax are paying tax, [and] those people who are not supposed to pay tax are not being charged tax).
Let's take a look at this IF function and see it's anatomy. Remember, when you start typing a function [in Excel it] shows you below what the actual parameters are going to be... so the first parameter is a logical test... whatever you put here has to evaluate to true or false: in this case I reference another cell that had true or false but we could be putting another more complicated example in here and we will do that in a moment.
The next parameter is [the value] if it is true... so in this case, if it is true they do pay tax... [we enter the expression to] calculate the tax. The last parameter is what [value to produce] if the [conditional] value is false. In this case, I said if they do not pay tax the tax amount is going to be zero.
So that is the anatomy of the IF function: it has three parameters... the first parameter is something that evaluates to a Boolean value (either true or false)... the second parameter is the value [expression] if [the value] is true, and the second parameter is [the expression] if the value is false.
Let's change our example slightly: our boss does not like the way it says true/false in here he wants us to enter in the spreadsheets "y"s or "n"s.
Well, that is really messed up the rest of our spreadsheet, so let's go back and modify our tax calculation. Now the trouble is is the first parameter to the IF function expects a Boolean value and Y is not a Boolean value.
But, I could say "is D7 equal to Y?"... that now is a Boolean value.
If I say "is 7=3?" false... is "102=102?" true.
Equals itself evaluates to a Boolean value.
This is a little confusing, because they have overloaded (or used equals more than once).
For the most part, the equals at the very beginning of the formula is a very special thing... once you are past that, the equals really means truly equals (=)... it is a function (or an operator) that produces true or false.
Now that I have modified this function [expression], we can go back, and everything returns to the way we would expect it to be.
We have seen an example of using an expression in here... note how I use the quotes around "Y"... I did not really draw attention to it at the time, but the quotes means this is "Y" itself... do not try to look for a cell named Y or anything else like that.
Let's go back and look at our big example. I could change this (for example) now our boss does not like TRUEs and FALSEs, so I can modify this and say if F7 is bigger than big, then I will say "yes", otherwise I will say "no".
It is a very subtle difference, but look in this formula I did not put big in quotes... that is because big is the name of a cell I want to reference a cell here I did not want a reference to cell named "y"... I wanted to use the value of "y" (or the text "y") so I put it in quotes.
Similarly over in this [other] cell... to show that I wanted to use "yes" or "no" I put them in quotes, because again... if I did not put them in quotes (if I just for example put y here) it is going to say: "I do not know what [this reference to] y is... it is not a cell I could find anywhere".
[Instead], I put them in quotes and use those text values directly.
We are going to make this example a little bit more complicated.
What we are going to do is, we are going to introduce a new column... let's call it "good customer?". [We want to] give good customers a discount price.
I am going to insert column and say "discounted price?".
Bob is not a good customer, Sally is a good customer, Frank is a good customer and Joe is not a good customer.
Let's add a discount amount (let's just call it discount) and we will say it is a 10% discount (let's name this sell "disc"(out)).
What I am going to do is calculate the new value if there is a discount: I am going to say: "if they are good customers (so if that equals yes) then the discount order is going to be the order amount times (1-discount), otherwise it is going to be the regular order amount".
If I fill the cell down, [I need] to be a little careful: [for the tax calculation,] instead of referencing cell C7 for the order, I want to reference E7 which stores the discount amount (I am going to fix that, and I am also going to calculate the total to [ensure] it uses the discount amount as well).
We have changed the spreadsheet: I have added more features so I am going to have to change a few things to make everything work.
Now we have a more complicated spreadsheet: Bob had an order for $100... he is not a good customer (so that is [his order] amount), he does pay tax (so that is his tax amount)... and that is his total amount.
I am going to complicate it just one more time, and what I am going to do is say: "are they a good customer OR are they a first customer?"
Let's say [for] the very first order you are allowed to have a discount... and Bob is a brand new (first) customer.
What we want to say is: "is the customer a good customer OR a first customer?... then we are going to give them a discount".
I am going to do is modify the formula to say IF they are a good customer OR... so I am going to use the OR function... which takes multiple arguments and OR producers true if any of them are true (if you remember, [this] is a callback to our Boolean functions earlier).
=IF(OR(D7="Y",E7="Y")...
then calculate the discount.
I am going to copy that down, and I really only affected Bob... but now we can see we have a much more complicated spreadsheet we can keep track of customer Bob: he is not a good customer but he is a first customer, so he will get the discount but he does pay tax and calculate the tax and there we have the total. He is still barely a big order.
Those are some examples of using IF and OR in Excel. The AND function works very similar to OR: it takes arguments and only produces true if they are all true.