Adding X-Intercept Line

[rickree]

Hello,

I've been trying to find a solution. I have two lines based on a
scatter plot.
The first line is cumulative cash flow over a period of time. The
line is non-linear.
The second line represents the number of cumulative items sold. This
line is also non-linear.

My x-axis reprsents the year.
My primary y-axis is dollars
My secondary y-axis is number of items sold

i'd like to graph the x-inercept line for the cumulative cash flow,
and find out what year it is. Also find out where that line
intersects the qty line. Is this possible? Any ideas?

 

[Shane Devenshire]

Hi,

The X-intercept may be meaningless depending on the equations of your lines.
Consider a parabolic line which never intercepts the x or y axis.

Also, I believe that the x-intercept for a straight line is a point not a
line, and that might be the case for many types of equations. In that case
it is not clear what you mean by the intersection of the qty line and the
x-intercept line?

You can solve for the intersection of two non linear lines but there could
be no solutions, one or and infinite number of solutions.

You might want to post the equations of the cumulative cash flow and the
cumulative items sold.

 

[rickree]

Hello Shane,

thanks for the reply. I don't have an equation for any of the lines.
It's a scatter plot showing on the y axis the total cumulative cash
amount and on the x-axis the years. Every graph will cross the X axis
at some point, as my cash flow will always be negative to start and
then positive in some amount of years.

For example:
Y-Values
-10
-8
7
20
35

X-Values
2009
2010
2011
2012
2013

I'd like to know at what point does the y-values cross the x-values
when you make a line from the data series. Is this possible?

 

[Frank]

There are several ways to do this. You want to find the X-Value when
the Y-Value is zero.
One way would be to add a trend line to your graph. There are several
sophicated ones to choose from. Then use "Goal Seek" to find the X-
Value which makes the Y-Value zero.

Another way would be to reverse the graph and use the Y-Values as the
abcissa and the X-values as the ordinate. Now observe wher the line
crosses the X-axis. Viola!

A third way would be to interpolate the data for Y = 0. There are
several programs to do that.

I tried your data by fitting it with a Cubic Spline function and found
at Y = 0, X = 2011.36.

 

[Lori]

Assume your data is in A2:B6 and D2=0 is the Y lookup value.

2009 -10
2010 -8
2011 7
2012 20
2013 35

1. To base the estimate on an XY chart with straight lines try:

=TREND(OFFSET(A2:A6,MATCH(D2,B2:B6),0,2),
OFFSET(B2:B6,MATCH(D2,B2:B6),0,2),D2)

which gives 2010.462 or 18 June, 2010.


2. To base the estimate on smooth lines joining points try instead:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
OFFSET(B2:B6,MATCH(D2,B2:B6)-2,0,4)-D2
)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
OFFSET(A2:A6,MATCH(D2,B2:B6)-2,0,4)))/2

which gives 2010.570 or 27 July, 2010.

If the intercept is in the first or last interval you'd need to
extrapolate at each end by including (2008,12) and (2004,50).


3. You can convert decimals to dates, for a value in E2, using

=DATE(E2,1,1)+MOD(E2,1)*(DATE(E2+1,1,1)-DATE(E2,1,1))

HTH. Lori

 

[Lori]

Sorry, i missed out a "-1" in the first formula:

=TREND(OFFSET(A2:A6,MATCH(D2,B2:B6)-1,0,2),
OFFSET(B2:B6,MATCH(D2,B2:B6),0,2)-1,D2)

so it should be 14 July 2010 which means they only differ by a week.

 

[Dana DeLouis]

Assume your data is in A2:B6 and D2=0 is the Y lookup value.

2009 -10
2010 -8
2011 7
2012 20
2013 35

1. To base the estimate on an XY chart with straight lines try:

=TREND(OFFSET(A2:A6,MATCH(D2,B2:B6),0,2),
OFFSET(B2:B6,MATCH(D2,B2:B6),0,2),D2)

which gives 2010.462 or 18 June, 2010.

Another option for a straight line might be:

= -INTERCEPT(data,Years) / SLOPE(data,Years)

(By setting y = 0 )

= = = = =
HTH
Dana DeLouis

 

[Lori]

= -INTERCEPT(data,Years) / SLOPE(data,Years)

Yes, that would be the result of a linear trendline which gives April 3,
2010. If you restrict the data to the interval of interest, your formula
agrees with the one i tried to post i.e. TREND({2010;2011},{-8;7},0)

The "smooth line" charting option is generally an exact match with the
method in this link: http://www.mvps.org/directx/articles/catmull/
It's easier to use goal seek for this but the IRR method is more direct
and seems to work in tests (although it did take some figuring out!)

 

[Dana DeLouis]

I tried your data by fitting it with a Cubic Spline function and found

at Y = 0, X = 2011.36.

Hi. I may be wrong, but on a Cubic Spline, the only parametric equation
that gave a value between 0 & 1 was the second interval (between 2010
and 2011)

