User Name
Password
Log in
Forgotten Your Password?
Help
Register
Remember Me?
Forum
What's New?
Today's Posts FAQ Calendar
Forum
Members List Community
Calendar
Forum Actions
Forum Rules
Dashboard
Commercial Services Advanced Search
Quick Links
Microsoft Office Application Help - Excel Help forum
Excel General
Can I get Excel to determine the line curve formula without graph.
To get replies by our experts at nominal charges, follow this link to buy points and post your thread in our Commercial Services forum! Here is the FAQ for this forum. Results 1 to 9 of 9
+ Reply to Thread Can I get Excel to determine the line curve formula without graph. LinkBack
Thread Tools
Rate This Thread
#1
12-15-2005, 07:00 PM
Cadelima Guest
Display
Can I get Excel to determine the line curve formula without graph. I am trying to otain a solution for the number where two curves meet. Specifically trying to determine the expected flow rate given a pump curve and system curve. The curves shift depending on variables that are read from a spreadsheet. I can get the curve formulas by graphing them and requesting the trendline feature, but then I have to manually enter the curve formulas, set them equal to each other, and solve the equation. Can Excel do this automatically from two data sets? Any help would be appreciated.
Vortex Flow Meter
Register To Reply
#2
12-15-2005, 08:35 PM
B. R.Ramachandran Guest
RE: Can I get Excel to determine the line curve formula without graph. Hi, What kind curves are they (i.e., what kind of functions are those trendline equations) - linear, polynomial, logarithmic, ....? Can you post sample trendline equations for the pump curve and the system curve?
Riff City Guitar & Music
Regards, B. R. Ramachandran "Cadelima" wrote: > I am trying to otain a solution for the number where two curves meet. > Specifically trying to determine the expected flow rate given a pump curve > and system curve. The curves shift depending on variables that are read from > a spreadsheet. I can get the curve formulas by graphing them and requesting > the trendline feature, but then I have to manually enter the curve formulas, > set them equal to each other, and solve the equation. Can Excel do this > automatically from two data sets? Any help would be appreciated. Register To Reply
#3
12-15-2005, 09:55 PM
Harlan Grove Guest
Re: Can I get Excel to determine the line curve formula without graph. B. R.Ramachandran wrote... >What kind curves are they (i.e., what kind of functions are those trendline >equations) - linear, polynomial, logarithmic, ....? Can you post sample >trendline equations for the pump curve and the system curve? .... It doesn't matter what kind of curves they are if they're both functions of a single, common variable (otherwise they couldn't be graphed together easily). All that's needed is one cell containing the common X value, one cell for each curve containing the f(x) and g(x) function values given the common X value and their other respective parameters, and finally another formula cell calculating the difference between the two function values. Then use Solver to set that last cell to zero by changing the X value cell. If the two curve intersect transversally, Solver shouldn't have any difficulty finding the intersection point's X value, and both functions will return the same Y value. If the two curve intersect nontransversally, e.g., two circles intersecting at a single point, then Solver might have some difficulty and the OP would need to play with its options. >"Cadelima" wrote: >>I am trying to otain a solution for the number where two curves meet. >>Specifically trying to determine the expected flow rate given a pump curve >>and system curve. The curves shift depending on variables that are read from >>a spreadsheet. I can get the curve formulas by graphing them and requesting >>the trendline feature, but then I have to manually enter the curve formulas, >>set them equal to each other, and solve the equation. Can Excel do this >>automatically from two data sets? Any help would be appreciated.
Register To Reply
#4
12-16-2005, 01:00 AM
B. R.Ramachandran Guest
Re: Can I get Excel to determine the line curve formula without gr Hi, Of course "Solver" wouldn't have any difficulty in finding the x- and y- values of the intersection point of f(x) and g(x) (by making the difference between the functions equal zero). However, please note that the solver solution is a one-time result, and doesn't automatically update when f(x) and g(x) change; so your have to manually invoke Solver every time the functions change (I know it is not a big deal, but is certainly an inconvenience). The OP did mention, "The curves shift depending on variables that are read from a spreadsheet........... I can get the curve formulas by graphing them and requesting the trendline feature, but then I have to manually enter the curve formulas, set them equal to each other, and solve the equation. Can Excel do this automatically from two data sets?". My purpose of asking the OP for the functional forms of f(x) and g(x) was that, if both are linear, logarithmic, simple exponential, quadratic, or power functions (and not higher order polynomials), then the x-value (and from it, the y-value) of the intersection point can be obtained using analytical formulas. Since those formulas will reference the cells containing the parameters/constants of f(x) and g(x), the solutions will automatically (and conveniently) update when f(x) and g(x) change. If f(x) and g(x) are higher-order polynomials or if they are of different functional forms, analytical solutions may be difficult/impossible to obtain, and "Solver" is the only recourse. Regards, B. R. Ramachandran
"Harlan Grove" wrote: > B. R.Ramachandran wrote... > >What kind curves are they (i.e., what kind of functions are those trendline > >equations) - linear, polynomial, logarithmic, ....? Can you post sample > >trendline equations for the pump curve and the system curve? > .... > > It doesn't matter what kind of curves they are if they're both > functions of a single, common variable (otherwise they couldn't be > graphed together easily). All that's needed is one cell containing the > common X value, one cell for each curve containing the f(x) and g(x) > function values given the common X value and their other respective > parameters, and finally another formula cell calculating the difference > between the two function values. Then use Solver to set that last cell > to zero by changing the X value cell. If the two curve intersect > transversally, Solver shouldn't have any difficulty finding the > intersection point's X value, and both functions will return the same Y > value. > > If the two curve intersect nontransversally, e.g., two circles > intersecting at a single point, then Solver might have some difficulty > and the OP would need to play with its options. > > >"Cadelima" wrote: > >>I am trying to otain a solution for the number where two curves meet. > >>Specifically trying to determine the expected flow rate given a pump curve > >>and system curve. The curves shift depending on variables that are read from > >>a spreadsheet. I can get the curve formulas by graphing them and requesting > >>the trendline feature, but then I have to manually enter the curve formulas, > >>set them equal to each other, and solve the equation. Can Excel do this > >>automatically from two data sets? Any help would be appreciated. > > Register To Reply
#5
12-16-2005, 01:40 AM
Harlan Grove Guest
Re: Can I get Excel to determine the line curve formula without gr "B. R.Ramachandran" wrote... .... >My purpose of asking the OP for the functional forms of f(x) and g(x) >was that, if both are linear, logarithmic, simple exponential, quadratic, >or >power functions (and not higher order polynomials), then the x-value (and >from it, the y-value) of the intersection point can be obtained using >analytical formulas. Since those formulas will reference the cells >containing the parameters/constants of f(x) and g(x), the solutions will >automatically (and conveniently) update when f(x) and g(x) change. .... If the OP's functions are simple low order polynomials, logarithmic or exponential functions to begin with, it'd be inefficient for the OP to be fitting trend lines. And the trend lines aren't necessarily good approximations for the underlying functions near the point of intersection. Solver is a manual solution (unless the OP were to use a Calculate event handler to run Solver whenever the function parameters change upon any recalc), but it's likely to be more accurate than finding the intersection point of trend line approximations to the underlying functions.
Register To Reply
#6
12-16-2005, 05:10 PM
B. R.Ramachandran Guest
Re: Can I get Excel to determine the line curve formula without gr Hi, The question, as I understood from the OP's posting, is not how to obtain f(x) and g(x) for two sets of real-life data; the OP says "I can get the curve formulas by graphing them and requesting the trendline feature,...". (Incidentally, the functions have got to be one of the types that the Trendline tool can handle, which are linear, exponential, logarithmic, polynomial, ...). The OP's question, on the other hand, was how to find the intersection point of f(x) and g(x). As I did mention in my earlier response, I do agree that Solver can certainly find the intersection point. However, my point is, if the solution for x when f(x)=g(x) can be expressed as an explicit analytical function (which is possible if both functions are linear, quadratic, simple exponential, logarithmic, or power functions, and not higher order polynomials), calculating the intersection point using such an analytical solution is certainly superior to finding an 'optimized' solution using Solver. Any optimization method, including Solver, is limited by the optimization criteria (algarithm used, convergence criterion, decimal tolerance, .....), and also on the initial values of the optimization parameters (in this case x-value of the intersection point). Of course, for simple functions such as the ones considered here, the Solver-result may be as good as the analytical solution, but the analytical solution is absolute. An added advantage with analytical solutions, as I had mentioned, is that the solutions update when the functions change (As you correctly pointed out, the same can be accomplished by using Calculate Event Handler to run Solver). A couple of questions about your remarks. My apologies if I haven't understood those remarks correctly. "If the OP's functions are simple low order polynomials, logarithmic or exponential functions to begin with, it'd be inefficient for the OP to be fitting trend lines." But, aren't those the only function-types that Trendline handles (besides moving-averages)? What type of functions would lend themselves to be efficient to be fit with trendlines? And, when the functions do belong to those simple types, how would you efficiently fit them to real data without using Trendline (or Solver for that matter)? "And the trend lines aren't necessarily good approximations for the underlying functions near the point of intersection. Solver is ........ likely to be more accurate than finding the intersection point of trend line approximations to the underlying functions." f(x) and g(x) are two functions that happen to intersect at some x,y-point when somebody plots them out on a common graph; crudely put, the functions don't even know the existence of each other (If the two functions are coupled/correlated it is a totally different story). Any uncertainty in finding the intersection point of f(x) and g(x) depends on the quality of how well each function fits its respective x,y-data and how those uncertainties add up near the intersection point. Calculating the intersection point using the analytical solution can not diminish the inherent uncertainty in the intersection point, but it would certainly not worsen it; on the other hand, an optimaztion method such as Solver could, in certain situations, add some extra uncertainty to the solution due to inherent limitations of the method. But then, when explicit analytical solutions are not possible for a sytem of equations, one has to resort to optimization methods. Regards, B. R. Ramachandran
"Harlan Grove" wrote: > "B. R.Ramachandran" wrote... > .... > >My purpose of asking the OP for the functional forms of f(x) and g(x) > >was that, if both are linear, logarithmic, simple exponential, quadratic, > >or > >power functions (and not higher order polynomials), then the x-value (and > >from it, the y-value) of the intersection point can be obtained using > >analytical formulas. Since those formulas will reference the cells > >containing the parameters/constants of f(x) and g(x), the solutions will > >automatically (and conveniently) update when f(x) and g(x) change. > .... > > If the OP's functions are simple low order polynomials, logarithmic or > exponential functions to begin with, it'd be inefficient for the OP to be > fitting trend lines. And the trend lines aren't necessarily good > approximations for the underlying functions near the point of intersection. > Solver is a manual solution (unless the OP were to use a Calculate event > handler to run Solver whenever the function parameters change upon any > recalc), but it's likely to be more accurate than finding the intersection > point of trend line approximations to the underlying functions. > > > Register To Reply
#7
12-19-2005, 03:45 PM
Cadelima Guest
RE: Can I get Excel to determine the line curve formula without gr Polynomial equations are the best fit. As I mentioned, the system pressure and pump output pressure varies by fluid properties. An example of a data set from one fluid is as follows: GPM Pump Head System Head 0 2279 123 300 2254 145 800 2219 277 1300 2192 531 1800 2171 905 2300 2155 1399 2800 2141 2014 3300 2130 2750 3800 2118 3607 4300 2105 4584 4800 2089 5681 5300 2069 6900 5800 2042 8239 Graphing the data and selecting a 3rd order polynomial for the trendline gives the following equations: System rate f(x): y=-5E-21x^3=0.002x^2-1E-13x+123.08 Pump rate g(x): y=-2E-09x^3=2E-05x^2-0.0896x+2279.4 The solution for flow rate is where the pump and system pressures are equivalent, or where the two equations are equal. Hope this helps. "B. R.Ramachandran" wrote: > Hi, > > What kind curves are they (i.e., what kind of functions are those trendline > equations) - linear, polynomial, logarithmic, ....? Can you post sample > trendline equations for the pump curve and the system curve? > > Regards, > B. R. Ramachandran > > "Cadelima" wrote: > > > I am trying to otain a solution for the number where two curves meet. > > Specifically trying to determine the expected flow rate given a pump curve > > and system curve. The curves shift depending on variables that are read from > > a spreadsheet. I can get the curve formulas by graphing them and requesting > > the trendline feature, but then I have to manually enter the curve formulas, > > set them equal to each other, and solve the equation. Can Excel do this > > automatically from two data sets? Any help would be appreciated. Register To Reply
#8
12-20-2005, 12:10 PM
Dana DeLouis Guest
Re: Can I get Excel to determine the line curve formula without gr Hi. If I'm not mistaken, I believe your equation results are slightly off because of display error. For example, your x^3 term in System Rate should be about 8.34 *10^-12 (vs your -5*10^-21). When Solving equations, you may find it better to extract the coefficients. For Example, select 4 horizontal cells and Array enter the following with X & Y referring to your data. =LINEST(Y ,X^{1,2,3}) (Ctrl+Shift+Enter) These coefficients appear correct when compared to the output from another program. (System): 8.34352E-12 ,0.000241208 ,1.19407E-05, 123.0624551 (Pump): -2.02024E-09 ,2.01471E-05, -0.089731972, 2279.045625 Subtract the coef. to get something like... -2.0285*10^-9*x^3-0.000221*x^2-0.08974*x+2155.98317 Solve the cubic equation (equation =0) to get three real solutions... -108474.58402 -3389.100225 2890.94350 Only 2890 appears as the feasible solution. HTH -Dana DeLouis Win XP & Office 2003
"Cadelima" wrote in message news:
[email protected]... > Polynomial equations are the best fit. As I mentioned, the system pressure > and pump output pressure varies by fluid properties. An example of a data > set > from one fluid is as follows: > > GPM Pump Head System Head > 0 2279 123 > 300 2254 145 > 800 2219 277 > 1300 2192 531 > 1800 2171 905 > 2300 2155 1399 > 2800 2141 2014 > 3300 2130 2750 > 3800 2118 3607 > 4300 2105 4584 > 4800 2089 5681 > 5300 2069 6900 > 5800 2042 8239 > > Graphing the data and selecting a 3rd order polynomial for the trendline > gives the following equations: > > System rate f(x): y=-5E-21x^3=0.002x^2-1E-13x+123.08 > Pump rate g(x): y=-2E-09x^3=2E-05x^2-0.0896x+2279.4 > > The solution for flow rate is where the pump and system pressures are > equivalent, or where the two equations are equal. > > Hope this helps. > > "B. R.Ramachandran" wrote: > >> Hi, >> >> What kind curves are they (i.e., what kind of functions are those >> trendline >> equations) - linear, polynomial, logarithmic, ....? Can you post sample >> trendline equations for the pump curve and the system curve? >> >> Regards, >> B. R. Ramachandran >> >> "Cadelima" wrote: >> >> > I am trying to otain a solution for the number where two curves meet. >> > Specifically trying to determine the expected flow rate given a pump >> > curve >> > and system curve. The curves shift depending on variables that are read >> > from >> > a spreadsheet. I can get the curve formulas by graphing them and >> > requesting >> > the trendline feature, but then I have to manually enter the curve >> > formulas, >> > set them equal to each other, and solve the equation. Can Excel do this >> > automatically from two data sets? Any help would be appreciated.
Register To Reply
#9
12-20-2005, 06:00 PM
B. R.Ramachandran Guest
RE: Can I get Excel to determine the line curve formula without gr Hi, Thre are a few ways by which you can accomplish what you want: Let's suppose that column flow rates (GPM) are in A2:A14, Pump Pressure are in B2:B14, and System Pressure are in C2:C14. Create a column D2:D14 as follows: In D2 enter the formula =B2-C2, and drag the formula down to D14. Method 1 (Maybe Good!): By making an inverse plot (a slightly approximate, but very quick, solution) Make a plot of A2:A14 (Y-axis) and D2:D14 (X-axis) and fit a polynomial (maybe, 3-rd order) and get the trendline equation. The constant term is what you want. Unfortunately, this result may be about 2-4% off from the 'correct' value. For example, for your data, the result from this approach is 2952 gpm (the trendline equation is y = 1E-08x3 - 0.0001x2 + 0.8402x + 2952.2) is about 2% off from the correct value (2891 gpm) A convenient aspect of this method is that the trendline equation would automatically update when your original data change. Method 2 (Better?): You can use Solver, and solve for the flow rate for which the difference between Pump Pressure and System Pressure will be zero. Obtain the trendline equation for The result obtained would be very good (e.g., 2891 for your samnple data). However the inconvenient aspect is that the result WILL NOT update when your original data change(s). You have to manually invoke Solver every time that happens. Method 3 (Best!): Exploiting Circular Reference. Select a 1 Row x 4 Column area, and entering the following formula, =LINEST(D2:D14,A2:A14^{1,2,3}), confirmed with CTRL-SHIFT-ENTER (for your sample data, 2.029E-09, 2.211E-04, 8.974E-02, -2.156E+03) In E2, enter an arbitrary flow rate (gpm) value, say 300. In F2, =1.00001*E2 IN G2, =a*E2^3+b*E2^2+c*E2+d (where a, b, c, and d are the actual values of the coefficients from the trendline equation or linest function) In H2, =a*F2^3+b*F2^2+c*F2+d In I2, =(H2-G2)/(F2-E2) In J2, =(I2*E2-G2)/I2 Now comes the interesting part. Go back to E2 and enter =J2. Excel will complain and say there is a Circular Reference. Ignore that. Go to "Tools" --> "Options" --> "Calculation" Tab, check "Iteration" --> "OK" Now Excel will accept the circular reference. You would notice that E2 and J2 have become equal. That is the value you want! Your sample data yielded the following results: 2890.9 2891.0 0.0 0.0 1.4 2890.9 The values will update when your original data change. Regards, B. R. Ramachandran
"Cadelima" wrote: > Polynomial equations are the best fit. As I mentioned, the system pressure > and pump output pressure varies by fluid properties. An example of a data set > from one fluid is as follows: > > GPM Pump Head System Head > 0 2279 123 > 300 2254 145 > 800 2219 277 > 1300 2192 531 > 1800 2171 905 > 2300 2155 1399 > 2800 2141 2014 > 3300 2130 2750 > 3800 2118 3607 > 4300 2105 4584 > 4800 2089 5681 > 5300 2069 6900 > 5800 2042 8239 > > Graphing the data and selecting a 3rd order polynomial for the trendline > gives the following equations: > > System rate f(x): y=-5E-21x^3=0.002x^2-1E-13x+123.08 > Pump rate g(x): y=-2E-09x^3=2E-05x^2-0.0896x+2279.4 > > The solution for flow rate is where the pump and system pressures are > equivalent, or where the two equations are equal. > > Hope this helps. > > "B. R.Ramachandran" wrote: > > > Hi, > > > > What kind curves are they (i.e., what kind of functions are those trendline > > equations) - linear, polynomial, logarithmic, ....? Can you post sample > > trendline equations for the pump curve and the system curve? > > > > Regards, > > B. R. Ramachandran > > > > "Cadelima" wrote: > > > > > I am trying to otain a solution for the number where two curves meet. > > > Specifically trying to determine the expected flow rate given a pump curve > > > and system curve. The curves shift depending on variables that are read from > > > a spreadsheet. I can get the curve formulas by graphing them and requesting > > > the trendline feature, but then I have to manually enter the curve formulas, > > > set them equal to each other, and solve the equation. Can Excel do this > > > automatically from two data sets? Any help would be appreciated. Register To Reply
+ Reply to Thread
« Previous Thread | Next Thread » Thread Information
There are currently 1 users browsing this thread. (0 members and 1 guests) Bookmarks
Digg del.icio.us StumbleUpon Google
Posting Permissions
You may not post new threads You may not post replies You may not post attachments You may not edit your posts
BB code is On Smilies are On [IMG] code is Off HTML code is Off Trackbacks are Off
Pingbacks are Off Refbacks are On Forum Rules
Contact Us ExcelForum.com Archive Top
-- vB4 Default Style
All times are GMT -4. The time now is 06:53 AM.
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1