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

Feb 19th, 2002, 06:13 AM

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 ?

JugglerJAF · Feb 19th, 2002, 07:35 AM

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.

Barrie Davidson · Feb 19th, 2002, 07:37 AM

Change your IF formula to return NA() instead of "". Excel won't graph NA() values.

Feb 19th, 2002, 08:50 AM

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.

Barrie Davidson · Feb 19th, 2002, 09:22 AM

Quote:

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())

Feb 19th, 2002, 03:58 PM

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 !!!

Dr. Strangelove · Feb 19th, 2002, 04:03 PM

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...

Feb 19th, 2002, 07:35 AM	#2
JugglerJAF Board Regular Join Date: Feb 2002 Location: North Yorkshire, UK Posts: 173	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. __________________ A wise man can see more from the bottom of a well than a fool can see from the top of a mountain.

Feb 19th, 2002, 07:37 AM	#3
Barrie Davidson MrExcel MVP Join Date: Feb 2002 Location: Winnipeg Posts: 2,330	Change your IF formula to return NA() instead of "". Excel won't graph NA() values. __________________ Barrie Davidson "You're only given a little spark of madness. You mustn't lose it." - Robin Williams

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Feb 19th, 2002, 06:13 AM	#1
Guest Posts: n/a	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 ?

Feb 19th, 2002, 08:50 AM	#4
Guest Posts: n/a	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.

Feb 19th, 2002, 03:58 PM	#6
Guest Posts: n/a	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 !!!

Feb 19th, 2002, 04:03 PM	#7
Dr. Strangelove New Member Join Date: Feb 2002 Location: Tilburg, the Netherlands Posts: 7	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...