0 (zero) values shown in line graph for empty values - (EDIT

G

Guest

Guest
I use a bar graph to display actual data and a line graph to display Budget data. Now, for obvious reasons, I only want to show data until the current month. The problem is that the line graph shows 0 (zero) values (which are the result of a formula like IF[ (A1="FEB-02";B1;"") ]and the bar graph does not show 0 (zero) values.
I get the following situation in a graph:
------ Jan Feb Mar Apr May
Actual 5 6 8
Budget 6 5 5 0 0
Why does the line not show the same behaviour as the bar ? I do not want the line to show 0 values or "" as 0 (zero).
BTW. Options-Tools-Graph shows: "Plot empty cells as: Not Plotted (leave gaps)"'
Apparently this only works for the Bar-Graph.
What can I do or is this just a peculiar habit of Excel2000 ?
 

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
I think this is one of those delightful "features" of Excel.

The solution I use is to delete any records where the value is zero. If your chart is using formulas, then you can avoid the problem for "future months" by only copying down your formula for the months where you have data.

It isn't an issue on a bar or column chart as a zero value would display a bar/column of zero height - i.e. not visible. When using the line chart, the data point markers are visible as is the line from the previous point which did contain a value.
 
Upvote 0
I have tried using blank, N/A(), #N/A, 1/0(to give #ERR) and all to no avail. I still get the graph line plunging to zero for the rest of the year.
 
Upvote 0
On 2002-02-19 07:50, Anonymous wrote:
I have tried using blank, N/A(), #N/A, 1/0(to give #ERR) and all to no avail. I still get the graph line plunging to zero for the rest of the year.
No, not N/A(), NA(). NA() returns the #N/A value for Excel. Try this:
IF(A1="FEB-02",B1,NA())
 
Upvote 0
Haha, the Anonymous is another anonymous as me Anonymous ! Apparently an anonymous with the same problem.
You know what ? I am going to register myself.
From now on I will be Dr. Strangelove !!!
 
Upvote 0
Yep, now I'm Dr. Strangelove. (Go watch that movie).
All people who took the time&effort to react upon my request. Thanks !
The NA() definitively did the trick !!
Life is Great, Excel isn't bad - just peculiar sometimes...
 
Upvote 0
Thanks, Barrie! I had been pulling my hair out on this exact issue and found your post from 11 years ago -- still works like a charm today in current versions of Excel and makes my line charts look much better.
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top