View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.misc
Bernard Liengme Bernard Liengme is offline
external usenet poster
 
Posts: 4,393
Default how do I create a blank cell in excel 2003?

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