-((297*x^3)/56) + (615*x^2)/56 + (261*x)/28 - 8


Solving for 0 gave an x value of:
0.576046363414434

The years are linear, so just 2010 + 0.576046363414434
= 2010.57604636341

This seems to visually check with the scatter chart of around 2010.6

= = = =
Dana DeLouis

 

[rickree]

There are several ways to do this.  You want to find the X-Value when
the Y-Value is zero.
One way would be to add a trend line to your graph.  There are several
sophicated ones to choose from.  Then use "Goal Seek" to find the X-
Value which makes the Y-Value zero.

Another way would be to reverse the graph and use the Y-Values as the
abcissa and the X-values as the ordinate. Now observe wher the line
crosses the X-axis.  Viola!

A third way would be to interpolate the data for Y = 0.  There are
several programs to do that.

I tried your data by fitting it with a Cubic Spline function and found
at Y = 0, X = 2011.36.




- Show quoted text -

I'm old fashion and I'm still running Excel 2003. Well that's becuase
my company doesn't want to upgrade yet.
how do I use the goal seek function?

Also, thanks to everyone that's replied. It's really been very
helpful.

Nick

 

[rickree]

Assume your data is in A2:B6 and D2=0 is the Y lookup value.

2009    -10
2010    -8
2011    7
2012    20
2013    35

1. To base the estimate on an XY chart with straight lines try:

=TREND(OFFSET(A2:A6,MATCH(D2,B2:B6),0,2),
OFFSET(B2:B6,MATCH(D2,B2:B6),0,2),D2)

which gives 2010.462 or 18 June, 2010.

2. To base the estimate on smooth lines joining points try instead:

=SUM((1+1/IRR(MMULT({0,0,2,0;0,1,0,-1;-1,4,-5,2;1,-3,3,-1},
OFFSET(B2:B6,MATCH(D2,B2:B6)-2,0,4)-D2
)))^-{0;1;2;3}*MMULT({0,2,0,0;-1,0,1,0;2,-5,4,-1;-1,3,-3,1},
OFFSET(A2:A6,MATCH(D2,B2:B6)-2,0,4)))/2

which gives 2010.570 or 27 July, 2010.

If the intercept is in the first or last interval you'd need to
extrapolate at each end by including (2008,12) and (2004,50).

3. You can convert decimals to dates, for a value in E2, using

=DATE(E2,1,1)+MOD(E2,1)*(DATE(E2+1,1,1)-DATE(E2,1,1))

HTH. Lori







- Show quoted text -

Lori,

Thanks for the insight. I'm working with function you suggested.
What if the data was in columns instead of rows? I'm confused as the
what the mmult function is doing. Can you explain?


thanks again,
nick

 

[Lori]

For data in the horizontal range D2:H3 and K3=0 try instead:

=TREND(OFFSET(D2:H2,,MATCH(K3,D3:H3)-1,,2),
OFFSET(D3:H3,,MATCH(K3,D3:H3)-1,,2),K3)

=SUM((1+1/IRR(MMULT(OFFSET(D3:H3,,MATCH(K3,D3:H3)-2,,4)-K3,
{0,0,-1,1;0,1,4,-3;2,0,-5,3;0,-1,2,-1}
)))^-{0,1,2,3}*MMULT(OFFSET(D2:H2,,MATCH(K3,D3:H3)-2,,4),
{0,-1,2,-1;2,0,-5,3;0,1,4,-3;0,0,-1,1}))/2

(MMULT is needed for the formula in the link i posted above.)
You should be able to use the same basic formulas to then
lookup the no. of items by switching the x and y ranges.

An example is shown in the attached file
http://www.mediafire.com/?sharekey=e17a075d3741a4938ef1259ff1b60e81ea9b75d0b42c06f9ce018c8114394287

 

[rickree]

THANKS!!!

For data in the horizontal range D2:H3 and K3=0 try instead:

=TREND(OFFSET(D2:H2,,MATCH(K3,D3:H3)-1,,2),
OFFSET(D3:H3,,MATCH(K3,D3:H3)-1,,2),K3)

=SUM((1+1/IRR(MMULT(OFFSET(D3:H3,,MATCH(K3,D3:H3)-2,,4)-K3,
{0,0,-1,1;0,1,4,-3;2,0,-5,3;0,-1,2,-1}
)))^-{0,1,2,3}*MMULT(OFFSET(D2:H2,,MATCH(K3,D3:H3)-2,,4),
{0,-1,2,-1;2,0,-5,3;0,1,4,-3;0,0,-1,1}))/2

(MMULT is needed for the formula in the link i posted above.)
You should be able to use the same basic formulas to then
lookup the no. of items by switching the x and y ranges.

An example is shown in the attached file:http://www.mediafire.com/?sharekey=e17a075d3741a4938ef1259ff1b60e81ea...






- Show quoted text -