Add vertical line at intersection of 2 curves

  • Thread starter Thread starter LeAnne
  • Start date Start date
L

LeAnne

Hi folks,

Picture, if you will, an Excel graph with 2 bell-curves plotted on
it...one curve shows the freqency distribution of measurement values at
"good," or reference, sites, and the other shows the distribution of
values at "bad," or impaired, sites. At some point on the graph the
curves intersect (hopefully at the inflection points, assuming a
more-or-less normal distribution for both populations of sites). The
"curves" are actually XY scatterplots with smoothed lines and no
markers, using data sorted into uniform "bins" generated by Tools | Data
Analysis | Histogram. Now, I can add vertical lines to the graph to
show the median (50th %ile) of each distribution using the technique at
Kelly O'Day's website
(http://processtrends.com/pg_charts_vertical_line.htm), because I "know"
(i.e. can calculate) the median value for each pop. But what I would
*really* like to do is add a vertical line to the chart at the
intersection point for these 2 curves. I have 21 of these charts (7
measures, 3 biological regions) and prefer not to have to eyeball all
those intersection points, then hard-code in the values for each graph
to make the intersection line. Is this even possible? Or let me phrase
it another way...is it possible without VB? <crosses fingers>

Using Excel 97, Win XP.

tia,

LeAnne
 
Thanks for responding, Andy -

Yes, I did locate this example on your site before my original post. I
was desperately hoping there would be some way to accomplish this task
sans VB. I'm not at all code-savvy, so tweaking someone else's code
would have been beyond my capabilities. Fortunately for me, on closer
examination your examples include the needed procedures and seem
relatively straightforward. If I can get just the X-value of the
intersection point, I can figure out how to add the vertical line.

Much obliged,

LeAnne
 
Leanne:
Excel can find the intersection of the two bell curves within reasonable
accuracy - i.e. by adding a best fitting line through your points. Likely
some polynomial function will do with a good mathematical fit (i guess second
or third order polynomial might be already accurate enough). Select the data
series, add trendline and select "display equation on chart" to show you the
parameters. Repeat for the other series and now you have the two
mathematical equations for which you can solve the intersection point (this
could also be done with Excel too). The R-squared value gives you how well
the trendline fits the data (0 = no fit; 1 = perfect fit). Hope this helps
you a bit further.
Good luck, Henk
 
Back
Top