Results 1 to 15 of 17

20050707, 21:59 #1
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
pick values of graph that meet condition (excel)
Hi ya'll,
I am wondering if its possible to pick values from a chart. If so what criteria do I need for my data to do this?
Basically I need to a way to get a value off the graph from two trend lines, both related by friction.
ex. if the friction was 5.3 then I would have 2.8 m3 in the tube and 4.7 in the outer hole.
the total rate would be 7.5.
Im not sure if this is possible to do.

20050707, 22:15 #2
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
heres a little more info to try and explain
I have attached the graph. Basically in one cell the total rate will typed in (the rate wont exceed 10m3 ever)
I am trying to find out the friction gradient for both the annulus and the tube. Which is the same number. But the rate at which the friction for the annulus will be different for rate the tubing because of different volumes.
For example.
I have typed a total rate of 7.5 into call a1
If I go on to my graph and pick a point on the tube rate (blue line) at 2.8m3/min the friction is 5.3Kpa/m
So,
On the pink line (Annulus) when friction is at 5.3 the rate is 4.7.
2.8m3/min + 4.7m3/min =7.5m3/min so this works.
Im not sure an alogrithm to use right now but working on one to figure out how I can find the two values that add to up to the value of a1 (7.5) and have the same friction number
does anyone have an idea?

20050707, 22:18 #3
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: pick values of graph that meet condition (excel)
One way would be to fit curves for both graphs. As an example, I have used linear approximation in the attached workbook. As you can see from the chart, that is not entirely correct. If you have an idea what kind of function would fit, you can use that instead.
I used LINEST to find the coefficients of the lines (slope and intercept).
Given an X, you can determine the friction forTubing; you can then determine the X' for which Annulus has the same friction, and add X and X' together.
You can use Tools  Goal Seek to "reverse engineer": for which X + X' = 7.5.
BTW, your chart should be an XY Scatter chart, not a line chart, since the X values are numerically meaningful, not just categories.

20050707, 22:49 #4
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
so the linest function makes a quess for a value for the tubing line, and then a value for the annulus line, where both these quessed numbers from the line add up to the total value?
Is the chart able to find a value that is not mentioned in my data?
for example , 8.4min is not in my flow rate table,
so if I choose 8.4m3/min as my total flow rate basically disect 8.4 until I find 2 values; 1 from the annulus line and one from the tubing line that add up to 8.4. THe friction value is always the same for the annulus line and tubing line. so it be 2.6m3/min for tubing and 5.8m3/min for annulus. so basically 31% is moving in the tubing and 69% in the annulus. right now i figure this answer out by alot of trial and error, and it be a big help to get a way to figure this equation out.
am i making sense? I am trying to figure the best way to ask my question,
thanks for taking a look

20050707, 22:54 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: pick values of graph that meet condition (excel)
It is very hard to help you if you keep on changing the text of your questions while I am typing a reply!
 Select cell J6 in the workbook I attached.
 Select Tools  Goal Seek.
 Specify that you want to set cell J6 to the value 8.4 by varying cell G6.
 Click OK.
After a short interval, cell G6 will change to approx. 3.13 and cell I6 to approx. 5.27. These are the flow rates. Their sum is 8.4 (cell J6) and the friction for both is 6.1 (cell H6)

20050707, 23:01 #6
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
sorry am changing my post...
thanks for your help it seems that it works.
if I add more data points into my data columns would this affect my out come?
would a macro be able to do this with a click of a button? or is the tools>goal seek step nessesary?

20050707, 23:12 #7
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
I should of mentioned, if I add more data to my columns, how do I manipulate this array so that it includes the new data into the linest equation.

20050707, 23:33 #8
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
ok, I dont see the attached modified worksheet...
when I change the 18 to a 20, and press enter excel says"you cant change part of an array!" am I doing this wrong?
THank you!!!

20050707, 23:35 #9
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: pick values of graph that meet condition (excel)
In this situation, with two linear approximations, you can actually calculate the x values (it's a system of 2 linear equations with 2 variables). See attached modified version. You can enter a number in the yellow cell, the two X values will be calculated automatically.
But if you would fit nonlinear curves, it may not be possible to calculate an exact solution, and you would need goal seek. This can be automated in a macro  try recording it.
The (array) formula =LINEST(C3:C18,B3:B18) specifies C3:C18 as y values and B3:B18 as x values. If you add more data points, the 18 would have to change to 19, 20, ... If you want this to be automatic, you can use dynamic ranges  see for example Named Ranges.

20050707, 23:39 #10
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: pick values of graph that meet condition (excel)
I have added the attachment.
Cells H2 and I2 share an array formula. If you want to edit or remove the formula, you must select H2:I2. And after editing, you must confirm with Ctrl+Shift+Enter, not just Enter. Same for H3 and I3.

20050707, 23:39 #11
 Join Date
 May 2005
 Location
 Vancouver, Br. Columbia
 Posts
 223
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
when you change the array highlight h2:i2 and the click the = beside the equation. this will prevent that message

20050707, 23:53 #12
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: pick values of graph that meet condition (excel)
<P ID="edit" class=small>(Edited by sdckapr on 07Jul05 20:53. Added more info)</P>A way without needing to approximate some regression is to just imterpolate. This has the advantage incase your data is not linear. Like Hans' solution you will have to remove the blank items within your data or you will get an message
Steve
PS. for got to add directions
Add the code to a module and then use something like:
=flowrate(7.5,B316)
the default limit is 0.001 you can change it in code or "on the fly with something like:
=flowrate(7.5,B316,0.1)
The limit is how close the sum of the 2 values have to come to the target value...

20050708, 06:40 #13
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
Wow, thanks for your time on this Hans and Steve, I know it must of taken time to create this cause it would of taken me a month. you both really know excel I am impressed and want to learn your skillzzzzzz.
Steve you code is awsome but I have no Idea where to place it. Im guess in the area where it says linear approximation,
I sorry but the code you wrote is over my head and thank you but let me put it to use.
where should I place it in Hans last attachment.
Thanks guys!!!

20050708, 07:07 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: pick values of graph that meet condition (excel)
The code should copied into a module (select Insert  Module in the Visual Basic Editor).
The formulas involving LINEST aren't needed any more since Steve uses a different method.
With the target flow in G2, the formula for the friction becomes
=FlowRate(G2,B3:B20)
or if you only need it to 1 decimal
=FlowRate(G2,B3:B20,0.1)
See attached version.

20050711, 07:19 #15
 Join Date
 May 2005
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: pick values of graph that meet condition (excel)
right on thanks, what happened to the annuls rate and the tube rate though