In this video, learn how to calculate running averages that only look at data from a certain number of months in your collection.
- [Instructor] Collecting data over time lets you perform many kinds of analysis. One useful way to analyze your data is to find the average. For example, you might have a count of the number of customers who've come to your store or website over the course of a year, and by calculating the average, you can find the number of customers who typically come to your store or site during a month. What I'll do in this movie, is show you how to calculate running averages that only look at data from a certain number of months in your collection. My sample file is the running average workbook, and you can find it in the chapter two folder of your exercise files collection.
You can see here that I have a number of customers broken down by the months in a year. What I'd like to do, rather than find the average across all months, is to find a running average. That gives me a better idea of how things change throughout the year. So for a running average, in cell C2, which I already have selected, I will type an equals sign, and then I want to start out with the range B2 to B2. So yes, I'm going to be taking the average of one cell, which if I do it correctly, should be that cell's value.
However, I also want to be able to copy the formula, so I'll need to play around with cell references. So in cell C2, after the equals sign, I'll type average, and then B2, which is my starting cell. I want that to stay the same across all the formulas when I copy it, so I'll press F4 to make it an absolute reference. I'll type a colon, and the other end of the range will be B2, so I'll type B2 again, but because I want it to change and update as we include more months, I'll leave that reference relative.
Type a right parentheses to close out the formula, enter, and I get the average I expected. Now what I can do, is to copy that formula down to the remaining cells that are next to my original data list, from A1 to B13. Rather than copy it down by hand, which is prone to error, and also just annoying to have to do, you can select the cell that contains the formula, and then double click the fill handle, that's at the bottom right corner, and looks like a green square.
So I'll move my mouse pointer over the fill handle, and when it changes to a black crosshair, I know I'm in the right place, double click, and there we have it. So you can see that I have a running average of 5,337 in cell C3, that's for the first three months, it goes up to 6,798, and the running average settles as it goes down, but you can also see that it drops in August and September, and also October, before going back up in November and December.
So that's interesting, but there's not a lot of difference between the running averages, and in fact, a large value in March can overwhelm smaller values elsewhere. A way to get around that, is to have a three month running average. So you need three months worth of data. I'll click in cell D4, which corresponds to the month of March, and I will take a three month running average for January through March. And then in cell D4, I'll type an equals sign, followed by average, and then after the left parentheses, I want to find the average of customers, so that's B2 through B4, so B2 colon B4.
And because I'm taking a three month running average, I want both the start and the end of the range to change as I copy the formula down. So I'll type a right parentheses, and enter, and I get 6,798, which corresponds to the running average for March before. That also takes January through March, and so does my three month running average. Now once again, I can copy the running average formula down, so I will click cell D4, double click the fill handle, and it goes down, and you can see that the three month running average changes a lot more drastically based on the day that that is either included or excluded.
And if I click cell D13, the last one, and I look up, I see that in fact it does look at B11 through B13. One thing to note before I go, is that these error indicators at the top left corner of a lot of the formula cells, those just indicate that the formula omits adjacent cells. In this case it's okay, you can ignore the error, which will get rid of the indicator for that one cell, or you can turn it off entirely in Excel options.
- Distinguish between the mean, median, and mode.
- Describe the relationship between variance and standard deviation.
- Identify a nondirectional hypothesis.
- Point out the difference between COVARIANCE.P and COVARIANCE.S.
- Explain correlation.
- Analyze Bayes’ rule.