Glenn has a series of line charts showing monthly data for 2009. Most charts work fine, but the total chart shows the line dropping to zero for future months. Episode 1004 shows how to solve this problem, then how to solve the problem from the solution to the original problem!
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 netcast.
I'm Bill Jelen.
Basically, we start out with massive amounts of data How we're gonna analyze this. Well, plus fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Great question today sent in by Glenn.
Glenn has three components A, B and C, and then a Total over here.
He wants to create charts from these and the chart is working great.
On the component items A, B or C.
So, you can nice little chart here shows the year day revenue life is good, right?
But then when he tries to create the same chart from the total.
It has this...
the drop to zero so we create the chart all of a sudden for the future months baam we are to 0.
It's like the company went out of business. Well, it's not the company went out of business.
It's just that we don't have any future data, but take a look at how good it looks here, and how bad it looks here.
Glenn says I have a lot of charts. I don't want to update these every month.
I don't want to edit the series formula, or do anything like that, and he admitted writing for he says well look I'm using this formula over here in the total to make sure that we don't see anything if it's not filled in.
So, if the sum of these three cells is zero, then put quote, quote in other words nothing and then otherwise put the sum.
Well, that was really close what you have to do is instead of the quote, quote is to put the N/A function.
N/A will force the N/A or to show up the great thing usually we hate N/A's but the great thing about N/A's is that they caused our chart to not plot any data the chart won't show any data for NA so, makes the chart look good.
Now, unfortunately it makes the spreadsheet look really bad we've introduced a new problem if you're going to print this and go to Page Layout click the dialog launcher and back on the Sheet tab, Cell errors as usually as displayed, but I change it to blank, to make sure those cell error don't print they still show up in the spreadsheet though. That's driving crazy.
Let's do this. Let's choose these cells and we're going to go into Conditional Formatting.
So, Home, Conditional Formatting, New Rule and we're gonna use a formula to determine which cells to format the formula has to refer to the active cell, the active cell in this case being K2.
so,=NA(K2).
If that's true and it will be replicated throughout, then I want to use a white font so, let's do that click OK, click OK and you see that now the future numbers are not appearing as N/A they won't print as N/A because we change the page setup and also, the total is working.
Now, let's just test to make sure that's working, 1800 see the chart updates there for the new value.
So, it seems like it shouldn't be that hard, but there you have it.
Once you get it set up with those N/A's and get those N/A's hidden.
Glenn you'll be able to go through and update this every month just by plugging in a new number new charts will update.
There you have it.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
I'm Bill Jelen.
Basically, we start out with massive amounts of data How we're gonna analyze this. Well, plus fire up a pivot table.
Let's see if you can solve this problem.
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Great question today sent in by Glenn.
Glenn has three components A, B and C, and then a Total over here.
He wants to create charts from these and the chart is working great.
On the component items A, B or C.
So, you can nice little chart here shows the year day revenue life is good, right?
But then when he tries to create the same chart from the total.
It has this...
the drop to zero so we create the chart all of a sudden for the future months baam we are to 0.
It's like the company went out of business. Well, it's not the company went out of business.
It's just that we don't have any future data, but take a look at how good it looks here, and how bad it looks here.
Glenn says I have a lot of charts. I don't want to update these every month.
I don't want to edit the series formula, or do anything like that, and he admitted writing for he says well look I'm using this formula over here in the total to make sure that we don't see anything if it's not filled in.
So, if the sum of these three cells is zero, then put quote, quote in other words nothing and then otherwise put the sum.
Well, that was really close what you have to do is instead of the quote, quote is to put the N/A function.
N/A will force the N/A or to show up the great thing usually we hate N/A's but the great thing about N/A's is that they caused our chart to not plot any data the chart won't show any data for NA so, makes the chart look good.
Now, unfortunately it makes the spreadsheet look really bad we've introduced a new problem if you're going to print this and go to Page Layout click the dialog launcher and back on the Sheet tab, Cell errors as usually as displayed, but I change it to blank, to make sure those cell error don't print they still show up in the spreadsheet though. That's driving crazy.
Let's do this. Let's choose these cells and we're going to go into Conditional Formatting.
So, Home, Conditional Formatting, New Rule and we're gonna use a formula to determine which cells to format the formula has to refer to the active cell, the active cell in this case being K2.
so,=NA(K2).
If that's true and it will be replicated throughout, then I want to use a white font so, let's do that click OK, click OK and you see that now the future numbers are not appearing as N/A they won't print as N/A because we change the page setup and also, the total is working.
Now, let's just test to make sure that's working, 1800 see the chart updates there for the new value.
So, it seems like it shouldn't be that hard, but there you have it.
Once you get it set up with those N/A's and get those N/A's hidden.
Glenn you'll be able to go through and update this every month just by plugging in a new number new charts will update.
There you have it.
Wanna thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Thanks for stopping by.
We'll see you next time for another netcast form MrExcel.
