Intersect Line

[GoodTrouble]

Hello all,

I have been banging my head against the wall forever on this one...

I have a scatter plot X,Y graph in my spreadsheet. The X axis is dates, and
the Y, dollar figures (Value over time). The plotting is fine, however I have
a constant line (cost) which at some point the values will cross. I need to
get Excel to draw a vertical line, or at the very least tell me the figure at
which this happens.

I know of the "INTERSECT" and "LINEST" functions, however they are just
returning unusable decimals, I'm guessing due to having dates on one axis.

I am using Excel 2007 Please Help!
Thank You!

 

[Jon Peltier]

You're probably thinking of INTERCEPT and its sister function SLOPE. You're
also probably thinking that the chart will do these calculations for you,
but you're going to have to get your worksheet dirty.

In two cells, enter these formulas:

=SLOPE(Yrange,Xrange)
=INTERCEPT(Yrange,Xrange)

where Yrange and Xrange are the cell ranges containing your data. These can
be used in the well-known formula for a straight line, Y = mX + b, where m =
SLOPE and b = INTERCEPT. To find the X value at which your line crosses the
constant, invert the formula to X = (Y - b)/m, plug in the constant for Y
and the calculated linear parameters for m and b.

To convert a number to a date, simply apply a date format to the cell. The
whole number is how many days have elapsed since 1-Jan-1900, and the
fraction is how much of the day has elapsed since midnight.

- Jon

 

[GoodTrouble]

Thanks again Jon!!! This will work, I just have to decide what to do with
that information now, I'm not sure if it can plot that point on the graph or
what...

None the less, thank you again for your help!

 

[Jon Peltier]

Plot a point using the calculated X and the target Y as a new series. It
will cover the point of intersection. You could format it as a large circle
with no background color, then add a negative error bar using 100% of the Y
value to draw a line down to the X axis.

- Jon

 

[GoodTrouble]

Perfect! Thanks again!

Plot a point using the calculated X and the target Y as a new series. It
will cover the point of intersection. You could format it as a large circle
with no background color, then add a negative error bar using 100% of the Y
value to draw a line down to the X axis.

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