software

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.

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

quot;Cadelimaquot; wrote:

gt; I am trying to otain a solution for the number where two curves meet.
gt; Specifically trying to determine the expected flow rate given a pump curve
gt; and system curve. The curves shift depending on variables that are read from
gt; a spreadsheet. I can get the curve formulas by graphing them and requesting
gt; the trendline feature, but then I have to manually enter the curve formulas,
gt; set them equal to each other, and solve the equation. Can Excel do this
gt; automatically from two data sets? Any help would be appreciated.

B. R.Ramachandran wrote...
gt;What kind curves are they (i.e., what kind of functions are those trendline
gt;equations) - linear, polynomial, logarithmic, ....? Can you post sample
gt;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.

gt;quot;Cadelimaquot; wrote:
gt;gt;I am trying to otain a solution for the number where two curves meet.
gt;gt;Specifically trying to determine the expected flow rate given a pump curve
gt;gt;and system curve. The curves shift depending on variables that are read from
gt;gt;a spreadsheet. I can get the curve formulas by graphing them and requesting
gt;gt;the trendline feature, but then I have to manually enter the curve formulas,
gt;gt;set them equal to each other, and solve the equation. Can Excel do this
gt;gt;automatically from two data sets? Any help would be appreciated.Hi,

Of course quot;Solverquot; 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, quot;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?quot;.

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 quot;Solverquot; is the only recourse.

Regards,
B. R. Ramachandranquot;Harlan Grovequot; wrote:

gt; B. R.Ramachandran wrote...
gt; gt;What kind curves are they (i.e., what kind of functions are those trendline
gt; gt;equations) - linear, polynomial, logarithmic, ....? Can you post sample
gt; gt;trendline equations for the pump curve and the system curve?
gt; ....
gt;
gt; It doesn't matter what kind of curves they are if they're both
gt; functions of a single, common variable (otherwise they couldn't be
gt; graphed together easily). All that's needed is one cell containing the
gt; common X value, one cell for each curve containing the f(x) and g(x)
gt; function values given the common X value and their other respective
gt; parameters, and finally another formula cell calculating the difference
gt; between the two function values. Then use Solver to set that last cell
gt; to zero by changing the X value cell. If the two curve intersect
gt; transversally, Solver shouldn't have any difficulty finding the
gt; intersection point's X value, and both functions will return the same Y
gt; value.
gt;
gt; If the two curve intersect nontransversally, e.g., two circles
gt; intersecting at a single point, then Solver might have some difficulty
gt; and the OP would need to play with its options.
gt;
gt; gt;quot;Cadelimaquot; wrote:
gt; gt;gt;I am trying to otain a solution for the number where two curves meet.
gt; gt;gt;Specifically trying to determine the expected flow rate given a pump curve
gt; gt;gt;and system curve. The curves shift depending on variables that are read from
gt; gt;gt;a spreadsheet. I can get the curve formulas by graphing them and requesting
gt; gt;gt;the trendline feature, but then I have to manually enter the curve formulas,
gt; gt;gt;set them equal to each other, and solve the equation. Can Excel do this
gt; gt;gt;automatically from two data sets? Any help would be appreciated.
gt;
gt;

quot;B. R.Ramachandranquot; gt; wrote...
....
gt;My purpose of asking the OP for the functional forms of f(x) and g(x)
gt;was that, if both are linear, logarithmic, simple exponential, quadratic,
gt;or
gt;power functions (and not higher order polynomials), then the x-value (and
gt;from it, the y-value) of the intersection point can be obtained using
gt;analytical formulas. Since those formulas will reference the cells
gt;containing the parameters/constants of f(x) and g(x), the solutions will
gt;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.
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 quot;I can get the
curve formulas by graphing them and requesting the trendline feature,...quot;.
(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.

quot;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.quot; 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)?

quot;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.quot;
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. Ramachandranquot;Harlan Grovequot; wrote:

gt; quot;B. R.Ramachandranquot; gt; wrote...
gt; ....
gt; gt;My purpose of asking the OP for the functional forms of f(x) and g(x)
gt; gt;was that, if both are linear, logarithmic, simple exponential, quadratic,
gt; gt;or
gt; gt;power functions (and not higher order polynomials), then the x-value (and
gt; gt;from it, the y-value) of the intersection point can be obtained using
gt; gt;analytical formulas. Since those formulas will reference the cells
gt; gt;containing the parameters/constants of f(x) and g(x), the solutions will
gt; gt;automatically (and conveniently) update when f(x) and g(x) change.
gt; ....
gt;
gt; If the OP's functions are simple low order polynomials, logarithmic or
gt; exponential functions to begin with, it'd be inefficient for the OP to be
gt; fitting trend lines. And the trend lines aren't necessarily good
gt; approximations for the underlying functions near the point of intersection.
gt; Solver is a manual solution (unless the OP were to use a Calculate event
gt; handler to run Solver whenever the function parameters change upon any
gt; recalc), but it's likely to be more accurate than finding the intersection
gt; point of trend line approximations to the underlying functions.
gt;
gt;
gt;

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:

GPMPump HeadSystem Head
02279123
3002254145
8002219277
13002192531
18002171905
230021551399
280021412014
330021302750
380021183607
430021054584
480020895681
530020696900
580020428239

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.

quot;B. R.Ramachandranquot; wrote:

