Results 1 to 10 of 10

20011101, 20:24 #1
 Join Date
 Jan 2001
 Location
 Warsaw, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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.

20011101, 23:27 #2
 Join Date
 Jan 2001
 Location
 Newcastle, New South Wales, Australia
 Posts
 81
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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 xrange and E17 as yrange. Select the singlepointseries and click again so that the cursor becomes a 4way 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

20011102, 21:23 #3
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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).

20011102, 21:38 #4
 Join Date
 Jan 2001
 Location
 Warsaw, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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!!

20011102, 21:53 #5
 Join Date
 Jan 2001
 Location
 Warsaw, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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.
Thanks again for your help!!

20011105, 01:12 #6
 Join Date
 Oct 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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 arrayenter. eg for the "fan curve" from your spreadsheet you would highlight a row of 6 cells and arrayenter 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

20011105, 13:35 #7
 Join Date
 Jan 2001
 Location
 Kortrijk, Belgium
 Posts
 571
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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 (n1)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.

20011110, 11:53 #8
 Join Date
 Jan 2001
 Location
 Adelaide, South Australia, Australia
 Posts
 387
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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.

20011116, 04:31 #9
 Join Date
 Oct 2001
 Posts
 15
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
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

20011119, 13:57 #10
 Join Date
 Jan 2001
 Location
 Warsaw, Indiana, USA
 Posts
 37
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Calculating Intersections in a Chart (ExcelOffice 2000SR1)
Thanks for your response geecee.
I passed along your response and all of the other responses to Curt . He is as please as punch and found what he needed. Thanks for all of your help.