Graphing "" values

I am attmepting to graph data that includes both numerical values and
non-numerical (i.e., "") values. The data is as follows:
A B C D
E
(row 1) 10/01/05 11/01/05 12/01/05 TOTAL
(row 2)# Accounts 62 3 65

Since data for D2 (december of 05) does not yet exist, I don't want anything
in that cell (I have a formula that places "" in that cell in that scenario).
But my problem is that when I graph A2:D2, D2 is interpreted as a 0 and not
as the absence of a number.

How do I get Excel to only graph the first two values in that range without
changing the range (the example above is a small section of a 8 MB file).

Thanks,

Jim

Excel interprets text numerically as zero. "" is nothing but a short text
string, it is anything but a blank. In Excel line and XY charts, use NA()
instead of "". This puts a nasty #N/A error in the worksheet (which you can
hide with conditional formatting), but it is treated nicely in the chart.

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


"jim314" <[email protected]> wrote in message
news:[email protected]...
>I am attmepting to graph data that includes both numerical values and
> non-numerical (i.e., "") values. The data is as follows:
> A B C D
> E
> (row 1) 10/01/05 11/01/05 12/01/05 TOTAL
> (row 2)# Accounts 62 3 65
>
> Since data for D2 (december of 05) does not yet exist, I don't want
> anything
> in that cell (I have a formula that places "" in that cell in that
> scenario).
> But my problem is that when I graph A2:D2, D2 is interpreted as a 0 and
> not
> as the absence of a number.
>
> How do I get Excel to only graph the first two values in that range
> without
> changing the range (the example above is a small section of a 8 MB file).
>
> Thanks,
>
> Jim

That did make the chart look beautifully, but now the sum of all of those
"#NA" is giving me an "#NA"

Any ideas on how I can keep the chart looking good with the #NA and still be
able to sum the values?

"Jon Peltier" wrote:

> Excel interprets text numerically as zero. "" is nothing but a short text
> string, it is anything but a blank. In Excel line and XY charts, use NA()
> instead of "". This puts a nasty #N/A error in the worksheet (which you can
> hide with conditional formatting), but it is treated nicely in the chart.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> "jim314" <[email protected]> wrote in message
> news:[email protected]...
> >I am attmepting to graph data that includes both numerical values and
> > non-numerical (i.e., "") values. The data is as follows:
> > A B C D
> > E
> > (row 1) 10/01/05 11/01/05 12/01/05 TOTAL
> > (row 2)# Accounts 62 3 65
> >
> > Since data for D2 (december of 05) does not yet exist, I don't want
> > anything
> > in that cell (I have a formula that places "" in that cell in that
> > scenario).
> > But my problem is that when I graph A2:D2, D2 is interpreted as a 0 and
> > not
> > as the absence of a number.
> >
> > How do I get Excel to only graph the first two values in that range
> > without
> > changing the range (the example above is a small section of a 8 MB file).
> >
> > Thanks,
> >
> > Jim
>
>
>

This is where I remind people that they have an almost unlimited supply of
rows, columns, and worksheets. Start with the original data in a handy
place, arranged for any subsequent analysis. From this produce as many
copies of the data, in as many different arrangements as you need. One
arrangement for charting, with NA and whatever skipped rows you need to make
the chart come out right. Another arrangement to print out and hand to your
boss, with nice table gridlines, subtotals, bold and italic. If there are
multiple report formats you need, crank them out. Everything links back to
the original, so if something changes, update the original, and the changes
propagate through the workbook.

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


"jim314" <[email protected]> wrote in message
news:[email protected]...
> That did make the chart look beautifully, but now the sum of all of those
> "#NA" is giving me an "#NA"
>
> Any ideas on how I can keep the chart looking good with the #NA and still
> be
> able to sum the values?
>
> "Jon Peltier" wrote:
>
>> Excel interprets text numerically as zero. "" is nothing but a short text
>> string, it is anything but a blank. In Excel line and XY charts, use NA()
>> instead of "". This puts a nasty #N/A error in the worksheet (which you
>> can
>> hide with conditional formatting), but it is treated nicely in the chart.
>>
>> - Jon
>> -------
>> Jon Peltier, Microsoft Excel MVP
>> Peltier Technical Services
>> Tutorials and Custom Solutions
>> http://PeltierTech.com/
>> _______
>>
>>
>> "jim314" <[email protected]> wrote in message
>> news:[email protected]...
>> >I am attmepting to graph data that includes both numerical values and
>> > non-numerical (i.e., "") values. The data is as follows:
>> > A B C D
>> > E
>> > (row 1) 10/01/05 11/01/05 12/01/05 TOTAL
>> > (row 2)# Accounts 62 3 65
>> >
>> > Since data for D2 (december of 05) does not yet exist, I don't want
>> > anything
>> > in that cell (I have a formula that places "" in that cell in that
>> > scenario).
>> > But my problem is that when I graph A2:D2, D2 is interpreted as a 0 and
>> > not
>> > as the absence of a number.
>> >
>> > How do I get Excel to only graph the first two values in that range
>> > without
>> > changing the range (the example above is a small section of a 8 MB
>> > file).
>> >
>> > Thanks,
>> >
>> > Jim
>>
>>
>>