Mod 5
Advanced Excel: Text
In this video we are going to explore how to use text and labels in Excel.
We have cooked up a little example that you might [encounter] in a "real world" situation, where you want to illustrate [display in cells]:
Sub-total
Tax
Total
Your boss looks at the spreadsheet and says: "that was great, but I wanted [it] to look like this":
Sub-total
+ Tax
= Total
You say "no problem". You go back to your Excel spreadsheet and you want to make it look exactly like your boss [indicated].
You enter "+ tax" and now we have entered [encountered] a problem, because when I started typing "+ tax" Excel automatically assumed: "oh, you are using plus... you must be entering a formula... I am going to interpret this as a formula". Now it is going to look for [try and reference] a cell named "tax", but we have not named any cells "tax" so it [displays] an error message: "#name?"... means: "you used a name [that could not be found]".
How do we fix this? A little trick is to use the apostrophe ('). If you start [the contents of a cell] with a single apostrophe ('), it [indicates to] Excel that [the contents of the cell] should be interpreted as text (it should be interpreted as a label [, which is another term for text]).
Similarly we are going to have a problem [with =Total]. If I try to type [into a cell] =Total it does not know what total is (it looks like a formula), so [again,] what I want to do is use a single quote (') and then =Total.
If you are trying to enter something and Excel misinterprets it, forcing it to be text (by using the apostrophe) [may] be very helpful.
Another way that we can use text in Excel is by using quotations ("") directly. You may be familiar with air quotes or quotations in different contexts.
For example, if I showed you this message Billy is really "smart" you would interpret that word smart to mean not smart or not literally smart.
In Excel, [quotes almost have the] opposite [effect].
In Excel we can use quotations to indicate: "this is text, do not try to interpret [the contents of the text as] a formula". I will show you where that is going to come in handy.
If I use the equal sign to [indicate the content of the cell] is going to be a formula, and then I put a [double] quote around [text, it indicates:] "this is still a formula, but everything I put in the quotes [should be] interpreted as text".
For example ="= Total", appears the same [as '=Total].
The difference is [that] this [version] is a formula whereas, the [prior version] was just a text [value].
Where does this really matter? Let's [consider] a new example where that might be useful.
You are familiar with the mathematical operators like + and subtraction and multiplication. There is an operator that you can use working with text: it is called the concatenation operator and it is the ampersand (&).
For example, instead of "= Total", I could say: "your total amount is...", [and then] I use the ampersand, and then I want to reference the cell that [contains] the total amount.
="Your total amount is "&D5
Now we can see that it [nicely] displays at the bottom: "Your total amount is 114".
This might be something useful if you are going to be printing out your Excel spreadsheet to give us an invoice to a customer.
we can even be a bit fancier and [add] something like "please pay in 30 days".
With the concatenation operator, if you look at it [in the formula bar]
="Your total amount is "&D5&" please pay in 30 days"
The ampersand "glues together" two things. It is officially called a concatenation operator.
There is [also] a CONCATENATE function which does a very similar thing but most people just use the concatenation operator.
There are functions that can be used with text and not just numbers. For example, in this [example], the contents of D5 are a number (not text). I can force it to be text [if] I use the TEXT function: it [displays in the pop-up] that it converts a value to text in a certain number format. I am going to [use the TEXT function] on D5 to use the format "$0.00".
TEXT(D5,"$0.00")
This looks a little unusual but now we can see how it formatted the [contents of D5]: Your total amount is now $113.00 please pay in 30 days".
The TEXT function is an example of a function that takes a number and converts it to a text in a specific format. [It] is useful if you want to use it inside of a sentence like this.
There are other interesting functions. For example, the UPPER [function]. I say "what is the uppercase of this?". So maybe if they do not pay in 25 days we yell at them and say "Hey! you are supposed to pay in 30 days" or something like that.
You can yell at someone [with the UPPER function and] you can also convert [text] to lowercase.
You can also do things like: "let's take the left [of some text] and I only want the first 20 characters" [with the LEFT function].
I could [also] use the [RIGHT function], or you can [obtain text from the middle with the MID function]. I could say: "give me the middle of this text, starting with character number 10 and give me 10 characters".
=MID(C9,10,10)
If you count in 10 characters: "l amount I".
The MID, LEFT and RIGHT [functions] can be used sometimes to tease out parts of a text. These are all examples of formulas that use text. [There are additional functions] that we can use.
An interesting one is [the] FIND [function]. It finds the position of a character or another text [sequence] within [another] text [sequence].
Where this might be useful (for example) [is if] you have some email addresses "bob@uwaterloo.ca" and we also have "sally@uwaterloo.ca". What we might [want to] do is extract just the name from that particular piece of text.
What I can do first is find ampersand [the @ symbol] in that text. You will notice that it [produced the result that] the ampersand [@ symbol] is at position 4. [With sally,] the ampersand [@ symbol] is at position 6.
That is working great... I can [now] use [the] LEFT [function]: find the left of cell C13 and then the number of characters I want to keep is the number at [position of] the ampersand [@ symbol] minus 1.
=LEFT(C13,FIND(C13,"@")-1)
This is a little [tricky, so I will] review this before I hit enter: I am saying: "first find the ampersand [@ symbol] in cell C13... subtract 1 from it, and then I want to keep [that many] leftmost characters.
[In this example], we can see that we [extracted the text] "bob" and "sally". We were able to extract part of a piece of text.
Those are some examples of working with strings [text and labels] in Excel
Multiple Worksheets
In this video we are going to explore how you can use multiple worksheets within a single workbook.
We mentioned this previously, and now we are just going to see a few more examples.
If you recall, on the bottom left hand corner of the current worksheet it shows other possible [all of the] worksheets [in this file].
Depending on your version of Excel, you might already have a few worksheets. In this version of Excel they default to only having one worksheet [per file], but we can add more.
[Excel] is going to call them Sheet2 and Sheet3.
For example, I am going to enter [on Sheet1] the number 123 and then on Sheet2 I am going to enter 456.
Both of those are in B2: [123 is in] cell B2 of Sheet1, [and] if I go to Sheet2, [456] is cell B2 of Sheet2 and then [on] Sheet3 what I want to do is I will go over to C2 and I want that to be equal to.. now, how do I click on what cell I want? What I can do is once I start [the formula with] the equal sign ([Excel] knows I am creating a formula) I can jump to another sheet and [reference a cell] to add [and then jump to another sheet].
[When] we look at [cell C2] on Sheet3, we can see
=Sheet1!B2+Sheet2!B2
I have calculated something [with a reference from] Sheet1 and [a reference from] Sheet2.
Let's take a look at the syntax a little closer: what it does is it puts the sheet name and then an exclamation mark (!) or a bang, and then the cell reference.
Similarly for Sheet2 it says Sheet2!B2. That may not look very sophisticated... you might want to have different names for your sheets.
For example [Sheet1 can be renamed to] "sales" and [Sheet2 can be renamed to] "marketing".
Now, when I go to Sheet3, you will notice [in the contents of C2] that it changed the name of the sheets for us.
=Sales!B2+Marketing!B2
On the Sales worksheet, I want cell B2 and on the Marketing worksheet, I want B2.
That is an example of referencing cells from other worksheets and that is about [all there is]... there really is not much more to it.
Note these are all relative cell references. For example, if I go back to Sales and I am enter [in cell B3] 100 and I go back to Marketing and [in cell B3] enter 200... if I go to Sheet3 and I copy [from C2 to C3]... because those were all relative references, it still works: it says "OK, this was calculating B2 from that sheet and B2 from that sheet... [so when I copy it down one cell] it says I want B3 and B3".
You [can also] use absolute references. I [change the contents of C2] to say "OK, I want [to use an] absolute reference: I always want B2 [from Sales]". Now, when I copy [on Sheet3 cell C2 to C3], it is going to be 323, because it used 123 from Sales, but the [reference to Marketing] was relative so it used 200 from that sheet to get 323.
You can combine [references to] other worksheets with absolute [and relative] referencing.
One little trick that I want to point out is that if you rename your sheets to have spaces ([such as] "Sales forecast from 2016"), [we] can do that Excel: will let me do that. Then, if I look at the formula [in C3 of Sheet3] it shows 'Sales forecast from 2016'!B2. To ensure that it interprets [the reference with a space] it uses single quotes: this is a different use of single quotes than before when [we were] working with strings [text labels].
These single quotes are only used to indicate [reference] a worksheet that has spaces in it: these [single] quotes group all this text [reference] together [to indicate that] it is one thing all this particular description.
That is [an overview of] using multiple worksheets within Excel.
Boolean Logic
Boolean logic is named after a dude named George Boole, who lived about a hundred and fifty years ago.
It is important for you pedantic people out there that when you write Boolean you always should use a capital B because it is named after a person. If you have never seen that rule before, whenever you use Newtons or anything like that that is named after a person you should always give respect. Make sure [it is a] capital B for Boolean.
For Boolean logic, our variables are either going to be true or false. [Do not give me any of that] "fifty shades of gray" nonsense: everything is either true or false.
This is similar to a light switch which is either on or off. A light switch is either on or it is off: there is no in-between.
[Consider] a [Boolean] variable named X: [it] has a value of either true or false.
Working with Boolean logic [is not much] different than working with numbers and other [areas] of mathematics, where you have integers and values. With Boolean logic, the [only possible] values are true and false.
Where some people get confused is that sometimes when you are working with numbers you can have Boolean results, based on certain operators.
For example, the equals (=) operator produces a value that is either true or false.
If I say "seven equals five" that is false. The equals operator can work on regular numbers, but the value it produces is a Boolean value: either false or true. Similarly, 7 is greater than 5 [is] true.
We [will use] a little table, where we keep track of all the possible values of X.
X
-
T
F
We are going to see a few examples of how we are going to use this.
The operators that work exclusively on Boolean values are:
NOT
OR
AND
You will see why I have this picture [of] the cosmos in the background in a few seconds.
NOT is usually the easiest one to understand... you learn [it] when you are a very small child: you would [ask] your mom "hey can i haz Cheezburger?" and your mom would say "NO you may not have a cheeseburger".
[No] is one of the first words that many babies learn: negation... [the] concept "No, you cannot have something".
If I have a Boolean variable X, if it is true, [then] NOT(X) is false. Conversely, if X is false, [then] NOT(X) is true.
X NOT(X)
----------
T F
F T
That is how NOT works. We have [presented] it here like a fancy function: the NOT function... you use it in regular everyday language all the time (the word not).
The next [operator] we are going to talk about is AND, which is also one of the first things you learn.
If you say you want to order a cheeseburger at a restaurant and the menu says it comes with fries and salad, you are going to be very excited because when you order your cheeseburger you get fries and your salad.
If you only get fries, you are going to be very disappointed because the menu said fries AND salad. If you only get salad you are going to be [equally] disappointed, so how do we look at [AND] from a Boolean perspective?
[Consider] we have variable X that represents fries, and another variable Y which represents salad.
X Y AND(X,Y)
----------------
T T T
T F F
F T F
F F F
If you get both your fries and your salad, then you got both of them so that AND function on the two Boolean variables X and Y: if X is true and Y is true, then AND(X,Y) is true.
If either one of them are false (if Y is false or X is false or they are both false) then the [result of AND] is false. That is how the AND operator works: both things must be true.
If you say you are going to get fries AND salad, then you expect to get both of them.
OR is a little trickier. If you see on the menu that the cheeseburger comes with fries OR salad, you would expect that when your cheeseburger arrives you will be getting fries, or perhaps you will be getting salad.
Let's see what our table looks like for OR.
X Y OR(X,Y)
----------------
T T T
T F T
F T T
F F F
If I say to you: "you are going to get fries OR salad" and I give you neither, the original statement was not satisfied so that is false.
If you do not get your fries but you do get your salad... that works. If you get your fries but you did not get your salad, that also works.
But, there is a row missing in this table: what if you get your fries and your salad? Is the original statement true?
X Y OR(X,Y)
----------------
T T ???
T F T
F T T
F F F
Let's have a bit of a conversation about that.
If you go to the restaurant and you order your cheeseburger and they ask you "do you want fries or salad?" and you say both, they are probably not going to be very happy with you because they intended for you to just get one of those things: fries or the salad.
Often, when you look at menus you will see the word either. You will see "cheeseburger comes with either fries or salad". That "either" word is very important because that either [is] what we call an exclusive or: you cannot have both.
When many people use OR, this is what they mean: exclusive or... you only get one or the other.
In logic, we have a special term to refer to exclusive ORs and it is called XOR.
X Y XOR(X,Y)
----------------
T T F
T F T
F T T
F F F
For [the] XOR function, you can only have one of the two variables: Either X (note how I had to use the word either there) either X or Y. If both of them [are] true then the XOR is false.
You might be thinking: "that is what I always mean with OR... do I want to go to a play OR a movie? Do I want fries OR a salad?"
We also use OR in a different way, and it may not be obvious to you until it is pointed out. Let's say you want to go to an event and they say: "we need some ID... we will accept your watcard OR your driver's license".
[If] you show up and you have both... well [in that] circumstance you would think it is ridiculous if they turn you away. In this [example] we are talking about an inclusive OR. In this situation, you can have your watcard by itself you can have your driver's license by itself or they will be perfectly happy if you have both of them.
This is an inclusive OR. For an inclusive OR, the top role of the truth table is true if X is true (you have your driver's license) and Y is true (you have your watcard) then OR(X,Y) is also true.
X Y OR(X,Y)
----------------
T T T
T F T
F T T
F F F
In computer science, and in the world of logic, ORs are always inclusive unless you say otherwise. When you are working with a computer (or you are working with Microsoft Excel).
Microsoft Excel always has an inclusive OR [function]. If you want to use an exclusive or then there is also a function XOR.
Let's go back to this picture of the galaxy with just AND, OR, NOT you can express very complex ideas and concepts... in fact, every language that [was] ever developed by a human (that we are aware) of has some form of these terms... ancient languages, modern languages, remote languages... they all have some concept of negation, some way of two specifying OR (whether it is exclusive OR or inclusive OR) and there is some mechanism for AND.
It must be that some fundamental part of our brain is hardwired to understand these concepts. If you really want to get philosophical, computers really only understand OR, NOT, AND: these three basic concepts are used to build giant computers.
As an example here is a way of saying exclusive OR using just OR, NOT, AND:
OR(AND(X,NOT(Y)),AND(NOT(X),Y))
If the menu says you can have fries but not salad or salad but not fries... then that is much clearer and this is the way you would probably express this to a computer. It looks complicated, but this is the way that an exclusive OR is written.
Fundamentals of the universe: OR, NOT, AND.
The next time you go to a restaurant and they say fries OR salad and they do not use that key word "either", then I would encourage you to give them a lecture on the difference between inclusive OR and exclusive OR and why you deserve both fries and salad with your cheeseburger.
Boolean Functions
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 really messed up the rest of our spreadsheet, so let's go back and modify our tax calculation. Now the trouble 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 a 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.
Errors and Conditional Formatting
In this video we are going to explore a couple [of] special Boolean functions that Excel has and look at conditional formatting.
We have already [generated] an example of some data: some students who have [had] five assignments.
We want a summary table with the students' names and what their average assignment grade is.
Currently it has the first name, the middle name, and the last name [in] three separate columns. I want to just have student names and I want to combine them all together.
We have already learned the concatenation operator (the ampersand) so what I could do is: I want [the cell with] Sally then what I am going to do is concatenate in [a] space and then [the cell with] Anne, concatenate in the space and then [the cell with] Johnson.
=B3&" "&C3&" "&D3
That looks pretty nice: I am going to fill that down... [but] something looks a little bit off: Bob Smith now has two spaces in his name... If I look at the formula, it is taking B4 and then it is inserting a space and it is taking C4 and then inserting a space and then D4 but Bob's parents chose not to give [him a] middle name... it is not Bob's fault he does not have a middle name.
[There are] lots of different ways we can fix this problem, but I am going to show you a very particular one using the IF function that we have seen before.
I am going to [add something] in the middle [of the expression]. I am going to use a very special function called ISBLANK. If C3 is blank, then I am going to do nothing... otherwise I am going to use C3 and an extra space.
=B3&" "&IF(ISBLANK(C3),,C3&" ")&D3
[Let's] copy this down make sure it works... [and it] looks pretty good. Bob was fixed and Peter Zimmer looks much better... So now we will take a look at this formula, which is a little complicated... but not too bad.
First I take B3 and then I add in the space. Then I specifically check to see if the middle name is blank and if the middle name is blank, I put in nothing. Notice I put in nothing. If it is not blank, I want the middle name and another space.
There are lots of different ways I could have done this logic I could for example check to see what is the length of the middle name is: [is] that greater than zero? But I want to highlight this special formula called ISBLANK.
There are a few of these special Excel functions that can be quite handy if you know how to use them. We are going to see another one shortly, but this is the first one we are going to see.
Now [that] we have got the names, I want to calculate their average.
What I am going to do is take the average of their five assignments. Sally was sick for one (but that is okay she was excused for that).
We are going to copy that down... uh-oh... we now have a problem.
Alex did not show up for any of his exams [complete any of his assignments] so we have what happens is the average function is trying to take all the numbers that occur here and then divide by the number of things there... which is zero.
When you divide by zero you get what is called a "singularity"... now if you are used to science fiction movies a singularity is often a black hole that will suck in everything around it or if you watch The Terminator movies the singularity point is the point at which the artificial intelligence becomes smart enough to kill all the humans.
Fortunately, Excel is not doing any of that but what it is doing is displaying an error message and that does not make our spreadsheet look very nice.
We are going to change this formula.
There is a special function in Excel: ISERROR. If the average is an error, then I am going to say "not available". If it is not an error then I am just going to calculate the average again.
Now it looks a little bit nicer... we see the average for Alex is not available.
Of course, we can put whatever we want in there [to indicate not available] and most instructors would probably give zero.
We [can] use the special Boolean function ISERROR to determine if something is an error.
Let's take a look at this function: it is not ideal because we have had to enter the average function twice... we said first: "calculate the average... if it is an error then use the text not available otherwise let's recalculate the average again".
That is not very efficient, so Excel has a very special function (I am just going to start typing it from scratch) and it is called IFERROR.
Let's take a look at what IFERROR does: it calculates a value (in this case average of these cells) if that is an error then I [can] specify what [expression to use] it if there is an error.
I do not have to recalculate the average twice: I will just put in zero and say "if it is an error, then produce zero... otherwise produce the value you calculated". In this case, Alex now has zero.
We have introduced two functions: ISBLANK and ISERROR: both of [them] can be used to make your spreadsheets more robust and handle errors a little bit more gracefully.
The last feature we are going to show is something fancy in Excel called conditional formatting.
We want to see at a quick glance, students that are failing and have an average below 50.
I am going to highlight the averages and then go up to "conditional formatting" [on the ribbon]. You [can] see a bunch of different things... there are a lot of sophisticated things you can do, and if you want to become an expert [at] Excel you can play with some of these things: they really do make some of your Excel spreadsheets pop and show some interesting things.
I am just going to do a very simple rule here: if those cells are less than (so I want to do conditional formatting, highlighting cells rules)... if they are less than 50, then I am going to fill them in with light red with dark red text.
We have now highlighted certain cells that have not met a certain criteria. If all of a sudden Alex comes through and "Wow! He scored 100"... he is no longer going to fail this course because he got a hundred on that assignment (I just undid that)
As I mentioned, there [are] a lot of advanced conditional formatting things: you can use color scales to show a wide range of values you can even use icons like checkmarks or X's to indicate special values in your spreadsheet: they are just there to help you interpret the data
COUNTIF and AVERAGEIF
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 equality... 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 are a bunch of different functions 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 were COUNTIF, COUNTIFS, AVERAGEIF and there [are] other functions out there such as SUMIF.
VLOOKUP
In this video we are going to learn how to use the VLOOKUP function in Excel.
The VLOOKUP function is very useful for looking up data in a table.
The example we are going to use right now is: we have some provinces and what the sales tax rate is in that province (which includes federal tax for all you tax nerds).
The first thing I am going to do is name this table I am just going to call it "taxtable" to make my life a little easier.
Let's do a concrete example of how to use VLOOKUP: I am going to start typing a formula [and] the function VLOOKUP.
The first thing I want to know is what do I want to look up, so in this case I want to look up the Ontario tax rate.
[Next,] I specify where the table is. Fortunately, I have given it a nice name taxtable, and now I [need to specify] which column [I] want from this table.
It looks in the first column for a match... it is going to find Ontario in that table because Ontario exists... but then [it needs] to know which column [we] want to use. In this [example] I want the second column.
I give a value of two [for the] last [third] parameter.
You will notice [there] is a Boolean value [for the fourth parameter]: it either wants true for approximate match or false for exact match. I am going to specify false and we will explore this option later.
=VLOOKUP("ON",taxtable,2,FALSE)
That is everything we need to do: VLOOKUP is going to look that value up in the table and see that this Ontario sales tax rate is 13%.
If I specify a different value I could lookup Quebec (which has a very awkward sales tax rate and it says it is 15% -- it is not showing all the decimal places).
What if I try to look up my friend in Washington DC [who] wants to know what our sales tax is... it is going to give an error message that says not available: it could not find DC in that column.
We could use our fancy IFERROR function if we can't find the sales tax rate we will just say "not in table". this will be a little friendlier version of what it did before.
Let's [do] one more [example and] look up the sales tax rate in BC... we see that it is 12%.
Let's use this in a practical application. We are going to [revisit] our sales example where we have some random people from Canada and their orders.
We want to calculate how much tax is going to be on their order. I am going to use VLOOKUP: I am going to say: "look up that province in my tax table": I want column two and false for an exact match.
The tax is not quite what we want... we want to calculate the tax, so we are going to take that value and then multiply it by the order amount.
We can see how much tax is going to be on each of these orders. We have a column that generates the appropriate level of tax, based on the different provinces.
This is a very practical example of where you could use VLOOKUP in your spreadsheets.
Let's do one more example. [Consider that] we have a shipping amount as well. I have [modified] this table: it is not just for tax rates... I am also going to include how much it costs to ship to each province.
Let's say our company is based out of Ontario, so the shipping rate to Ontario is $5 but the rates to some of the other provinces are going to be higher so shipping to Alberta is $10, British Columbia is $12, Manitoba is going to be $8, Northwest Territories is going to be expensive, Quebec is going to be same as Manitoba and Saskatchewan the same as well.
Now we have extra shipping rates added to our taxtable. I am going to change the table (even though I call [it] taxtable)... what I am going to do is modify it so it now includes this extra column.
Now we can look up the shipping rate: I am going to do something very similar to what we did before I am going to VLOOKUP look up the province in my tax table and now I want column 3, so we now want the third column value, so that is going to be column 3. Again, I am going to use exact match here.
=VLOOKUP(C4,taxtable,3,false)
We can see this shipping rate (format that as a dollar amount) and then just copy that down to all of our customers.
Now we can see the different shipping rates based on the province that they are located in as well.
Just one more example... we are using a different column than we were using before, and in fact if we look at the formula, the only difference between this calculation is using column 3. [The previous example used] column 2.
For our final example, let's tackle what that fourth parameter is about: exact or inexact lookup.
Let's create a new sheet and what we want to do is we are going to have a discount rate based on a certain order size. If your order size is between zero and $500 you do not get a discount.
Let's [enter] this: discount is going to be zero percent. If [your order] is above $500 then you are going to get a discount of 2%, if your order is over $750 then your discount is going to be 3%, and for orders over $1000 then you are going to get a discount of 5%.
We have different discount amounts based on how big your order is.
What VLOOKUP does is it looks in the table and it finds the smallest [largest] value that still matches [is less than or equal to] what you are looking for.
Let's do some concrete examples. I am going to call this table discounts.
I VLOOKUP the value $10 in my discounts table, and I am looking [at] the second column and then I [specify] true [for] approximate match.
=VLOOKUP(10,discounts,2,true)
If my order is $10, my discount is zero.
What if my order is exactly $499? My discount is still zero, but as soon as my order is $500, I am getting a 2% discount.
If I bump the order up to $750, it says my discount is now 3%.
I am going to enter the value $888... [which] does not appear in this table.
What Excel does is it finds the row with the smallest [largest] value that corresponds to the value smaller than [less than or equal to] what you were looking [for].
750 is the smallest [largest] value in the table that is still less than or equal to $888, so it determines that your discount rate is 3% [... the value for $75].
This function was developed for doing this kind of a lookup... for thresholding different ranges and assigning different values to those ranges.
We can [add] this [to] our main spreadsheet.
Let's calculate our discount, and what I am going to do is VLOOKUP the order amount in my discount table, and I want the second column (which is going to be the actual range in this case). But, I am going to [specify] true [for] an approximate match.
Then my discount is going to be that amount [percentage] times the order itself (let's view that as a dollar) and we will calculate all the discounts that people are receiving.
We can calculate the final total: their order plus the tax plus shipping minus the discount they get and now we have [the] full calculation to determine [what] the final total amount is going to be for each customer.
[This has been] a practical example of how we could use VLOOKUP in a table.
There is also an HLOOKUP function that is very similar except it looks up across rows and then finds the corresponding column to match. With VLOOKUP, we look in the first column find the row we are interested in and then look up the appropriate column.
I just want to clarify one more time how the last parameter of VLOOKUP works: if the value is true then uses an inexact match... and I did not mention this earlier, but this is very important [when] using inexact match: the table must be sorted by the first column so the entries in the first column must be sorted from smallest to largest. They do not have to be numbers: they can be names and strings [or text] or dates, or something like that... but they must be sorted from smallest to largest.
If the fourth parameter is false, then it uses exact match and the table does not have to be sorted.
That is one last clarification on how to use VLOOKUP in Excel.