Ricardo sends in a tricky formula question for todays podcast. How can you calculate a moving average of all sales in the last 90 days, particularly when your data does not include one row for every day? In Episode 512, I discuss the really convoluted solution in Excel 2003 and the only slightly convoluted solution in Excel 2007.
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
This blog is the video podcast companion to the book, Learn Excel from MrExcel. Download a new two minute video every workday to learn one of the 277 tips from the book!
Transcript of the video:
Hey, welcome back to the MrExcel netcast. I'm Bill Jelen.
Today we have questions sent in by Ricardo.
If you have a question for the podcast you can either send me an email bill@MrExcel.com or call and leave a voicemail.
Ricardo referred back to episode 498 where we calculated a running average since the beginning of a data set.
He said it will be a lot more interesting is to figure out a moving average where you figure out the average of all the values in the last 90 days and Ricardo said. To make it more difficult you can't assume that you have a value for every day.
So you might have an invoice every third fourth fifth day and you basically need to go back and figure out the average for the last 90 days Well I'm going to show you a solution for this in excel 2003 and then show you an easier solution in excel 2007.
I'm going to go to the next worksheet here my approach is to use two different functions.
First the COUNTIF function. COUNTIF function and the criteria portion of this is going to be something that I'm going to concatenate together.
So let's just start down here at the last row.
I say I basically want to always look from A$2. I want to start at row 2 all the time and then, go down to A of the current row.
So if I'm in row 25, that's A$2:A25 But then my criteria is something I'm going to have to concatenate together.
So I in quotes I put a > sign and then the & that's the shift 7 which is the concatenation character and say basically I want to take the date in A25 and subtract 90 from it. When I use that formula, it will show me how many values since A2 have fallen within the last 90 days.
In the next column I'm going to use a SUMIF function.
Basically I'm looking at the same range from A$2:A25 using the same criteria So concatenating > A25-90 but then I have to specify a sum range. Now the sum range is going to be column B but again it will range from B$2:B25.
and this will conditionally sum only the values from the last 90 days. Now to put all this together, I need to copy the SUMIF and the COUNTIF into a single cell.
A great trick for this is basically to go to the formula bar and copy the characters from the formula bar so watch when I control C and I hit escape and go back to column C where I'm going to build my moving average.
I can now paste and it will copy that formula in for me.
I then will go over to my COUNTIF formula again copy the characters out of the formula bar.
This time making sure not to get the = Use Ctrl+C to copy.
Escape to get out of Edit mode.
Now go back and edit my original formula and say I want to divide the SUMIF by I'll hit control V to paste the COUNTIF formula in and that shows me the moving average for the last 90 days.
I can now copy that formula up to the other cells and everything works perfectly.
Now here's the opportunity in excel 2007.
They added five new functions for us and one of those functions happens to be in the COUNTIF and SUMIF area.
They've given us a function called AVERAGEIF.
So rather than have this big long formula that does a SUMIF and then divides by the COUNTIF, we can just say we want to take an average of A$2:A25 Build the same criteria that we built back in excel 2003.
And then specify the range that should be summed B$2:B25 A little bit simpler to use AVERAGEIF in excel 2007 instead of SUMIF and COUNTIF .
Thanks to Ricardo for sending that question in. If you have a question please feel free to get the question to us, we'll get you on a future podcast.
Thanks for stopping by. I will see you next time for another netcast from MrExcel.
Today we have questions sent in by Ricardo.
If you have a question for the podcast you can either send me an email bill@MrExcel.com or call and leave a voicemail.
Ricardo referred back to episode 498 where we calculated a running average since the beginning of a data set.
He said it will be a lot more interesting is to figure out a moving average where you figure out the average of all the values in the last 90 days and Ricardo said. To make it more difficult you can't assume that you have a value for every day.
So you might have an invoice every third fourth fifth day and you basically need to go back and figure out the average for the last 90 days Well I'm going to show you a solution for this in excel 2003 and then show you an easier solution in excel 2007.
I'm going to go to the next worksheet here my approach is to use two different functions.
First the COUNTIF function. COUNTIF function and the criteria portion of this is going to be something that I'm going to concatenate together.
So let's just start down here at the last row.
I say I basically want to always look from A$2. I want to start at row 2 all the time and then, go down to A of the current row.
So if I'm in row 25, that's A$2:A25 But then my criteria is something I'm going to have to concatenate together.
So I in quotes I put a > sign and then the & that's the shift 7 which is the concatenation character and say basically I want to take the date in A25 and subtract 90 from it. When I use that formula, it will show me how many values since A2 have fallen within the last 90 days.
In the next column I'm going to use a SUMIF function.
Basically I'm looking at the same range from A$2:A25 using the same criteria So concatenating > A25-90 but then I have to specify a sum range. Now the sum range is going to be column B but again it will range from B$2:B25.
and this will conditionally sum only the values from the last 90 days. Now to put all this together, I need to copy the SUMIF and the COUNTIF into a single cell.
A great trick for this is basically to go to the formula bar and copy the characters from the formula bar so watch when I control C and I hit escape and go back to column C where I'm going to build my moving average.
I can now paste and it will copy that formula in for me.
I then will go over to my COUNTIF formula again copy the characters out of the formula bar.
This time making sure not to get the = Use Ctrl+C to copy.
Escape to get out of Edit mode.
Now go back and edit my original formula and say I want to divide the SUMIF by I'll hit control V to paste the COUNTIF formula in and that shows me the moving average for the last 90 days.
I can now copy that formula up to the other cells and everything works perfectly.
Now here's the opportunity in excel 2007.
They added five new functions for us and one of those functions happens to be in the COUNTIF and SUMIF area.
They've given us a function called AVERAGEIF.
So rather than have this big long formula that does a SUMIF and then divides by the COUNTIF, we can just say we want to take an average of A$2:A25 Build the same criteria that we built back in excel 2003.
And then specify the range that should be summed B$2:B25 A little bit simpler to use AVERAGEIF in excel 2007 instead of SUMIF and COUNTIF .
Thanks to Ricardo for sending that question in. If you have a question please feel free to get the question to us, we'll get you on a future podcast.
Thanks for stopping by. I will see you next time for another netcast from MrExcel.
