Andre provides a set of formulas to chart the last rolling six months of data. Episode 1013 shows you how.
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This video is the podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey welcome back to the MrExcel netast, I'm Bill Jelen.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well plus fire up a pivot table.
Lets's see if you can solve this problem.
Welcome back to the MrExcel NetCast. I'm Bill jelen. This is episode 1013.
We're going back to episode 993 to correct a couple of things back there.
In 993 I talked about how to create a dynamic range, how that would automatically extend this chart and as you add more data it extends the chart. Great.
We've got a note from Andre in England and Andre said, Well hey that method that you used is fine, but it's going to keep growing forever.
What if you need to show just the last 6 or 10 points and Andre provided a great set of formulas here.
So we use the same name range that we use in 993 but instead we're subtracting.
So if we want to look for the last 6 points, see he uses -7 here for the last 10 points -11.
So we're subtracting one more than before.
Let's give it a try. Here we'll type "December" and a value of 9.1 and you see that the chart automatically shifted when we go look at that formula.
It is indeed grabbing the last 6 values and then in 994 I showed a faster way to do this using Ctrl T in Excel 2007 Ctrl L in Excel 2003.
Tracy who manages all of our VBA projects wrote to me and said, Hey did you actually test that before you put in the podcast and well you know sure enough I did test it in 2007 and even here in 2003, I have to find this list using ctrl L and you'll see that if I had a brand new region "Canada" the chart does update like I advertised.
Let us put a 100 in there However, unlike Excel 2007 when we add a new column, so here "May" the list does update like I thought it would but the chart does not update.
So in excel 2003 the feature is there, but it is half there.
It's great if you're adding new rows, not so great if you're adding new columns.
Of course if you're going to be adding new columns you could always just copy and paste special transpose.
and then create a list and you see that the feature will work there.
So thanks to Andrey from England.
Sending him an excel master pin.
Thanks to Tracy and South Dakota for catching me on not actually checking my work back in Excel 2003.
And I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
Basically we start out with massive amounts of data.
So how we're gonna analyze as well plus fire up a pivot table.
Lets's see if you can solve this problem.
Welcome back to the MrExcel NetCast. I'm Bill jelen. This is episode 1013.
We're going back to episode 993 to correct a couple of things back there.
In 993 I talked about how to create a dynamic range, how that would automatically extend this chart and as you add more data it extends the chart. Great.
We've got a note from Andre in England and Andre said, Well hey that method that you used is fine, but it's going to keep growing forever.
What if you need to show just the last 6 or 10 points and Andre provided a great set of formulas here.
So we use the same name range that we use in 993 but instead we're subtracting.
So if we want to look for the last 6 points, see he uses -7 here for the last 10 points -11.
So we're subtracting one more than before.
Let's give it a try. Here we'll type "December" and a value of 9.1 and you see that the chart automatically shifted when we go look at that formula.
It is indeed grabbing the last 6 values and then in 994 I showed a faster way to do this using Ctrl T in Excel 2007 Ctrl L in Excel 2003.
Tracy who manages all of our VBA projects wrote to me and said, Hey did you actually test that before you put in the podcast and well you know sure enough I did test it in 2007 and even here in 2003, I have to find this list using ctrl L and you'll see that if I had a brand new region "Canada" the chart does update like I advertised.
Let us put a 100 in there However, unlike Excel 2007 when we add a new column, so here "May" the list does update like I thought it would but the chart does not update.
So in excel 2003 the feature is there, but it is half there.
It's great if you're adding new rows, not so great if you're adding new columns.
Of course if you're going to be adding new columns you could always just copy and paste special transpose.
and then create a list and you see that the feature will work there.
So thanks to Andrey from England.
Sending him an excel master pin.
Thanks to Tracy and South Dakota for catching me on not actually checking my work back in Excel 2003.
And I want to thank you for stopping by. We'll see you next time for another netcast from MrExcel.
