Add vertical line at intersection of 2 curves

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

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 wrote:
> 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, Microsoft MVP - Excel
http://www.andypope.info

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

Andy Pope wrote:
> 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 wrote:
>
>> 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
>
>

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

"LeAnne" wrote:

> 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
>
> Andy Pope wrote:
> > 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 wrote:
> >
> >> 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
> >
> >
>