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.