Add vertical line at intersection of 2 curves

[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

 

[Andy Pope]

Hi,

This example should help you get the intersecting points.
http://www.andypope.info/charts/intersection.htm

You could then plot this points and maybe use the error bars to give
your vertical lines.

Cheers
Andy

 

[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

 

[Guest]

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