Null values in charts and how to override the goal seek functionality

Hi all,

I am trying to chart data that contains null values, I want the null
values to be represented as a gap or such.
The chart series are dynamically created from code using a series
formula.
The chart needs to be editable as well as the underlying data.

My attempts at displaying null data is slowly painting me into a corner.

Issue 1:
The series formula will not allow null values to be included in the
selected cells. Any attempt to include a null value in the series range will
give me a COMException.

Workaround 1:
Bind the series to an area on the sheet filled with formulas like
IF(ISBLANK(D3),NA(),D3)
This allows me to display the chart and dynamically change range of
values being displayed. Yay. NA() = graphical null value!?

Issue 2:
Editng the chart now invokes the goal seek helper requesting that I
specify the cell to change.
This renders the chart editing functionality useless because now a value
needs to be entered to allow the goal seek to perform and change the
underlying value.

Questions
Can I somehow display null values in the chart without compomising the
editing behaviour?
Can I override the GOAL SEEK behaviour?


I am developing an excel solution using VSTO 2003 and C#.

Thanks in advance

Mark Sargent

Issue 1: Are you selecting a range that includes a blank cell or #N/A, or
are you trying to define an array which includes some programming language's
representation of a null value? I don't know VSTO or C#, so I can't comment
on the error. Does it give you any description? Based on your workaround, I
guess you're selecting a range of cells, and "" in a cell produces the
error.

The only true null value in Excel is a blank cell. NA() is a useful
workaround in that it allows you to interpolate a line across a missing
value in a line or XY chart, and prevents a text string (which "" is,
right?) from being interpreted by Excel as zero. NA() is not a null value,
and it cannot produce a gap between points. You could have your program
delete the contents of a cell that needs to be blank to chart properly.

Issue 2: This occurs when you try to change a plotted value in a chart by
dragging a point. If the cell on which the point depends contains a formula,
Excel needs to know which precedent cell is to be changed to provide the
desired chart value. Is your program dragging the point, or are the users
doing it?

To prevent this but still allow other formatting, you'd need to unlock the
chart, or rather, unlock the shape representing the chart; protect the
chart's data, as in Chart.ProtectData; and protect the sheet.

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

"Sarge" <[email protected]> wrote in message
news:u5G6j%[email protected]...
> Hi all,
>
> I am trying to chart data that contains null values, I want the null
> values to be represented as a gap or such.
> The chart series are dynamically created from code using a series
> formula.
> The chart needs to be editable as well as the underlying data.
>
> My attempts at displaying null data is slowly painting me into a corner.
>
> Issue 1:
> The series formula will not allow null values to be included in the
> selected cells. Any attempt to include a null value in the series range
> will give me a COMException.
>
> Workaround 1:
> Bind the series to an area on the sheet filled with formulas like
> IF(ISBLANK(D3),NA(),D3)
> This allows me to display the chart and dynamically change range of
> values being displayed. Yay. NA() = graphical null value!?
>
> Issue 2:
> Editng the chart now invokes the goal seek helper requesting that I
> specify the cell to change.
> This renders the chart editing functionality useless because now a
> value needs to be entered to allow the goal seek to perform and change the
> underlying value.
>
> Questions
> Can I somehow display null values in the chart without compomising the
> editing behaviour?
> Can I override the GOAL SEEK behaviour?
>
>
> I am developing an excel solution using VSTO 2003 and C#.
>
> Thanks in advance
>
> Mark Sargent
>
>
>
>