Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

Not sure I understand your question.

But what I use to do when Excel is putting zeros in cells that I want to
keep blank, is as follow:
(Keep in mind that I am using I Norwegian Excel program, so I have to
translate to English)

Right click the cell(s) you want to changes and select €œFormat cells€�. Go to
€œNumber€� (its where you choose what format your data should have in the
cell) and choose €œown definition€�. Wright €œ €œ (exclamation mark with space
between) if its text that appears as zero or just # if its numbers.

Have a nice day :)
ElFrodo


"pczerny" wrote:

Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

ElFrodo,


Thanks much for the very prompt reply. I obviously, wasn't clear enough. I
am using the INDEX function to pull data out of a dataset. Cells that are
blank get converted to zeros by the INDEX function. I would like to change
the zeros in the cells back to blank cells so that when graphed, the graph
will show a gap because there is no data rather than a zero. I don't know if
that's clear enough.
--
Paul Czerny


"ElFrodo" wrote:

Not sure I understand your question.

But what I use to do when Excel is putting zeros in cells that I want to
keep blank, is as follow:
(Keep in mind that I am using I Norwegian Excel program, so I have to
translate to English)

Right click the cell(s) you want to changes and select €œFormat cells€�. Go to
€œNumber€� (its where you choose what format your data should have in the
cell) and choose €œown definition€�. Wright €œ €œ (exclamation mark with space
between) if its text that appears as zero or just # if its numbers.

Have a nice day :)
ElFrodo


"pczerny" wrote:

Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

There is unfortunately no function that returns a blank. We would like to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A in the
chart, which isn't exactly a blank: no point is drawn in a line or XY chart
for this cell, and any line connecting data points connects the points on
either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pczerny" wrote in message
...
Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd
like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

Jon,

Thank you for your prompt reply. I stumbled upon the NA() solution and
while it's misleading (if there is no data, there should be a gap in the
graph) it's better than the graph going to zero and then coming back up.
I'm assuming that the MVP group has pushed for such a function to be included?

Thanks again.

--
Paul Czerny


"Jon Peltier" wrote:

There is unfortunately no function that returns a blank. We would like to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A in the
chart, which isn't exactly a blank: no point is drawn in a line or XY chart
for this cell, and any line connecting data points connects the points on
either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pczerny" wrote in message
...
Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd
like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

If I understand correctly:
You have plotted a range of cells which include one or more empty (blank cells)
On the chart you want the line to break when at a blank.
Click chart to activate the chart; use Tools | Options and open the Chart tab
Select "Plot empty cells ..Not potted (leave gaps)"
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pczerny" wrote in message ...
Jon,

Thank you for your prompt reply. I stumbled upon the NA() solution and
while it's misleading (if there is no data, there should be a gap in the
graph) it's better than the graph going to zero and then coming back up.
I'm assuming that the MVP group has pushed for such a function to be included?

Thanks again.

--
Paul Czerny


"Jon Peltier" wrote:

There is unfortunately no function that returns a blank. We would like to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A in the
chart, which isn't exactly a blank: no point is drawn in a line or XY chart
for this cell, and any line connecting data points connects the points on
either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pczerny" wrote in message
...
Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd
like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

Bernard,


Oops...I'm obviously, not being clear enough. I am pulling data from a
dataset and in some instances doing mathematical manipulations. Excel
converts a blank cell to a zero when you use a lookup function (I'm using
INDEX). I can easily enough determine that there is a blank cell in the
dataset using the ISBLANK function. What I've not been able to do is create
a blank cell in the data that I'm using to create a graph. If I could create
a blank cell, I would be able to show a gap in the data which is accurate.
For now I'm stuck with plotting the zero that Excel returns or use the NA()
function to interpolate over the missing datum.

Don't know if this is any more clear that my original note.
--
Paul Czerny


"Bernard Liengme" wrote:

If I understand correctly:
You have plotted a range of cells which include one or more empty (blank cells)
On the chart you want the line to break when at a blank.
Click chart to activate the chart; use Tools | Options and open the Chart tab
Select "Plot empty cells ..Not potted (leave gaps)"
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pczerny" wrote in message ...
Jon,

Thank you for your prompt reply. I stumbled upon the NA() solution and
while it's misleading (if there is no data, there should be a gap in the
graph) it's better than the graph going to zero and then coming back up.
I'm assuming that the MVP group has pushed for such a function to be included?

Thanks again.

--
Paul Czerny


"Jon Peltier" wrote:

There is unfortunately no function that returns a blank. We would like to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A in the
chart, which isn't exactly a blank: no point is drawn in a line or XY chart
for this cell, and any line connecting data points connects the points on
either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pczerny" wrote in message
...
Hi,

I'm trying to create a blank cell in excel 2003. When I move data from a
blank cell, excel turns the data from a blank cell into a zero and I'd
like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny

I agree Excel should handle this. But it does not so:
In B2:B20 I have a formula that either returns a number or it returns #N/A
I select B2:B20 and run this macro
Now I use the C column for my y-values in the chart
Hope this helps

Sub tryme()
For Each cell In Selection
If IsNumeric(cell.Value) Then
cell.Offset(rowOffset:=0, columnOffset:=1) = cell.Value
End If
Next
End Sub

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pczerny" wrote in message
...
Bernard,


Oops...I'm obviously, not being clear enough. I am pulling data from a
dataset and in some instances doing mathematical manipulations. Excel
converts a blank cell to a zero when you use a lookup function (I'm using
INDEX). I can easily enough determine that there is a blank cell in the
dataset using the ISBLANK function. What I've not been able to do is
create
a blank cell in the data that I'm using to create a graph. If I could
create
a blank cell, I would be able to show a gap in the data which is accurate.
For now I'm stuck with plotting the zero that Excel returns or use the
NA()
function to interpolate over the missing datum.

Don't know if this is any more clear that my original note.
--
Paul Czerny


"Bernard Liengme" wrote:

If I understand correctly:
You have plotted a range of cells which include one or more empty (blank
cells)
On the chart you want the line to break when at a blank.
Click chart to activate the chart; use Tools | Options and open the Chart
tab
Select "Plot empty cells ..Not potted (leave gaps)"
best wishes

--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email

"pczerny" wrote in message
...
Jon,

Thank you for your prompt reply. I stumbled upon the NA() solution and
while it's misleading (if there is no data, there should be a gap in
the
graph) it's better than the graph going to zero and then coming back
up.
I'm assuming that the MVP group has pushed for such a function to be
included?

Thanks again.

--
Paul Czerny


"Jon Peltier" wrote:

There is unfortunately no function that returns a blank. We would like
to
have something like BLANK() or NULL(), but it's not available.

=IF(LEN(A1)=0,NA(),A1)

LEN(A1) checks for a blank. If there is a blank, NA() turns into #N/A
in the
chart, which isn't exactly a blank: no point is drawn in a line or XY
chart
for this cell, and any line connecting data points connects the points
on
either side.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______


"pczerny" wrote in message
...
Hi,

I'm trying to create a blank cell in excel 2003. When I move data
from a
blank cell, excel turns the data from a blank cell into a zero and
I'd
like
it left as a blank cell so that a graph of the data shows a gap.

Thanks ahead of time.

--
Paul Czerny