# Thread: pick values of graph that meet condition (excel)

1. ## 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.

2. ## Re: pick values of graph that meet condition (excel)

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?

3. ## 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.

4. ## 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

5. ## 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)

6. ## 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?

7. ## 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.

8. ## 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!!!

9. ## 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 non-linear 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.

10. ## Re: pick values of graph that meet condition (excel)

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.

11. ## 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

12. ## Re: pick values of graph that meet condition (excel)

<P ID="edit" class=small>(Edited by sdckapr on 07-Jul-05 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...

13. ## 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!!!

14. ## 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.

15. ## Re: pick values of graph that meet condition (excel)

right on thanks, what happened to the annuls rate and the tube rate though

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•