Today's Podcast looks at Future Zeros. If your chart shows future months as a line at zero, replace the line with blanks. With Episode #1338, Bill shows us how to remove future zeros in to produce a sharp looking Chart for presenting our future Data.
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Excel Charts, chapter 14.
Replace Zeroes.
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 the turn off, you know they show here back in 2007 and started around ten thousand dollars and went up through 2008, up through 2009.
And you know, I'm sorry, I was kind of a smart 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 go, 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, all right!
So, it was coming from another spot and because there was no data in the future.
Those numbers were showing up as zeroes which causes the chart to look as if you know, basically the fund balance went to zero.
So, what we want to do here is we want to take the existing formula and say equal IF.
Equal IF this is equal to 0, then we want to put in NA there.
NA, so we're going to use the NA function, NA open parenthesis, close parenthesis.
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 going to be a bit of a hassle to go through and do this.
But you see that changes those values to N A's in the future and the beautiful thing about NA, is that on a chart, NA is not plotted at all.
We don't get any markers and you know, it looks like the fund balance is up there.
Very nice, seventy thousand or so.
It doesn't go, just crashing down the zero.
All right! So, if you ever had this, where the chart in the future month there's no data there.
So, your formulas returning zero and the chart shows that, see if you can change those future months to NAs to prevent this view...
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
Excel Charts, chapter 14.
Replace Zeroes.
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 the turn off, you know they show here back in 2007 and started around ten thousand dollars and went up through 2008, up through 2009.
And you know, I'm sorry, I was kind of a smart 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 go, 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, all right!
So, it was coming from another spot and because there was no data in the future.
Those numbers were showing up as zeroes which causes the chart to look as if you know, basically the fund balance went to zero.
So, what we want to do here is we want to take the existing formula and say equal IF.
Equal IF this is equal to 0, then we want to put in NA there.
NA, so we're going to use the NA function, NA open parenthesis, close parenthesis.
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 going to be a bit of a hassle to go through and do this.
But you see that changes those values to N A's in the future and the beautiful thing about NA, is that on a chart, NA is not plotted at all.
We don't get any markers and you know, it looks like the fund balance is up there.
Very nice, seventy thousand or so.
It doesn't go, just crashing down the zero.
All right! So, if you ever had this, where the chart in the future month there's no data there.
So, your formulas returning zero and the chart shows that, see if you can change those future months to NAs to prevent this view...
Hey! I want to thank you for stopping by, we’ll see you next time for another netcast from MrExcel.
