Zero is a dropdown Take 2

[DKY]

I tried this earlier and nothing happened. I'm setting up a chart with
several series instead of a data range (wow, looks like I know what I'm
talking about. LOL) and I'm doing a line graph. Problem is that when I
have zero's or an N/A it drops down and then comes back up. I'm looking
to have it just stop and then start back up the next week, instead of
dropping down but don't know how to do that or even what to call it or
else I would search for it.

 

[Kelly O'Day]

DKY:

There are several possible conditions that can cause your chart to drop to
zero.

1. Your data cell has a zero (0).
2. Your data cell is blank. You can use Tools >> Options > Chart > Plot
Empty Cells as (Not Plotted - Leave Gaps)
3. Your data cell has a formula like = If (A1 = "", "", 99) or =
If(A1="",Na(),99)
4. Your data cell has text instead of a number (Excel plots text as zero's)

The specific fix depends on your situation.

If you have empty cells (no formulas or values), then the tools > Options >
Charts > Plot empty Cells as choice will solve problem

If you have zeros, Excel will plot them as zero unless you remove them or
use a formula like = If(A1=0,Na(),A1)

If you have cells with string (you mentioned N/A), then Excel will plot as
0.

There are many web posts on plotting missing data. You may want to start
with this one:

http://processtrends.com/pg_charts_missing_data.htm

...Kelly

(e-mail address removed)

 

[DKY]

Actually I have a formula in there that looks like this
=IF(D10<>"",1-(E10/D10),"")
and when I go to Tools >> Options > Chart > Plot
Empty Cells as
is already set to (Not Plotted - Leave Gaps)
So, I would think that it would not plot it. I even tried deleting the
formula and it still plots as zero.

 

[Kelly O'Day]

DKY

Excel treats cells with formulas as Not Empty. The Not Plotted (Leave Gaps)
doesn't work because the cell is not empty.

When you have formulas, the fix is to use NA() instead of "". Excel will
place a #N/A in those cells. Excel recognizes NA() and interpolates through
the #N/A cells.

Your formula should look like: = IF(D10<>"",1-(E10/D10),NA())

If you want gaps in your data series instead of interpolation, then you can
replace the formulas with values by copying data and using Edit - Paste
Special Values then remove all #N/As.

....Kelly

(e-mail address removed)

 

[DKY]

I used the formula and it gives me the #N/A but the chart still has
line that shoots down to zero in the middle of it. I'm not sure wha
I'm doing wrong here, I even got rid of the formula, line's stil
there. I then put a #N/A in the cell, line's still there. I don't ge
it. I have the option selected in the options that if its blank the
don't plot but when the cell is empty it still draws a line down an
plots it at zero. I'm so lost

 

[DKY]

Does the fact that I'm plotting several different series in one chart
matter? The reason I ask is that one of the series works where it
doesn't plot but the other two don't

 

[Guest]

Hi:
Just a hunch: could it be that you have (1) a hidden row in your data range
with zero, text or blank value AND (2) you have unchecked "Plot visible cells
only" in Tools/Options/Chart?
Henk

 

[DKY]

Okay, Here's what I've got

Not plotted (leave gaps) Checked
Plot visible cells only UnChecked
Chart sizes with window frame Checked
Show names Checked
Show values Checked

each of my three series are from three different sheets and there are
no values where it should be a no value, not even a function. Two of
my three are doing what they're supposed to be doing but one is
dropping down to the zero's for some reason. I don't understand, could
there be something wrong with the sheet itself?

 

[DKY]

I made a new sheet for the one series and replotted it, it works now. I
don't know what the problem was but thanks for the help everyone.

 

[Guest]

I wld try checking "Plot visible cells only". Hit F9 if you set yr
calculation to manual and if this solves your problem you have hidden rows
out there with zero, text or blank values, giggling in the dark.
It may not seem very likely, but all you can do is checking all possible
causes Kelly mentioned carefully one by one. In particular check Kelly's
fourth option. If your cell is FORMATTED as text (check Cell/Format/Number),
a number may very well be TREATED as text and this can be non-obvious as you
entered it as a number and you see it as a number, but Excel treats it as a
text label. I hope this helps.
Henk