creating a directed graph from XY scatter

Would like to use XL to create a connected graph ( finance calls it a
binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.

XY scatter plots points OK. Need to connect points (nodes)--each node except
for t=T has two lines coming out of it.
A 3-peiod example is shown below. Eventually need to generalilze.

Data is
reference T Value
NOde 1... 0 100
NODE 2... 1 110
NODE 3... 1 90
NODE 4... 2 121
NODE 5... 2 100
NODE 6... 2 81

FOR NOW:
want a line to connect NODE 1 to NODE 2
want a line to connect NODE 1 to NODE 3
want a line to connect NODE 2 to NODE 4
want a line to connect NODE 2 to NODE 5
want a line to connect NODE 3 to NODE 5
want a line to connect NODE 3 to NODE 6

Suggestions would be greatly appreciated.

Luke

I suspect you'll need to have a different data series for each line. How
do you determine which nodes are connected to one another?

"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
wrote in message news:[email protected]...
> Would like to use XL to create a connected graph ( finance calls it a
> binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
>
> XY scatter plots points OK. Need to connect points (nodes)--each node
except
> for t=T has two lines coming out of it.
> A 3-peiod example is shown below. Eventually need to generalilze.
>
> Data is
> reference T Value
> NOde 1... 0 100
> NODE 2... 1 110
> NODE 3... 1 90
> NODE 4... 2 121
> NODE 5... 2 100
> NODE 6... 2 81
>
> FOR NOW:
> want a line to connect NODE 1 to NODE 2
> want a line to connect NODE 1 to NODE 3
> want a line to connect NODE 2 to NODE 4
> want a line to connect NODE 2 to NODE 5
> want a line to connect NODE 3 to NODE 5
> want a line to connect NODE 3 to NODE 6
>
> Suggestions would be greatly appreciated.
>
> Luke

In the simpest case:

1)All nodes are governed by analytics that specify the value of an "up move"
and the tha value of a "down move".... for example
UP = 1.1
Down = 0.909

2) In all cases the starting value is given

