If your chart shows future months as a line at zero, replace the line with blanks.
Transcript of the video:
This is a podcast sponsored by Excel-XL.
Excel Charts Chapter 14; Replace Zeros.
All right so, I sit in one of those staff meetings, right and guys from accounting, they're talking about how this fund balance keeps going up and up and up and up and it sure enough you know they show here back in 2007 and started around 10,000 dollars and went up through 2008, up through 2009, you know I'm sorry I was kind of a smart I like about the whole thing I said.
whoa whoa! what happened last month, it looks like someone stole all the money and you know ran away to the bahamas or something like that and the guys like oh yeah i couldn't get rid of it ,couldn't get rid of the zeros all right.
Well all right, this is a common problem, he was getting this data from elsewhere, so it was coming from another spot and because there was no data in the future, those numbers were showing up as zeros.
Which causes the chart to look as if, you know basically, the fund balance went to zero.
So what we want to do, we want to take the existing formula and say equal if, equal if this is equal to 0, then we want to put an NA there, NA.
So, we're going to use the NA function.
NA, open paren, close paren, otherwise we want the original value, so Sheet1, exclamation, H5.
Now if every formula here is pointing in a different spot to Sheet.
It's gonna be a bit of a hassle to go through and do this, but you see that that changes those values and NA's in the future, another beautiful thing about NA is that on a chart, NA is not plotted at all.
We don't get any markers on it, you know it looks like the fund balance is up there.
The very nice 70,000 or so and doesn't go just crashing down to zero.
All right so, if you ever had this where the chart in the future month, there's no data there, so your formulas were showing zero.
The chart shows that see if, you can change those future months to NA's to prevent this view.
I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
Excel Charts Chapter 14; Replace Zeros.
All right so, I sit in one of those staff meetings, right and guys from accounting, they're talking about how this fund balance keeps going up and up and up and up and it sure enough you know they show here back in 2007 and started around 10,000 dollars and went up through 2008, up through 2009, you know I'm sorry I was kind of a smart I like about the whole thing I said.
whoa whoa! what happened last month, it looks like someone stole all the money and you know ran away to the bahamas or something like that and the guys like oh yeah i couldn't get rid of it ,couldn't get rid of the zeros all right.
Well all right, this is a common problem, he was getting this data from elsewhere, so it was coming from another spot and because there was no data in the future, those numbers were showing up as zeros.
Which causes the chart to look as if, you know basically, the fund balance went to zero.
So what we want to do, we want to take the existing formula and say equal if, equal if this is equal to 0, then we want to put an NA there, NA.
So, we're going to use the NA function.
NA, open paren, close paren, otherwise we want the original value, so Sheet1, exclamation, H5.
Now if every formula here is pointing in a different spot to Sheet.
It's gonna be a bit of a hassle to go through and do this, but you see that that changes those values and NA's in the future, another beautiful thing about NA is that on a chart, NA is not plotted at all.
We don't get any markers on it, you know it looks like the fund balance is up there.
The very nice 70,000 or so and doesn't go just crashing down to zero.
All right so, if you ever had this where the chart in the future month, there's no data there, so your formulas were showing zero.
The chart shows that see if, you can change those future months to NA's to prevent this view.
I wanna thank you for stopping by.
See you next time for another netcast from MrExcel.
