intersection point of two normal distribution curves

[gauravsingh]

Hi, I am want to calculate the intersection point of two normal distribution curves.
Can someone help me how to get that intersection point in Excel. I am using 2010 version.

Thanks in advance
Gaurav

[MrShorty]

Its hard to give a specific response to a general question. In general terms, here's how I would approach the problem (note that most of this is generic math and not specific to Excel):

1) You have two curves - call one f(x) and the other g(x). At this point, other than calling them normal distribution curves, I don't know what they look like. Perhaps you are using or can set up the problem in a way to use the built in NORM.DIST function http://office.microsoft.com/en-us/ma...in=HA102929457
2) The point of intersection is where f(x)=g(x). Do a little algebra, and this becomes f(x)-g(x)=0. Now the problem looks like a root finding problem for the equation y=f(x)-g(x). Sometimes, this can be solved algebraically, and the resulting expression put into Excel. In other cases, numerical algorithms are required http://en.wikipedia.org/wiki/Root-finding_algorithm Newton's method is probably the favorite of these methods, and is already available in Excel via the Goal Seek/Solver utilities.

The basic approach to this kind of problem will look something like this (assuming a numerical root finding algorithm):

1) Determine what form you are going to use for f(x) and g(x). Put that into Excel so you have cells that calculate f(x) and g(x) for a given value of x.
2) Add another cell that takes the difference of f(x) and g(x) -- f(x)-g(x) at some initial guess for x
3) Call Solver, and tell it to set this cell to a value of 0 by changing x. If you've set up the spreadsheet well and given it a good initial guess for x, it should converge on the value for x at the intersection point. Then, to get y, you simply look at either of the f(x) or g(x) cells to see what y is at that x.