Unwanted zero values in a graph

[Zadig Galbaras]

Hi!

Here I am once again.

I have some graphs in a spreadsheet, and the sourcedata is being filled with
values as time goes on and I key in new values every fisrt day of the month.
Hence the cells containing future readings is empty.

The graph jumps from the latest value lets say 40, and stoops down to zero
on the X-axis, and follows it the for the rest of the year.

How can I get my graphs to stop showing any line after the latest typed
value?
I have tried every possible combination when cliking the graph, choose
Options on the Tools menu, and the tab named Graph, but in vain. Nothing
happens.


--

regards
Zadig Galbaras
A Perturbed Norwegian Agnostic
-

 

[Guest]

From what you are saying, I wonder if the cells are really empty. Try
deleting the entire contents of the "blank" cells. If this does not work,
you can select each segment of the chart (you don't say what kind--line, bar,
etc.) and cause it to have no pattern, color, fill, etc. (Format Data Series).

 

[Ragdyer]

The #N/A error message is what you're looking for.
It works very well with graphs by not equating to zero.

So, revise your formulas to something like this:

=IF(OR(A1="",B1=""),# N/A,A1/B1)

 

[Guest]

Hi,
I tried to mimic your problem by creating x-data containing 12 numbers (1
thru 12 in A1 thru A12) and y-data containing 6 numbers (in B1 thru B6) and
making XY-Scatter as well as Line plots by selecting A1:A12 and B1:B12. Both
graphs were fine, i.e., only the six data-points showed up, and the empty
values (B7 thru B12) DIDN'T show up as zeros on the graphs. I changed the
x-values to Jan 2005, Feb 2005, thru Dec 2005, and the line plot was still
fine. So I couldn't reproduce your problem. Are you sure your y-range cells
for future months were really blank (and didn't contain zeros)?
Or, you could use a self-expanding chart (For an excellent discussion, visit
'Stephen Bullen's Excel Page' at www.oaltd.co.uk/Excel/ and follow the link
'Professional Excel Development' ...'Advanced Charting Techniques'. It goes
as follows:

Name the x- and y- ranges first (Insert-Name-Define)
Names in workbook: Refers to:
yrange
=offset(Sheet1!$B$1,0,0,counta(Sheet1!$B:$B),1)
xrange =offset(yrange,0,-1)

Now create a dummy Line Chart (with a couple of x,y data), and get into
Source Data - Series, and enter =Sheet1!yrange for Values and =Sheet1!yrange
for Category (X) axis labels. The plot should contain only points for which
y-range contains values, and should update as you add more values.
Regards,
B.R.Ramachandran