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 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 name 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 is a very special function that says 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 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 is 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 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 do is highlight the averages and then go up to "conditional formatting" [on the ribbon]. You [can] see a bunch of different things... there is 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 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