# Thread: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

1. ## Calculating Intersections in a Chart (Excel-Office 2000-SR1)

A user has the following questions he needs help with. The spreadsheet is attached.

1. How do I set up a spreadsheet in which the fan and system curve data can be entered, and the spreadsheet will automatically calculate the intersection point of the best fit curves for each of the data sets?

2. How do get the spreadsheet to calculate the CFM data given static pressure data values along the best fit curve generated by the Fan Curve data.

Thanks for any help someone may be able to give.

2. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

Is there a preferred form to the two equations?
How are you calculating the "best fit" curve for each data set? (really the same question)

A manual approach, given that there is NO known form to the equations and you are just using the smoothed line visually would be to:

(Caveat) This is one of the times that a dangerous aspect of Excel can be used - when you drag a point on a chart the original values are changed.

Make a new series, comprisi just one point. I put .015 in cell D17 and 24000 in E17 of your file. Select them, copy and activating the chart, PasteSpecial as new series with D17 as x-range and E17 as y-range. Select the single-point-series and click again so that the cursor becomes a 4-way cross indicating the single point has been selected. Then drag it to the place where you believe the intersection is. You may have to drag it horizontally in one move and vertically in another. Then the cells D17 and E17 will contain the new values, which I got as .138 and 22000 respectively.

I dont know how you would automate this process.

Another Alternative: use spline fitting to make the smooth line between points. (this is essentially what clicking the "smoothed line" does). You can create a new set of data with the spline fits. Then I suppose you could check each set of data for where the two y ranges are close together and do successive spline interpolations to find an intersection.

If you do know the form of the equations, then maybe there is some easier way.

good luck
Ruth Callcott

3. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

Hello B. Ridley,

There is a numeric method, called Regula Falsi, to find the intersection of two mathematical expressions. To apply the method, I first fitted both curves in your chart, with a second order polynomial (for one of the data series, I just omitted the zero point, because the curve was difficult to fit with this point included). Then I defined a User Defined function (which I called 'funkt') which is just the difference between both polynomials. Then I applied Regula Falsi to find the intersection in the interval [0, 0.3].
Hope this helps you automating your application. (By the way, there are other numeric methods to do this, but I just picked out the Regula Falsi method).

4. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

Thanks for the response Ruth. I'm the go between and trying to helping an engineer user find a solution to this. This is way over my head mathematically. He's out today and two days next week, but I'll pass along your questions and hopefully, you'll still be willing to help with any other questions involved in finding a solution.

Thanks again!!

5. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

HP:

Thanks for your response. Mathematically this is out of my league. I'm helping a user (engineer) to find an Excel solution. He wants to know a formula that will return the data to the cells automatically. So some type of function is needed to do that.

To the best of my knowledge, he wants to know how to create a formula that will give him x and y intersection, as well as a formula that will fill in the CFM field.

I'll pass along the info you sent and when he returns next Wednesday, I may need to post more communication.

6. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

This post comes with a caveat: I am learning this myself at the moment and I know just enough about this to make myself dangerous!

The LINEST function in Excel allows polynomial expressions and can be used to find the equations of curves. The fan curve in the provided spreadsheet fits a 5th order polynomial and the system curve fits a 4th order polynomial.

To use LINEST with polynomials you need to enter the formula in an row of cells and array-enter. eg for the "fan curve" from your spreadsheet you would highlight a row of 6 cells and array-enter the following formula:
LINEST(B7:B12:A7:A12^{1,2,3,4,5})
The resultant row vector gives the coefficients of the polynomial expression of the fan curve, in this case
(-1402655)x^5+(7577868)x^4+(-1243062)x^3+(-8135.102)x^2+(-17458.39633)x+(25702)
Once you have the equations for the curves you can find the desired fan curve data points and set up a solver scenario to find the curve intersection.

regards
Grant Cartledge

7. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

geecee,

Theoretically you are right. But, as you said, this approach might be dangerous: (I did not try it out on this data, so my worries are purely theoretical too)
1) higher order polynomials will always tend to fit the data, (one can prove that a (n-1)th order polynomial will exactly go through n data points). However, watch out, you might be overfitting the data, that is, the curve will perfectly fit the data but might not be useful for interpolations (not to mention extrapolation!). Always plot the curve for visual inspection! Of course, if automation is necessary, this is not ideal. It would be better if some physical theory might offer a mathematical model to relate the x and y data.
2) the solver method might not lead to a satisfactory result. In fact, the goal seek method could be used here too, but might not lead to the closest solution for the intersection. 'Regula Falsi' or another numerical method like 'method of the means' is a better choice in case accuracy is important.

8. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

There was an article on Polynomial Curve Fitting by William Hood in Byte, June 1987 page 155. This involved calculating the change in Residual Variance on using successive increasing order polynomials, the polynomial that produces the largest decrease in RV over its predecessor is the best fit. The program used was written in basic, about 100 lines long, could be the basis for a VBA program for anyone interested.

9. ## Re: Calculating Intersections in a Chart (Excel-Office 2000-SR1)

I did a bit of research and came across this fantastic XLL file which will do exactly what you want. It is called XLXtrFun and can be found <A target="_blank" HREF=http://www.netrax.net/~jdavita/XlXtrFun/XlXtrFun.htm>here</A>.

It will interpolate using a variety of algorithms including the cubic spline that Excel seems to use for its curve fitting. It will also find the intersection of two curves. It is a neat package and it is freeware which is an outstanding price.

regards
Grant Cartledge