Series Formula

Hi all,
have a curious problem when setting the formula on a series via code
(C#)

When setting the formula property like this

currentSeries.Formula="SERIES(\"TradeTranche1\",Node!$B$10:$B$70,(Node!$DC$10:$DC$70),1)"

the value that the formula now has is "SERIES("TradeTranche1",,{1},2)"

I'm OK about the plot order being different but the XRange is missing?! and
the YRange has been set to 1?!

A related detail is that the XRange is a series of values that go from 1 -
48 and then repeat again from 1 - 48 again i.e. non unique. This doesn't
seem to be an issue in other charting situations but may be relevant.

Thanks in advance

Mark

Don't ask me about C# (I thought that meant C-pound, until my musician
daughter laughed at me!), but I know a little about charts.

=SERIES("TradeTranche1",,{1},2)

looks like the series formula for a newly added series that has only had its
series name defined. Excel uses the one element array {1} for the default Y
Values of a new series.

In your series formula command, why are the Y values in parens but the X
values are not? This sequence:

activechart.SeriesCollection.newseries
activechart.SeriesCollection(2).formula= _
"=SERIES(""TradeTranche1"",Node!$B$10:$B$70,(Node!$DC$10:$DC$70),1)"

leaves me with a one-point series with this formula:

=SERIES(,,{1},2)

in which not even the series name is changed. Whereas this

activechart.SeriesCollection.newseries
activechart.SeriesCollection(2).formula= _
"=SERIES(""TradeTranche1"",Node!$B$10:$B$70,Node!$DC$10:$DC$70,1)"

produces a series with this formula:

=SERIES("TradeTranche1",Node!$B$10:$B$70,Node!$DC$10:$DC$70,1)

Remove the extra parentheses and see what you get.

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


"Sarge" <[email protected]> wrote in message
news:e3zYTGr%[email protected]...
> Hi all,
> have a curious problem when setting the formula on a series via code
> (C#)
>
> When setting the formula property like this
>
> currentSeries.Formula="SERIES(\"TradeTranche1\",Node!$B$10:$B$70,(Node!$DC$10:$DC$70),1)"
>
> the value that the formula now has is "SERIES("TradeTranche1",,{1},2)"
>
> I'm OK about the plot order being different but the XRange is missing?!
> and the YRange has been set to 1?!
>
> A related detail is that the XRange is a series of values that go from 1 -
> 48 and then repeat again from 1 - 48 again i.e. non unique. This doesn't
> seem to be an issue in other charting situations but may be relevant.
>
> Thanks in advance
>
> Mark
>
>
>
>
>

Hi Jon,

firstly thanks for the reply,

secondly your knowledge is strong in the ways of excel.

Just for further clarification which may be of some value for others:

My code had been dynamically creating a set of ranges to make up the series,
in most cases it would be like this

"=SERIES(""TradeTranche1"",Node!$B$10:$B$70,(Node!$AZ$10:$AZ$20,
Node!$DC$21:$DC$70),1)"

But in some cases the node range Node!$AZ$10:$AZ$20, was not required and
hence leaving a single range with brackets around it and causing some kind
non lethal parsing error.

Thanks for the solution!

Mark


"Jon Peltier" <[email protected]> wrote in message
news:OcB5q12%[email protected]...
> Don't ask me about C# (I thought that meant C-pound, until my musician
> daughter laughed at me!), but I know a little about charts.
>
> =SERIES("TradeTranche1",,{1},2)
>
> looks like the series formula for a newly added series that has only had
> its series name defined. Excel uses the one element array {1} for the
> default Y Values of a new series.
>
> In your series formula command, why are the Y values in parens but the X
> values are not? This sequence:
>
> activechart.SeriesCollection.newseries
> activechart.SeriesCollection(2).formula= _
> "=SERIES(""TradeTranche1"",Node!$B$10:$B$70,(Node!$DC$10:$DC$70),1)"
>
> leaves me with a one-point series with this formula:
>
> =SERIES(,,{1},2)
>
> in which not even the series name is changed. Whereas this
>
> activechart.SeriesCollection.newseries
> activechart.SeriesCollection(2).formula= _
> "=SERIES(""TradeTranche1"",Node!$B$10:$B$70,Node!$DC$10:$DC$70,1)"
>
> produces a series with this formula:
>
> =SERIES("TradeTranche1",Node!$B$10:$B$70,Node!$DC$10:$DC$70,1)
>
> Remove the extra parentheses and see what you get.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> "Sarge" <[email protected]> wrote in message
> news:e3zYTGr%[email protected]...
>> Hi all,
>> have a curious problem when setting the formula on a series via code
>> (C#)
>>
>> When setting the formula property like this
>>
>> currentSeries.Formula="SERIES(\"TradeTranche1\",Node!$B$10:$B$70,(Node!$DC$10:$DC$70),1)"
>>
>> the value that the formula now has is "SERIES("TradeTranche1",,{1},2)"
>>
>> I'm OK about the plot order being different but the XRange is missing?!
>> and the YRange has been set to 1?!
>>
>> A related detail is that the XRange is a series of values that go from
>> 1 - 48 and then repeat again from 1 - 48 again i.e. non unique. This
>> doesn't seem to be an issue in other charting situations but may be
>> relevant.
>>
>> Thanks in advance
>>
>> Mark
>>
>>
>>
>>
>>
>
>