Is it possible for charting to ignore parts of data
ranges which are neither numbers nor dates? (So far as I
can tell it reads them as 0s.)
Regards,
giacomo

Giacomo -

You should delete the non numeric cells. Excel can at least skip a blank
cell. It can't skip an apparently blank cell produced by "" in a
formula, because "" is a text entry even though it's zero characters
long. If you change "" to NA() in a formula, you get an ugly error in
the sheet, but the chart doesn't plot it.

If you're stuck displaying the text within the data table, you could
make another table elsewhere, linked to this one, but without the text.
Use the new table as your chart's data range.

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

Giacomo wrote:

> Is it possible for charting to ignore parts of data
> ranges which are neither numbers nor dates? (So far as I
> can tell it reads them as 0s.)
> Regards,
> giacomo

thanks Jon,
I've (almost completely) succeded via filtering out of
the data range blank cells (nonblank). I don't like the
fact that it hides all the rows corresponding to the
blank column cells in the data range and I haven't been
able to fix that (help!). But charting wise it works
perfectly.
By the way substituting "" with na() does not work as the
chart still puts the na values on the axis.
Thanks for your time,
gicomo

>-----Original Message-----
>Giacomo -
>
>You should delete the non numeric cells. Excel can at
least skip a blank
>cell. It can't skip an apparently blank cell produced
by "" in a
>formula, because "" is a text entry even though it's
zero characters
>long. If you change "" to NA() in a formula, you get an
ugly error in
>the sheet, but the chart doesn't plot it.
>
>If you're stuck displaying the text within the data
table, you could
>make another table elsewhere, linked to this one, but
without the text.
>Use the new table as your chart's data range.
>
>- Jon
>-------
>Jon Peltier, Microsoft Excel MVP
>Peltier Technical Services
>Tutorials and Custom Solutions
>http://PeltierTech.com/
>_______
>
>Giacomo wrote:
>
>> Is it possible for charting to ignore parts of data
>> ranges which are neither numbers nor dates? (So far as
I
>> can tell it reads them as 0s.)
>> Regards,
>> giacomo
>
>.
>

Giacomo -

What you could do is filter the data, copy it, use Paste Special from
the Edit menu to Paste-Link it elsewhere in the worksheet (where it
won't be seen), and use this new range for the chart's data source.
Since you linked it, updating the original data will change the chart.

What kind of chart are you making? The NA() doesn't appear on a value
axis, but it does on a category axis. So a line chart will have an #N/A
category, but with no marker if the Y value is also #N/A.

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

giacomo wrote:

> thanks Jon,
> I've (almost completely) succeded via filtering out of
> the data range blank cells (nonblank). I don't like the
> fact that it hides all the rows corresponding to the
> blank column cells in the data range and I haven't been
> able to fix that (help!). But charting wise it works
> perfectly.
> By the way substituting "" with na() does not work as the
> chart still puts the na values on the axis.
> Thanks for your time,
> gicomo
>
>
>>-----Original Message-----
>>Giacomo -
>>
>>You should delete the non numeric cells. Excel can at
>
> least skip a blank
>
>>cell. It can't skip an apparently blank cell produced
>
> by "" in a
>
>>formula, because "" is a text entry even though it's
>
> zero characters
>
>>long. If you change "" to NA() in a formula, you get an
>
> ugly error in
>
>>the sheet, but the chart doesn't plot it.
>>
>>If you're stuck displaying the text within the data
>
> table, you could
>
>>make another table elsewhere, linked to this one, but
>
> without the text.
>
>>Use the new table as your chart's data range.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Giacomo wrote:
>>
>>
>>>Is it possible for charting to ignore parts of data
>>>ranges which are neither numbers nor dates? (So far as
>
> I
>
>>>can tell it reads them as 0s.)
>>>Regards,
>>>giacomo
>>
>>.
>>