3) 100 * (Up=1.10) gives 110
100* (Down = 0.909) give `90
110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
110*(Down =0.909) gives ~100
90* (UP=1.1) gives ~ 100
90* (DOWN = 0.909) gives ~ 81

The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.


The UP and Down parameters can vary, by period, in more complex situations.
Thus the tree is "not recombining"

Luke

"Barb Reinhardt" wrote:

> I suspect you'll need to have a different data series for each line. How
> do you determine which nodes are connected to one another?
>
> "Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> wrote in message news:[email protected]...
> > Would like to use XL to create a connected graph ( finance calls it a
> > binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> >
> > XY scatter plots points OK. Need to connect points (nodes)--each node
> except
> > for t=T has two lines coming out of it.
> > A 3-peiod example is shown below. Eventually need to generalilze.
> >
> > Data is
> > reference T Value
> > NOde 1... 0 100
> > NODE 2... 1 110
> > NODE 3... 1 90
> > NODE 4... 2 121
> > NODE 5... 2 100
> > NODE 6... 2 81
> >
> > FOR NOW:
> > want a line to connect NODE 1 to NODE 2
> > want a line to connect NODE 1 to NODE 3
> > want a line to connect NODE 2 to NODE 4
> > want a line to connect NODE 2 to NODE 5
> > want a line to connect NODE 3 to NODE 5
> > want a line to connect NODE 3 to NODE 6
> >
> > Suggestions would be greatly appreciated.
> >
> > Luke
>
>
>

You could probably write an algorithm in VBA that would generate the nodal
coordinates, then dump them into cell positions which will produce the appropriate
line segments. You could even have a pair of columns like this, with skipped lines,
to build the lattice from a single series.

0 100
1 110

0 100
1 90

1 110
2 121

1 110
2 100

1 90
2 100

1 90
2 81

etc. Select the range, blank rows included, and draw an XY Scatter chart

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


Luke Spar UTAFortWorth wrote:

> In the simpest case:
>
> 1)All nodes are governed by analytics that specify the value of an "up move"
> and the tha value of a "down move".... for example
> UP = 1.1
> Down = 0.909
>
> 2) In all cases the starting value is given
>
> 3) 100 * (Up=1.10) gives 110
> 100* (Down = 0.909) give `90
> 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> 110*(Down =0.909) gives ~100
> 90* (UP=1.1) gives ~ 100
> 90* (DOWN = 0.909) gives ~ 81
>
> The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
>
>
> The UP and Down parameters can vary, by period, in more complex situations.
> Thus the tree is "not recombining"
>
> Luke
>
> "Barb Reinhardt" wrote:
>
>
>>I suspect you'll need to have a different data series for each line. How
>>do you determine which nodes are connected to one another?
>>
>>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
>>wrote in message news:[email protected]...
>>
>>>Would like to use XL to create a connected graph ( finance calls it a
>>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
>>>
>>>XY scatter plots points OK. Need to connect points (nodes)--each node
>>
>>except
>>
>>>for t=T has two lines coming out of it.
>>>A 3-peiod example is shown below. Eventually need to generalilze.
>>>
>>>Data is
>>>reference T Value
>>>NOde 1... 0 100
>>>NODE 2... 1 110
>>>NODE 3... 1 90
>>>NODE 4... 2 121
>>>NODE 5... 2 100
>>>NODE 6... 2 81
>>>
>>>FOR NOW:
>>>want a line to connect NODE 1 to NODE 2
>>>want a line to connect NODE 1 to NODE 3
>>>want a line to connect NODE 2 to NODE 4
>>>want a line to connect NODE 2 to NODE 5
>>>want a line to connect NODE 3 to NODE 5
>>>want a line to connect NODE 3 to NODE 6
>>>
>>>Suggestions would be greatly appreciated.
>>>
>>>Luke
>>
>>
>>

I can sure try the second suggestion.

I like the VBA suggestion a lot. But if the nodal cooridnates are in
"points" then I am stumped---I do not know how to find them. The idea of
"points" versus "XY coordinates " has me crazy".

Thanks a lot, I will try the suggestion tomorrow.




"Jon Peltier" wrote:

> You could probably write an algorithm in VBA that would generate the nodal
> coordinates, then dump them into cell positions which will produce the appropriate
> line segments. You could even have a pair of columns like this, with skipped lines,
> to build the lattice from a single series.
>
> 0 100
> 1 110
>
> 0 100
> 1 90
>
> 1 110
> 2 121
>
> 1 110
> 2 100
>
> 1 90
> 2 100
>
> 1 90
> 2 81
>
> etc. Select the range, blank rows included, and draw an XY Scatter chart
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Luke Spar UTAFortWorth wrote:
>
> > In the simpest case:
> >
> > 1)All nodes are governed by analytics that specify the value of an "up move"
> > and the tha value of a "down move".... for example
> > UP = 1.1
> > Down = 0.909
> >
> > 2) In all cases the starting value is given
> >
> > 3) 100 * (Up=1.10) gives 110
> > 100* (Down = 0.909) give `90
> > 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> > 110*(Down =0.909) gives ~100
> > 90* (UP=1.1) gives ~ 100
> > 90* (DOWN = 0.909) gives ~ 81
> >
> > The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
> >
> >
> > The UP and Down parameters can vary, by period, in more complex situations.
> > Thus the tree is "not recombining"
> >
> > Luke
> >
> > "Barb Reinhardt" wrote:
> >
> >
> >>I suspect you'll need to have a different data series for each line. How
> >>do you determine which nodes are connected to one another?
> >>
> >>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> >>wrote in message news:[email protected]...
> >>
> >>>Would like to use XL to create a connected graph ( finance calls it a
> >>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> >>>
> >>>XY scatter plots points OK. Need to connect points (nodes)--each node
> >>
> >>except
> >>
> >>>for t=T has two lines coming out of it.
> >>>A 3-peiod example is shown below. Eventually need to generalilze.
> >>>
> >>>Data is
> >>>reference T Value
> >>>NOde 1... 0 100
> >>>NODE 2... 1 110
> >>>NODE 3... 1 90
> >>>NODE 4... 2 121
> >>>NODE 5... 2 100
> >>>NODE 6... 2 81
> >>>
> >>>FOR NOW:
> >>>want a line to connect NODE 1 to NODE 2
> >>>want a line to connect NODE 1 to NODE 3
> >>>want a line to connect NODE 2 to NODE 4
> >>>want a line to connect NODE 2 to NODE 5
> >>>want a line to connect NODE 3 to NODE 5
> >>>want a line to connect NODE 3 to NODE 6
> >>>
> >>>Suggestions would be greatly appreciated.
> >>>
> >>>Luke
> >>
> >>
> >>
>
>

The 'nodal coordinates' would use the same frame of reference as the
original data. One possible algorithm would take the original data and
rewrite them in the format suggested by Jon so that a single series
would do the job.

Another algorithm would be to rewrite the data to be the eqivalent of a
depth-first search (to borrow a term from Computer Science). The
result would be:
0 100
1 110
2 121
1 110
2 100
1 110
0 100
1 90
2 100
1 90
2 81

Use the above for a single series XY Scatter chart with connecting
lines.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <[email protected]>, "=?
Utf-8?B?THVrZSBTcGFyIFVUQUZvcnRXb3J0aA==?=" <Luke Spar UTAFortWorth
@discussions.microsoft.com> says...
> I can sure try the second suggestion.
>
> I like the VBA suggestion a lot. But if the nodal cooridnates are in
> "points" then I am stumped---I do not know how to find them. The idea of
> "points" versus "XY coordinates " has me crazy".
>
> Thanks a lot, I will try the suggestion tomorrow.
>
>
>
>
> "Jon Peltier" wrote:
>
> > You could probably write an algorithm in VBA that would generate the nodal
> > coordinates, then dump them into cell positions which will produce the appropriate
> > line segments. You could even have a pair of columns like this, with skipped lines,
> > to build the lattice from a single series.
> >
> > 0 100
> > 1 110
> >
> > 0 100
> > 1 90
> >
> > 1 110
> > 2 121
> >
> > 1 110
> > 2 100
> >
> > 1 90
> > 2 100
> >
> > 1 90
> > 2 81
> >
> > etc. Select the range, blank rows included, and draw an XY Scatter chart
> >
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Peltier Technical Services
> > Tutorials and Custom Solutions
> > http://PeltierTech.com/
> > _______
> >
> >
> > Luke Spar UTAFortWorth wrote:
> >
> > > In the simpest case:
> > >
> > > 1)All nodes are governed by analytics that specify the value of an "up move"
> > > and the tha value of a "down move".... for example
> > > UP = 1.1
> > > Down = 0.909
> > >
> > > 2) In all cases the starting value is given
> > >
> > > 3) 100 * (Up=1.10) gives 110
> > > 100* (Down = 0.909) give `90
> > > 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> > > 110*(Down =0.909) gives ~100
> > > 90* (UP=1.1) gives ~ 100
> > > 90* (DOWN = 0.909) gives ~ 81
> > >
> > > The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
> > >
> > >
> > > The UP and Down parameters can vary, by period, in more complex situations.
> > > Thus the tree is "not recombining"
> > >
> > > Luke
> > >
> > > "Barb Reinhardt" wrote:
> > >
> > >
> > >>I suspect you'll need to have a different data series for each line. How
> > >>do you determine which nodes are connected to one another?
> > >>
> > >>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> > >>wrote in message news:[email protected]...
> > >>
> > >>>Would like to use XL to create a connected graph ( finance calls it a
> > >>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> > >>>
> > >>>XY scatter plots points OK. Need to connect points (nodes)--each node
> > >>
> > >>except
> > >>
> > >>>for t=T has two lines coming out of it.
> > >>>A 3-peiod example is shown below. Eventually need to generalilze.
> > >>>
> > >>>Data is
> > >>>reference T Value
> > >>>NOde 1... 0 100
> > >>>NODE 2... 1 110
> > >>>NODE 3... 1 90
> > >>>NODE 4... 2 121
> > >>>NODE 5... 2 100
> > >>>NODE 6... 2 81
> > >>>
> > >>>FOR NOW:
> > >>>want a line to connect NODE 1 to NODE 2
> > >>>want a line to connect NODE 1 to NODE 3
> > >>>want a line to connect NODE 2 to NODE 4
> > >>>want a line to connect NODE 2 to NODE 5
> > >>>want a line to connect NODE 3 to NODE 5
> > >>>want a line to connect NODE 3 to NODE 6
> > >>>
> > >>>Suggestions would be greatly appreciated.
> > >>>
> > >>>Luke
> > >>
> > >>
> > >>
> >
> >
>

Thanks

I will try this ASAp.

My application of John's approach resulted in a correct scatter plot BUT
when I selected a "line chart" the chart repeated the "X" axis -- each pair
of nodes was connected but BUT the x-axis was " 01 01 12 12 12 12" so
the nodal pairs never connected.


Luke
"Tushar Mehta" wrote:

> The 'nodal coordinates' would use the same frame of reference as the
> original data. One possible algorithm would take the original data and
> rewrite them in the format suggested by Jon so that a single series
> would do the job.
>
> Another algorithm would be to rewrite the data to be the eqivalent of a
> depth-first search (to borrow a term from Computer Science). The
> result would be:
> 0 100
> 1 110
> 2 121
> 1 110
> 2 100
> 1 110
> 0 100
> 1 90
> 2 100
> 1 90
> 2 81
>
> Use the above for a single series XY Scatter chart with connecting
> lines.
>
> --
> Regards,
>
> Tushar Mehta
> www.tushar-mehta.com
> Excel, PowerPoint, and VBA add-ins, tutorials
> Custom MS Office productivity solutions
>
> In article <[email protected]>, "=?
> Utf-8?B?THVrZSBTcGFyIFVUQUZvcnRXb3J0aA==?=" <Luke Spar UTAFortWorth
> @discussions.microsoft.com> says...
> > I can sure try the second suggestion.
> >
> > I like the VBA suggestion a lot. But if the nodal cooridnates are in
> > "points" then I am stumped---I do not know how to find them. The idea of
> > "points" versus "XY coordinates " has me crazy".
> >
> > Thanks a lot, I will try the suggestion tomorrow.
> >
> >
> >
> >
> > "Jon Peltier" wrote:
> >
> > > You could probably write an algorithm in VBA that would generate the nodal
> > > coordinates, then dump them into cell positions which will produce the appropriate
> > > line segments. You could even have a pair of columns like this, with skipped lines,
> > > to build the lattice from a single series.
> > >
> > > 0 100
> > > 1 110
> > >
> > > 0 100
> > > 1 90
> > >
> > > 1 110
> > > 2 121
> > >
> > > 1 110
> > > 2 100
> > >
> > > 1 90
> > > 2 100
> > >
> > > 1 90
> > > 2 81
> > >
> > > etc. Select the range, blank rows included, and draw an XY Scatter chart
> > >
> > > - Jon
> > > -------
> > > Jon Peltier, Microsoft Excel MVP
> > > Peltier Technical Services
> > > Tutorials and Custom Solutions
> > > http://PeltierTech.com/
> > > _______
> > >
> > >
> > > Luke Spar UTAFortWorth wrote:
> > >
> > > > In the simpest case:
> > > >
> > > > 1)All nodes are governed by analytics that specify the value of an "up move"
> > > > and the tha value of a "down move".... for example
> > > > UP = 1.1
> > > > Down = 0.909
> > > >
> > > > 2) In all cases the starting value is given
> > > >
> > > > 3) 100 * (Up=1.10) gives 110
> > > > 100* (Down = 0.909) give `90
> > > > 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> > > > 110*(Down =0.909) gives ~100
> > > > 90* (UP=1.1) gives ~ 100
> > > > 90* (DOWN = 0.909) gives ~ 81
> > > >
> > > > The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
> > > >
> > > >
> > > > The UP and Down parameters can vary, by period, in more complex situations.
> > > > Thus the tree is "not recombining"
> > > >
> > > > Luke
> > > >
> > > > "Barb Reinhardt" wrote:
> > > >
> > > >
> > > >>I suspect you'll need to have a different data series for each line. How
> > > >>do you determine which nodes are connected to one another?
> > > >>
> > > >>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> > > >>wrote in message news:[email protected]...
> > > >>
> > > >>>Would like to use XL to create a connected graph ( finance calls it a
> > > >>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> > > >>>
> > > >>>XY scatter plots points OK. Need to connect points (nodes)--each node
> > > >>
> > > >>except
> > > >>
> > > >>>for t=T has two lines coming out of it.
> > > >>>A 3-peiod example is shown below. Eventually need to generalilze.
> > > >>>
> > > >>>Data is
> > > >>>reference T Value
> > > >>>NOde 1... 0 100
> > > >>>NODE 2... 1 110
> > > >>>NODE 3... 1 90
> > > >>>NODE 4... 2 121
> > > >>>NODE 5... 2 100
> > > >>>NODE 6... 2 81
> > > >>>
> > > >>>FOR NOW:
> > > >>>want a line to connect NODE 1 to NODE 2
> > > >>>want a line to connect NODE 1 to NODE 3
> > > >>>want a line to connect NODE 2 to NODE 4
> > > >>>want a line to connect NODE 2 to NODE 5
> > > >>>want a line to connect NODE 3 to NODE 5
> > > >>>want a line to connect NODE 3 to NODE 6
> > > >>>
> > > >>>Suggestions would be greatly appreciated.
> > > >>>
> > > >>>Luke
> > > >>
> > > >>
> > > >>
> > >
> > >
> >
>

John

Your approach created a perfect XY scatter using a single series... I
selected "line" and obtained a good pairwise line each pair of nodes were
connected. However the X axis repeated -- my axis : 01 01 12 12 12 12

As a resuly my nodes, pairwise connected, were not connected --oould look a
christmas tree on its side.

*121
/
110 *
/ \
100 * * 100
\ /
90 *
\
* 81


Thanks

Luke






"Jon Peltier" wrote:

> You could probably write an algorithm in VBA that would generate the nodal
> coordinates, then dump them into cell positions which will produce the appropriate
> line segments. You could even have a pair of columns like this, with skipped lines,
> to build the lattice from a single series.
>
> 0 100
> 1 110
>
> 0 100
> 1 90
>
> 1 110
> 2 121
>
> 1 110
> 2 100
>
> 1 90
> 2 100
>
> 1 90
> 2 81
>
> etc. Select the range, blank rows included, and draw an XY Scatter chart
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
>
> Luke Spar UTAFortWorth wrote:
>
> > In the simpest case:
> >
> > 1)All nodes are governed by analytics that specify the value of an "up move"
> > and the tha value of a "down move".... for example
> > UP = 1.1
> > Down = 0.909
> >
> > 2) In all cases the starting value is given
> >
> > 3) 100 * (Up=1.10) gives 110
> > 100* (Down = 0.909) give `90
> > 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> > 110*(Down =0.909) gives ~100
> > 90* (UP=1.1) gives ~ 100
> > 90* (DOWN = 0.909) gives ~ 81
> >
> > The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
> >
> >
> > The UP and Down parameters can vary, by period, in more complex situations.
> > Thus the tree is "not recombining"
> >
> > Luke
> >
> > "Barb Reinhardt" wrote:
> >
> >
> >>I suspect you'll need to have a different data series for each line. How
> >>do you determine which nodes are connected to one another?
> >>
> >>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> >>wrote in message news:[email protected]...
> >>
> >>>Would like to use XL to create a connected graph ( finance calls it a
> >>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> >>>
> >>>XY scatter plots points OK. Need to connect points (nodes)--each node
> >>
> >>except
> >>
> >>>for t=T has two lines coming out of it.
> >>>A 3-peiod example is shown below. Eventually need to generalilze.
> >>>
> >>>Data is
> >>>reference T Value
> >>>NOde 1... 0 100
> >>>NODE 2... 1 110
> >>>NODE 3... 1 90
> >>>NODE 4... 2 121
> >>>NODE 5... 2 100
> >>>NODE 6... 2 81
> >>>
> >>>FOR NOW:
> >>>want a line to connect NODE 1 to NODE 2
> >>>want a line to connect NODE 1 to NODE 3
> >>>want a line to connect NODE 2 to NODE 4
> >>>want a line to connect NODE 2 to NODE 5
> >>>want a line to connect NODE 3 to NODE 5
> >>>want a line to connect NODE 3 to NODE 6
> >>>
> >>>Suggestions would be greatly appreciated.
> >>>
> >>>Luke
> >>
> >>
> >>
>
>

Luke -

No, you must create a Scatter chart, with the 'lines and no markers' sub type. This
looks like a line chart but behaves like a scatter chart.

So many people have trouble with line and scatter charts. I've written some web
pages which might help clarify the differences:

Scatter Chart or Line Chart?
http://pubs.logicalexpressions.com/P...cle.asp?ID=190

X Axis: Category or Value?
http://peltiertech.com/Excel/ChartsH...ValueAxis.html

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

Luke Spar UTAFortWorth wrote:

> Thanks
>
> I will try this ASAp.
>
> My application of John's approach resulted in a correct scatter plot BUT
> when I selected a "line chart" the chart repeated the "X" axis -- each pair
> of nodes was connected but BUT the x-axis was " 01 01 12 12 12 12" so
> the nodal pairs never connected.
>
>
> Luke
> "Tushar Mehta" wrote:
>
>
>>The 'nodal coordinates' would use the same frame of reference as the
>>original data. One possible algorithm would take the original data and
>>rewrite them in the format suggested by Jon so that a single series
>>would do the job.
>>
>>Another algorithm would be to rewrite the data to be the eqivalent of a
>>depth-first search (to borrow a term from Computer Science). The
>>result would be:
>>0 100
>>1 110
>>2 121
>>1 110
>>2 100
>>1 110
>>0 100
>>1 90
>>2 100
>>1 90
>>2 81
>>
>>Use the above for a single series XY Scatter chart with connecting
>>lines.
>>
>>--
>>Regards,
>>
>>Tushar Mehta
>>www.tushar-mehta.com
>>Excel, PowerPoint, and VBA add-ins, tutorials
>>Custom MS Office productivity solutions
>>
>>In article <[email protected]>, "=?
>>Utf-8?B?THVrZSBTcGFyIFVUQUZvcnRXb3J0aA==?=" <Luke Spar UTAFortWorth
>>@discussions.microsoft.com> says...
>>
>>>I can sure try the second suggestion.
>>>
>>>I like the VBA suggestion a lot. But if the nodal cooridnates are in
>>>"points" then I am stumped---I do not know how to find them. The idea of
>>>"points" versus "XY coordinates " has me crazy".
>>>
>>>Thanks a lot, I will try the suggestion tomorrow.
>>>
>>>
>>>
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>>You could probably write an algorithm in VBA that would generate the nodal
>>>>coordinates, then dump them into cell positions which will produce the appropriate
>>>>line segments. You could even have a pair of columns like this, with skipped lines,
>>>>to build the lattice from a single series.
>>>>
>>>>0 100
>>>>1 110
>>>>
>>>>0 100
>>>>1 90
>>>>
>>>>1 110
>>>>2 121
>>>>
>>>>1 110
>>>>2 100
>>>>
>>>>1 90
>>>>2 100
>>>>
>>>>1 90
>>>>2 81
>>>>
>>>>etc. Select the range, blank rows included, and draw an XY Scatter chart
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>
>>>>Luke Spar UTAFortWorth wrote:
>>>>
>>>>
>>>>>In the simpest case:
>>>>>
>>>>>1)All nodes are governed by analytics that specify the value of an "up move"
>>>>>and the tha value of a "down move".... for example
>>>>>UP = 1.1
>>>>>Down = 0.909
>>>>>
>>>>>2) In all cases the starting value is given
>>>>>
>>>>>3) 100 * (Up=1.10) gives 110
>>>>> 100* (Down = 0.909) give `90
>>>>> 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
>>>>> 110*(Down =0.909) gives ~100
>>>>> 90* (UP=1.1) gives ~ 100
>>>>> 90* (DOWN = 0.909) gives ~ 81
>>>>>
>>>>>The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
>>>>>
>>>>>
>>>>>The UP and Down parameters can vary, by period, in more complex situations.
>>>>>Thus the tree is "not recombining"
>>>>>
>>>>>Luke
>>>>>
>>>>>"Barb Reinhardt" wrote:
>>>>>
>>>>>
>>>>>
>>>>>>I suspect you'll need to have a different data series for each line. How
>>>>>>do you determine which nodes are connected to one another?
>>>>>>
>>>>>>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
>>>>>>wrote in message news:[email protected]...
>>>>>>
>>>>>>
>>>>>>>Would like to use XL to create a connected graph ( finance calls it a
>>>>>>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
>>>>>>>
>>>>>>>XY scatter plots points OK. Need to connect points (nodes)--each node
>>>>>>
>>>>>>except
>>>>>>
>>>>>>
>>>>>>>for t=T has two lines coming out of it.
>>>>>>>A 3-peiod example is shown below. Eventually need to generalilze.
>>>>>>>
>>>>>>>Data is
>>>>>>>reference T Value
>>>>>>>NOde 1... 0 100
>>>>>>>NODE 2... 1 110
>>>>>>>NODE 3... 1 90
>>>>>>>NODE 4... 2 121
>>>>>>>NODE 5... 2 100
>>>>>>>NODE 6... 2 81
>>>>>>>
>>>>>>>FOR NOW:
>>>>>>>want a line to connect NODE 1 to NODE 2
>>>>>>>want a line to connect NODE 1 to NODE 3
>>>>>>>want a line to connect NODE 2 to NODE 4
>>>>>>>want a line to connect NODE 2 to NODE 5
>>>>>>>want a line to connect NODE 3 to NODE 5
>>>>>>>want a line to connect NODE 3 to NODE 6
>>>>>>>
>>>>>>>Suggestions would be greatly appreciated.
>>>>>>>
>>>>>>>Luke
>>>>>>
>>>>>>
>>>>>>
>>>>

Luke -

Yes, see my answer to your other post. You need a scatter type series, with the
lines and no markers subtype.

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

Luke Spar UTAFortWorth wrote:

> John
>
> Your approach created a perfect XY scatter using a single series... I
> selected "line" and obtained a good pairwise line each pair of nodes were
> connected. However the X axis repeated -- my axis : 01 01 12 12 12 12
>
> As a resuly my nodes, pairwise connected, were not connected --oould look a
> christmas tree on its side.
>
> *121
> /
> 110 *
> / \
> 100 * * 100
> \ /
> 90 *
> \
> * 81
>
>
> Thanks
>
> Luke
>
>
>
>
>
>
> "Jon Peltier" wrote:
>
>
>>You could probably write an algorithm in VBA that would generate the nodal
>>coordinates, then dump them into cell positions which will produce the appropriate
>>line segments. You could even have a pair of columns like this, with skipped lines,
>>to build the lattice from a single series.
>>
>>0 100
>>1 110
>>
>>0 100
>>1 90
>>
>>1 110
>>2 121
>>
>>1 110
>>2 100
>>
>>1 90
>>2 100
>>
>>1 90
>>2 81
>>
>>etc. Select the range, blank rows included, and draw an XY Scatter chart
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>
>>Luke Spar UTAFortWorth wrote:
>>
>>
>>>In the simpest case:
>>>
>>>1)All nodes are governed by analytics that specify the value of an "up move"
>>>and the tha value of a "down move".... for example
>>>UP = 1.1
>>>Down = 0.909
>>>
>>>2) In all cases the starting value is given
>>>
>>>3) 100 * (Up=1.10) gives 110
>>> 100* (Down = 0.909) give `90
>>> 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
>>> 110*(Down =0.909) gives ~100
>>> 90* (UP=1.1) gives ~ 100
>>> 90* (DOWN = 0.909) gives ~ 81
>>>
>>>The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
>>>
>>>
>>>The UP and Down parameters can vary, by period, in more complex situations.
>>>Thus the tree is "not recombining"
>>>
>>>Luke
>>>
>>>"Barb Reinhardt" wrote:
>>>
>>>
>>>
>>>>I suspect you'll need to have a different data series for each line. How
>>>>do you determine which nodes are connected to one another?
>>>>
>>>>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
>>>>wrote in message news:[email protected]...
>>>>
>>>>
>>>>>Would like to use XL to create a connected graph ( finance calls it a
>>>>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
>>>>>
>>>>>XY scatter plots points OK. Need to connect points (nodes)--each node
>>>>
>>>>except
>>>>
>>>>
>>>>>for t=T has two lines coming out of it.
>>>>>A 3-peiod example is shown below. Eventually need to generalilze.
>>>>>
>>>>>Data is
>>>>>reference T Value
>>>>>NOde 1... 0 100
>>>>>NODE 2... 1 110
>>>>>NODE 3... 1 90
>>>>>NODE 4... 2 121
>>>>>NODE 5... 2 100
>>>>>NODE 6... 2 81
>>>>>
>>>>>FOR NOW:
>>>>>want a line to connect NODE 1 to NODE 2
>>>>>want a line to connect NODE 1 to NODE 3
>>>>>want a line to connect NODE 2 to NODE 4
>>>>>want a line to connect NODE 2 to NODE 5
>>>>>want a line to connect NODE 3 to NODE 5
>>>>>want a line to connect NODE 3 to NODE 6
>>>>>
>>>>>Suggestions would be greatly appreciated.
>>>>>
>>>>>Luke
>>>>
>>>>
>>>>
>>

Thanks -- I now have a binomial lattice that connects nodes with lines and
looks like an christmas tree on its side.

I will look at your tutorial. MAybe if you get a chance you can desribe the
value of the "blanks" in the data series. If it is in your tutorials then
don't bother.

Luke

"Jon Peltier" wrote:

> Luke -
>
> Yes, see my answer to your other post. You need a scatter type series, with the
> lines and no markers subtype.
>
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Peltier Technical Services
> Tutorials and Custom Solutions
> http://PeltierTech.com/
> _______
>
> Luke Spar UTAFortWorth wrote:
>
> > John
> >
> > Your approach created a perfect XY scatter using a single series... I
> > selected "line" and obtained a good pairwise line each pair of nodes were
> > connected. However the X axis repeated -- my axis : 01 01 12 12 12 12
> >
> > As a resuly my nodes, pairwise connected, were not connected --oould look a
> > christmas tree on its side.
> >
> > *121
> > /
> > 110 *
> > / \
> > 100 * * 100
> > \ /
> > 90 *
> > \
> > * 81
> >
> >
> > Thanks
> >
> > Luke
> >
> >
> >
> >
> >
> >
> > "Jon Peltier" wrote:
> >
> >
> >>You could probably write an algorithm in VBA that would generate the nodal
> >>coordinates, then dump them into cell positions which will produce the appropriate
> >>line segments. You could even have a pair of columns like this, with skipped lines,
> >>to build the lattice from a single series.
> >>
> >>0 100
> >>1 110
> >>
> >>0 100
> >>1 90
> >>
> >>1 110
> >>2 121
> >>
> >>1 110
> >>2 100
> >>
> >>1 90
> >>2 100
> >>
> >>1 90
> >>2 81
> >>
> >>etc. Select the range, blank rows included, and draw an XY Scatter chart
> >>
> >>- Jon
> >>-------
> >>Jon Peltier, Microsoft Excel MVP
> >>Peltier Technical Services
> >>Tutorials and Custom Solutions
> >>http://PeltierTech.com/
> >>_______
> >>
> >>
> >>Luke Spar UTAFortWorth wrote:
> >>
> >>
> >>>In the simpest case:
> >>>
> >>>1)All nodes are governed by analytics that specify the value of an "up move"
> >>>and the tha value of a "down move".... for example
> >>>UP = 1.1
> >>>Down = 0.909
> >>>
> >>>2) In all cases the starting value is given
> >>>
> >>>3) 100 * (Up=1.10) gives 110
> >>> 100* (Down = 0.909) give `90
> >>> 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
> >>> 110*(Down =0.909) gives ~100
> >>> 90* (UP=1.1) gives ~ 100
> >>> 90* (DOWN = 0.909) gives ~ 81
> >>>
> >>>The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
> >>>
> >>>
> >>>The UP and Down parameters can vary, by period, in more complex situations.
> >>>Thus the tree is "not recombining"
> >>>
> >>>Luke
> >>>
> >>>"Barb Reinhardt" wrote:
> >>>
> >>>
> >>>
> >>>>I suspect you'll need to have a different data series for each line. How
> >>>>do you determine which nodes are connected to one another?
> >>>>
> >>>>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
> >>>>wrote in message news:[email protected]...
> >>>>
> >>>>
> >>>>>Would like to use XL to create a connected graph ( finance calls it a
> >>>>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
> >>>>>
> >>>>>XY scatter plots points OK. Need to connect points (nodes)--each node
> >>>>
> >>>>except
> >>>>
> >>>>
> >>>>>for t=T has two lines coming out of it.
> >>>>>A 3-peiod example is shown below. Eventually need to generalilze.
> >>>>>
> >>>>>Data is
> >>>>>reference T Value
> >>>>>NOde 1... 0 100
> >>>>>NODE 2... 1 110
> >>>>>NODE 3... 1 90
> >>>>>NODE 4... 2 121
> >>>>>NODE 5... 2 100
> >>>>>NODE 6... 2 81
> >>>>>
> >>>>>FOR NOW:
> >>>>>want a line to connect NODE 1 to NODE 2
> >>>>>want a line to connect NODE 1 to NODE 3
> >>>>>want a line to connect NODE 2 to NODE 4
> >>>>>want a line to connect NODE 2 to NODE 5
> >>>>>want a line to connect NODE 3 to NODE 5
> >>>>>want a line to connect NODE 3 to NODE 6
> >>>>>
> >>>>>Suggestions would be greatly appreciated.
> >>>>>
> >>>>>Luke
> >>>>
> >>>>
> >>>>
> >>
>
>

Luke -

The blanks are simply there to provide a gap in the connecting lines. If
the lines appeared, they would zigzag back across your lattice.

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

Luke Spar UTAFortWorth wrote:

> Thanks -- I now have a binomial lattice that connects nodes with lines and
> looks like an christmas tree on its side.
>
> I will look at your tutorial. MAybe if you get a chance you can desribe the
> value of the "blanks" in the data series. If it is in your tutorials then
> don't bother.
>
> Luke
>
> "Jon Peltier" wrote:
>
>
>>Luke -
>>
>>Yes, see my answer to your other post. You need a scatter type series, with the
>>lines and no markers subtype.
>>
>>- Jon
>>-------
>>Jon Peltier, Microsoft Excel MVP
>>Peltier Technical Services
>>Tutorials and Custom Solutions
>>http://PeltierTech.com/
>>_______
>>
>>Luke Spar UTAFortWorth wrote:
>>
>>
>>>John
>>>
>>>Your approach created a perfect XY scatter using a single series... I
>>>selected "line" and obtained a good pairwise line each pair of nodes were
>>>connected. However the X axis repeated -- my axis : 01 01 12 12 12 12
>>>
>>>As a resuly my nodes, pairwise connected, were not connected --oould look a
>>>christmas tree on its side.
>>>
>>> *121
>>> /
>>> 110 *
>>> / \
>>> 100 * * 100
>>> \ /
>>> 90 *
>>> \
>>> * 81
>>>
>>>
>>>Thanks
>>>
>>>Luke
>>>
>>>
>>>
>>>
>>>
>>>
>>>"Jon Peltier" wrote:
>>>
>>>
>>>
>>>>You could probably write an algorithm in VBA that would generate the nodal
>>>>coordinates, then dump them into cell positions which will produce the appropriate
>>>>line segments. You could even have a pair of columns like this, with skipped lines,
>>>>to build the lattice from a single series.
>>>>
>>>>0 100
>>>>1 110
>>>>
>>>>0 100
>>>>1 90
>>>>
>>>>1 110
>>>>2 121
>>>>
>>>>1 110
>>>>2 100
>>>>
>>>>1 90
>>>>2 100
>>>>
>>>>1 90
>>>>2 81
>>>>
>>>>etc. Select the range, blank rows included, and draw an XY Scatter chart
>>>>
>>>>- Jon
>>>>-------
>>>>Jon Peltier, Microsoft Excel MVP
>>>>Peltier Technical Services
>>>>Tutorials and Custom Solutions
>>>>http://PeltierTech.com/
>>>>_______
>>>>
>>>>
>>>>Luke Spar UTAFortWorth wrote:
>>>>
>>>>
>>>>
>>>>>In the simpest case:
>>>>>
>>>>>1)All nodes are governed by analytics that specify the value of an "up move"
>>>>>and the tha value of a "down move".... for example
>>>>>UP = 1.1
>>>>>Down = 0.909
>>>>>
>>>>>2) In all cases the starting value is given
>>>>>
>>>>>3) 100 * (Up=1.10) gives 110
>>>>> 100* (Down = 0.909) give `90
>>>>> 110 * (Up = 1.10) gives 121 ( note 100 * UP*UP = 121)
>>>>> 110*(Down =0.909) gives ~100
>>>>> 90* (UP=1.1) gives ~ 100
>>>>> 90* (DOWN = 0.909) gives ~ 81
>>>>>
>>>>>The example above is refereed to as "Recombining" since 100*UP*DOWN yield 100.
>>>>>
>>>>>
>>>>>The UP and Down parameters can vary, by period, in more complex situations.
>>>>>Thus the tree is "not recombining"
>>>>>
>>>>>Luke
>>>>>
>>>>>"Barb Reinhardt" wrote:
>>>>>
>>>>>
>>>>>
>>>>>
>>>>>>I suspect you'll need to have a different data series for each line. How
>>>>>>do you determine which nodes are connected to one another?
>>>>>>
>>>>>>"Luke Spar UTAFortWorth" <Luke Spar [email protected]>
>>>>>>wrote in message news:[email protected]...
>>>>>>
>>>>>>
>>>>>>
>>>>>>>Would like to use XL to create a connected graph ( finance calls it a
>>>>>>>binomial lattice). X axis iis the time period t=1,2,3,...T, Y is value.
>>>>>>>
>>>>>>>XY scatter plots points OK. Need to connect points (nodes)--each node
>>>>>>
>>>>>>except
>>>>>>
>>>>>>
>>>>>>
>>>>>>>for t=T has two lines coming out of it.
>>>>>>>A 3-peiod example is shown below. Eventually need to generalilze.
>>>>>>>
>>>>>>>Data is
>>>>>>>reference T Value
>>>>>>>NOde 1... 0 100
>>>>>>>NODE 2... 1 110
>>>>>>>NODE 3... 1 90
>>>>>>>NODE 4... 2 121
>>>>>>>NODE 5... 2 100
>>>>>>>NODE 6... 2 81
>>>>>>>
>>>>>>>FOR NOW:
>>>>>>>want a line to connect NODE 1 to NODE 2
>>>>>>>want a line to connect NODE 1 to NODE 3
>>>>>>>want a line to connect NODE 2 to NODE 4
>>>>>>>want a line to connect NODE 2 to NODE 5
>>>>>>>want a line to connect NODE 3 to NODE 5
>>>>>>>want a line to connect NODE 3 to NODE 6
>>>>>>>
>>>>>>>Suggestions would be greatly appreciated.
>>>>>>>
>>>>>>>Luke
>>>>>>
>>>>>>
>>>>>>
>>