Graphing "" values

[jim314]

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 A22, 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

 

[Jon Peltier]

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]

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]

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/
_______