Let's go back to the Gradebook file. Here we have that error, a warning here. The reason for that is it's detecting that the formula omits adjacent cells. So this average actually is not in incorporating column B. So sometimes it'll give you a warning there. We actually don't want to include the student ID number in our average. We can just go down here and select ignore. You can also highlight that entire block of cells all at once and click on Ignore Error. The problem with this grade sheet is, if there's missing assignments, like Charlie Chocolate has a missing assignment here. They didn't turn that in. What we really want to be doing is we want to assign a zero there and that will affect the average. It'll bring the average down because they shouldn't be getting away with not turning in an assignment. So I'm going to do Control Z. I'm going to put in a column here for missing assignments. There's a function in Excel called COUNTBLANK, and we can highlight the rows. So Arnold Apple is not missing any assignments. I'm going to drag that down. I'm going to ignore this error and so we can look down and see which students have missing assignments. We could also calculate the total missing assignments. I'm just going to align that to the right and we can do COUNTBLANK on the entire range of scores. Now as a teacher, you would want to go back in there and find all the blanks and replace them with zeros. There's easy ways to do that, and I will show you later on in the course in Week 3, filtering data. But for now I want to show you two ways that we can identify blanks. The first one, is you can highlight your selection. You can either go up here to Find and Select Go To, or you can simply press the F5 button. It brings up this Go To box. We can go down here to Special and we can click Blanks and click OK, and it automatically selects all of the blank cells and then you can just go up here and highlight. Manually you could go in and replace all of those yellow highlighted cells with zeros. You notice though, unfortunately, you're not changing the yellow formatting, you're not eliminating it. So let me show you another way we could do this. Instead of doing the Go To Special, I can do conditional formatting. So I'm going to highlight this region Conditional Formatting, I'm going to create a new rule and I'm going to go Format only cells that contain, and I can put in Blanks, and then I can format those with whatever I want. So maybe we want to make those red. Go ahead and click OK. OK again. Now it's identifying the blank cells, but when I replace those with zeros, the highlighting is removed. So this is how you can go through manually as a teacher and eliminate all the zeros. Makes sure that the missing assignments are all zero, because you want those incomplete assignments to be affecting their averages. Then the total missing assignments, you can make sure is zero. There are a couple more functions that I want to show you in Excel. Here I've got the Cookies. xlsx file. We wanted to calculate the sales. We've done this in a previous Screencast. One way to do this is just to multiply those two. There's also a product formula in Excel, so you can take the product of not just two things, but you could multiply if you wanted to, multiple numbers. So there's a product formula and I can drag this down. We calculated the total sales by summing that column. So we can sum the Sales column. That's one way to do it, but another way to calculate total sales, you wouldn't need to do this product formula and you wouldn't need to create this sales column. I'm going to leave that because we're going to use it here in a minute. I'm actually going to move this down a little bit, but you can also use a Sum Product formula. The sum product formula takes two vectors or arrays that have to match in size. So I can do the quantity and then I can do the price separated by a comma and what the SUMPRODUCT formula does is it multiplies each corresponding element of those two vectors or arrays and then it's can add them at the end. So it's going to take 7 times 5 plus 2 times 3.5 plus 3 times 5. So it's known as a sum product. What it's not doing, is it's not summing the quantities and multiplying that by the sum of the prices. So it's doing item by item multiplication and it's summing that. It's also known as the dot product and we get the same thing. The last thing I want to show you is how we can use the small and large functions to calculate the top sales. So the three biggest sales here, and maybe the three smallest individual sales here. There is a LARGE function in Excel, so this is similar to the MAX function. The MAX function only gives you the single maximum, but the LARGE we can put in our array here. I'm going to put in F4 to make that an absolute reference. The LARGE function requires a second argument in that is the largest what? So the first largest. If I put in that second argument as two is going to be the second largest, third largest, and so on. So I can use the LARGE function to calculate the top three sales. We can also use the small function on our array, put an F4 in there so I can drag the formula down, and we're going to do the same thing. We can calculate the lowest, the second lowest, and then the third lowest sales. So hopefully this Screencast gave you a better idea of how to use counting formulas in Excel. Thank you for watching.
