In this screen-cast, I'm going to talk all about counting, summing and averaging in Excel. It turns out that the sum function and the average functions are two of the top three functions in Excel, but you're going to learn a whole lot more than just the sum and average functions in this screen-cast. I've put in a couple of things into these cells B2 to B6. I've put some text, and a number, a date and currency. There's a function in Excel called the count function. The count function, if I do the count on those four values, is actually only going to count the numeric values in a selection. So there's three there. We could also do the count function not just on a selection, but we could do it on an entire column. So you see up here it puts in B, column B, that denotes it's the entire column and we see that there are three and now somewhere down here, if I add in well, first of all if I add in hi, it does not count that because hi is not numeric, but if I put something else in here then we see that our count goes up. If you want to count anything that is not blank, then we can use the count A function. So the count A function of a selection, or an entire column, or an entire row, or whatever you want to put as the argument here, by the way, you can put non-contiguous blocks of cells here, for example there and then I could either hold down the control key and click tow other blocks, or you can manually type in those commas. And so we see there we have six, so the count A function will count anything that is not blank. Real quick I wanted to show you that you should never in the same column that you are trying to count, assuming you're trying to count the entire column,so here I'm trying to count the entire column B, items that are not blank, you should never put that formula inside because what happens is when you're typing that formula and you're actually making that cell non-blank, so it's known as a circular reference, it is trying to refer to itself. So in general you shouldn't do that and it's not going to work properly. You can always drag that over and now it will work properly. By the way the status bar if, I highlight a block of cells down here in the lower right, that does have the average, the count and the sum. If you've got dates, I just wanted to mention this, if you've got dates, remember dates are stored as serial numbers, that's in one of the screen-cast in week one of the course and that's highly skewing the results here, you see that the average is 14,000 and the sum is something like 43,000. So just a little FYI. If you want to work along with what I'm going to do next, I've got this file Gradebook.xlsx, and I've got some hypothetical students here with their student IDs, we have five homework assignments and the scores. Now I wanted to remind you that there's this quick analysis toolbar. So let's just highlight this block of cells and I go down here, and click on the quick analysis toolbar, and one of the things it has is totals maybe I want to calculate the average of the various homework assignments. I can click average there. Let's also go back, because maybe I wanted to calculate the average of the students, and so that'll add this vertical column over here. If we click in, we see that we have the average function so that quick analysis toolbar will actually have the average formula, but you can manually type this in and I've shown that in at least a couple of screen-casts and I'm just going to format these real quick. I can hold down the control key select those and I'm just going to decrease the precision on there because I only need it to the tenths place. There are a couple of other useful counting functions in Excel. We can use the rows function. The rows function is going to determine the rows of an array, so I could, if I want to determine the total number of students, then I could, for example, just do the rows of that, and that tells me that there's ten. I could also do the columns function, so the columns of that array, that would be five. So that's how we could count the number of students and the number of homework assignments. Similar to the rows, in columns functions, you can put in the row. So the row I can click on any row and that's actually just going to give me the row in the spreadsheet. You can also use the column function. The column, if I click on a cell is going to give me which column, now this is column F, but it doesn't yield the letter it gives you the number of the column. So these you might find useful in some applications. I want to show you another use of the row and column functions. Let's say I have the row number here, one through five, and I have the column number one through five, and I got a block of data, and if I decide to delete a column for example by deleting, you see that the column number skips. It goes one, three, four, five. Let's do Ctrl Z. Same thing, if I delete a row, then my row numbers over here will skip three, but maybe I want it to automatically update so if I delete it will re-number, one, two, three, four. The way we can do that is instead of putting in the numbers here, I can just do the row. So I'm going to put in the row, that without any arguments in the row, that will tell you the current row. Now, I want that to be a one, I'm going to subtract two from each of those and now I can take that formula down and drag it. Similarly instead of one here, I'm going to type in column. So column without any arguments, is going to just tell you the current column of that cell. And so that is the third column. But again, I want that to start at one, so I'm going to normalize all those by subtracting two and then I can drag that over. Now, if I decide to delete an entire column, you see that, the five has been eliminated, but this five that was on the end has been changed to a four. Same thing, if I decide to delete a row here, row three, then the remaining rows have been updated. Earlier, in week one, we created this Gantt chart in an optional example screen-cast. Let's say I wanted to maybe put task number, so I'm going to highlight these and I'm going to insert by shifting the cells to the right, and let me just make this a little bit wider and I'm going to auto-scale these, I'm going to highlight that, I can double click on the borders, and now I wanted to maybe add a column here, which is the order that we're going to do things in. And if I just do one, two, and so on down to 13, center that, and I'm going to make this a little smaller, if I decide, because sometimes I like to change the order, so maybe I cut and I make that a little earlier by inserting the cut cells you see that the order does not automatically update. So what we can do is I can just type a row function in there. There are three rows above it, we're starting on the fourth, so I'm going to normalize down to one by subtracting three, and then I can drag that formula down. And now if I change the order of any tasks, so let's do CTRL X for cut and then I insert cut cells you see that, because I have that formula in there, it automatically re updates the order number to go from one to 13.