gt; Hi,
gt;
gt; What kind curves are they (i.e., what kind of functions are those trendline
gt; equations) - linear, polynomial, logarithmic, ....? Can you post sample
gt; trendline equations for the pump curve and the system curve?
gt;
gt; Regards,
gt; B. R. Ramachandran
gt;
gt; quot;Cadelimaquot; wrote:
gt;
gt; gt; I am trying to otain a solution for the number where two curves meet.
gt; gt; Specifically trying to determine the expected flow rate given a pump curve
gt; gt; and system curve. The curves shift depending on variables that are read from
gt; gt; a spreadsheet. I can get the curve formulas by graphing them and requesting
gt; gt; the trendline feature, but then I have to manually enter the curve formulas,
gt; gt; set them equal to each other, and solve the equation. Can Excel do this
gt; gt; automatically from two data sets? Any help would be appreciated.

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
amp; 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 amp; Office 2003quot;Cadelimaquot; gt; wrote in message
news
gt; Polynomial equations are the best fit. As I mentioned, the system pressure
gt; and pump output pressure varies by fluid properties. An example of a data
gt; set
gt; from one fluid is as follows:
gt;
gt; GPM Pump Head System Head
gt; 0 2279 123
gt; 300 2254 145
gt; 800 2219 277
gt; 1300 2192 531
gt; 1800 2171 905
gt; 2300 2155 1399
gt; 2800 2141 2014
gt; 3300 2130 2750
gt; 3800 2118 3607
gt; 4300 2105 4584
gt; 4800 2089 5681
gt; 5300 2069 6900
gt; 5800 2042 8239
gt;
gt; Graphing the data and selecting a 3rd order polynomial for the trendline
gt; gives the following equations:
gt;
gt; System rate f(x): y=-5E-21x^3=0.002x^2-1E-13x 123.08
gt; Pump rate g(x): y=-2E-09x^3=2E-05x^2-0.0896x 2279.4
gt;
gt; The solution for flow rate is where the pump and system pressures are
gt; equivalent, or where the two equations are equal.
gt;
gt; Hope this helps.
gt;
gt; quot;B. R.Ramachandranquot; wrote:
gt;
gt;gt; Hi,
gt;gt;
gt;gt; What kind curves are they (i.e., what kind of functions are those
gt;gt; trendline
gt;gt; equations) - linear, polynomial, logarithmic, ....? Can you post sample
gt;gt; trendline equations for the pump curve and the system curve?
gt;gt;
gt;gt; Regards,
gt;gt; B. R. Ramachandran
gt;gt;
gt;gt; quot;Cadelimaquot; wrote:
gt;gt;
gt;gt; gt; I am trying to otain a solution for the number where two curves meet.
gt;gt; gt; Specifically trying to determine the expected flow rate given a pump
gt;gt; gt; curve
gt;gt; gt; and system curve. The curves shift depending on variables that are read
gt;gt; gt; from
gt;gt; gt; a spreadsheet. I can get the curve formulas by graphing them and
gt;gt; gt; requesting
gt;gt; gt; the trendline feature, but then I have to manually enter the curve
gt;gt; gt; formulas,
gt;gt; gt; set them equal to each other, and solve the equation. Can Excel do this
gt;gt; gt; automatically from two data sets? Any help would be appreciated.
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 D214 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 D214 (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(D214,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 quot;Toolsquot; --gt; quot;Optionsquot; --gt; quot;Calculationquot; Tab, check quot;Iterationquot; --gt; quot;OKquot;

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.92891.00.00.01.42890.9
The values will update when your original data change.

Regards,
B. R. Ramachandranquot;Cadelimaquot; wrote:

gt; Polynomial equations are the best fit. As I mentioned, the system pressure
gt; and pump output pressure varies by fluid properties. An example of a data set
gt; from one fluid is as follows:
gt;
gt; GPMPump HeadSystem Head
gt; 02279123
gt; 3002254145
gt; 8002219277
gt; 13002192531
gt; 18002171905
gt; 230021551399
gt; 280021412014
gt; 330021302750
gt; 380021183607
gt; 430021054584
gt; 480020895681
gt; 530020696900
gt; 580020428239
gt;
gt; Graphing the data and selecting a 3rd order polynomial for the trendline
gt; gives the following equations:
gt;
gt; System rate f(x): y=-5E-21x^3=0.002x^2-1E-13x 123.08
gt; Pump rate g(x): y=-2E-09x^3=2E-05x^2-0.0896x 2279.4
gt;
gt; The solution for flow rate is where the pump and system pressures are
gt; equivalent, or where the two equations are equal.
gt;
gt; Hope this helps.
gt;
gt; quot;B. R.Ramachandranquot; wrote:
gt;
gt; gt; Hi,
gt; gt;
gt; gt; What kind curves are they (i.e., what kind of functions are those trendline
gt; gt; equations) - linear, polynomial, logarithmic, ....? Can you post sample
gt; gt; trendline equations for the pump curve and the system curve?
gt; gt;
gt; gt; Regards,
gt; gt; B. R. Ramachandran
gt; gt;
gt; gt; quot;Cadelimaquot; wrote:
gt; gt;
gt; gt; gt; I am trying to otain a solution for the number where two curves meet.
gt; gt; gt; Specifically trying to determine the expected flow rate given a pump curve
gt; gt; gt; and system curve. The curves shift depending on variables that are read from
gt; gt; gt; a spreadsheet. I can get the curve formulas by graphing them and requesting
gt; gt; gt; the trendline feature, but then I have to manually enter the curve formulas,
gt; gt; gt; set them equal to each other, and solve the equation. Can Excel do this
gt; gt; gt; automatically from two data sets? Any help would be appreciated.