Derive formula from chart

Could you attached your workbook?

If you want to do it in the chart, you can use the trendline feature: http://office.microsoft.com/en-us/ex...158.aspx?CTT=1

I personally prefer to use the LINEST() function (or other regression algorithm) directly in the spreadsheet. http://office.microsoft.com/en-us/ex...005209155.aspx

Excel1.jpg

Continuing this thread, attached is the chart which the formula to be derived...

Is it possible to know the formula from the chart, so that i can know the temperature value at point 120?

If possible, can anyone help me, how to convert this photo into excel chart with the same data dan curves...

Thanks for any help!!!

Short but useless answer, Yes it is possible. There is not enough information in a picture to talk specifics, though.

Do you only have the picture, or do you also have the data the chart is based on? If all we have to work with is the picture, it is going to be difficult (I'm not aware of any program that will convert a picture of a chart to data). If we have real numbers (even if they are manually read off of the chart), we can see what we can come up with.

This is how I generally approach something like this:

1) I first notice that this is a contour plot for 3D data. I also notice that there is very different bevavior for each contour. This suggests to me that there could easily be a very complex relationship between y, x, and T. Often times the first thing I do doesn't involve Excel at all -- I start by studying the literature for whatever I'm working with (this almost looks like a water pump) to find the kinds of equations that are used to model the kind of thing I'm working with. That can be important here, because a generic polynomial may not be a very good fit.
2) Once I have the data in a table and know what equation I want to use, then I turn my attention to Excel. In Excel, I can choose a suitable regression algorithm (Linear or non-linear) and build the spreadsheet to perform the regression. Hard to give specifics without knowing what y=f(x,T) looks like.
3) Once I have the regression performed, then I can compute y at any given x and T.

It is not a trivial problem to undertake. In some ways, part 1 (which doesn't even involve Excel) is often the most difficult.

Maybe I'm just an optimist, but I would not say there is no way to solve this. With enough time, effort, and ingenuity, I see no reason why this cannot be solved.

Based on my interactions with professional engineers on another forum, they might first suggest going to the pump vendor and asking for the equations. Considering how smooth the curves are, I expect the pump vendor has already done all this work. If your agreement with the pump vendor allows it, the easiest solution would be to ask the pump vendor for the equations.

If you are unable to get any information from the pump vendor, then there will be a lot more effort required. I would probably start by printing that chart out and manually reading off multiple data points. Then enter these y,x,T values into three columns of a spreadsheet. Then proceed to step 1 -- figure out what kind of regression equation is needed for this.

Capture10.JPG

Yea, I did make these values into three columns of a spreadsheet and up until now, I just can stare at those values

I did research and find same type of table but the weird thing is the table has different intersection point. (Attached photo is the table I am talking about). At 60 F they have same intersection point, but at 80 F they have totally different point, that is just weird, isn't it?

This makes me think that each vendor develops their own formula to make this pump selection table and the problem is they do not give any references or formula on making this table. Actually, this pump is quite new and is not like the other pump and I think the vendor still do not want to reveal the formula

I really have no idea, how to derive the formula for this table.

MrShorty, could you teach me, how to convert this chart into excel chart? I am really new to excel and I have been trying to convert this chart, but I got stuck. I already make the x and y axis data, but then I am not able to plot the line, do you know how do this?

Perhaps the vendor doesn't want to share the equation(s), I don't know.

So you do have x,y,T data you have pulled from the chart. Good. Basic tutorial for creating a scatter chart: http://office.microsoft.com/en-us/ex...478.aspx?CTT=1 Creating a contour plot like yours is the same, but with multiple data series (help for adding a data series to an existing chart: http://office.microsoft.com/en-us/ex...980.aspx?CTT=1)

When you say you are "new to Excel" and "you have no idea how to derive the formula" -- have you ever done any regression before in any application?

Let us know how you do making the chart. Recognize that the chart will be useful for visualizing the data, but I don't know how useful it will be in regressing the data. There are only a few available trendline equations, and I'm not sure any of them will really be a good choice for replicating the curves shown in the vendor's chart. Unless I am missing something, I would almost certainly perform this regression in the spreadsheet (linear or non-linear least squares, depending on what the regression equation looks like).

I don't see any need for plotting a secondary axis here. What we want is a contour plot, so what we are plotting is several series where factor is a function of pressure at a specific temperature. Some of this depends on exactly what data you have extracted from your chart, but I often think that the easiest way to build a contour plot is to put the data in a table that looks something like this:

Please Login or Register  to view this content.

where the T's are the different temperatures you have, P's are the pressures, and the f's are the factors at those T's and P's. Select P1 through fnn, and create a scatter plot from the data. (After creating the chart, you may have to go into the formatting dialogs and specify that you want the data in columns rather than rows -- I hate this about the new charting engines that it often defaults to data in rows rather than columns). This works when you read f's off of the chart at the same T and P for each point.

Each data series consists of only a single point -- there is nothing to connect. Also, since each data series only consists of a single data point, it is impossible to regress any kind of trendline for each data series.

These data points look like the "minimum pressure" curve from the original chart. In order to "draw" this minimum pressure curve, we need to take these data points, and make them their own data series -- separate from the other contours for the main function. I'd probably put these data points into a column adjacent to this table, then add this column to the chart and call it "minimum suction pressure" or something.

Once you have the "minimum suction pressure" series added to the chart, you can add a trendline using the chart trendline feature. http://office.microsoft.com/en-us/ex...158.aspx?CTT=1 Many people find chart trendlines more intuitive to use. However, since all of the available chart trendlines are "linear" functions, I personally prefer to use the LINEST() function to generate the regression coefficients directly in the spreadsheet. http://office.microsoft.com/en-us/ex...010069838.aspx

There is still the question of whether any of the available chart trendlines is a suitable equation for this curve. If you are unfamiliar with pump design, I would continue to encourage you to study pump design equations so you will understand what kind of curve this function should have before proceeding to choose a regression algorithm/trendline.

Untitled.jpg

Yes, that is right, 21.5 is the minimum pressure if the Temperature and Factor are 50 and 1.3, respectively. It goes the same to the next line.

One question, in the photo you see that series 1 point "21.5" (21.5, 1.3). Why the value series 1 is "21.5"? Not "50"? I mean, the command should say "show 50, if points 21.5 and 1.3 meet". Or is this ok?

I tried applied trendline to the chart, for "two period moving average" and it shows parallel line to the red line, what does it mean? I don't understand how this trendline works and how i can get formula from the chart. Could you give me an enlightenment to this matter?

Well, to study this type of pump is kind of hard, as i told you, this pump is quite new, there is not much information available but I am still searching for it and I hope I can get useful information to solve this problem.

One question, in the photo you see that series 1 point "21.5" (21.5, 1.3). Why the value series 1 is "21.5"? Not "50"? I mean, the command should say "show 50, if points 21.5 and 1.3 meet". Or is this ok?

As near as I can tell, you have done just fine so far. You have created a 2D scatter plot and have plotted f vs. P for the minimum pressure curve on this scatter plot, which is exactly what your original plot showed. You have not told Excel to do anything with the temperature column, and, since Excel doesn't support 3D scatter plots, Excel would not know what to do with the temperature column anyway.

In your original plot, the temperature data was shown visually by the other contours in the plot. You could see where the 50 degree contour intersects the minimum pressure curve, showing you the minimum pressure and f at 50 F. Same for the other temperatures. In terms of recreating the original chart, you still need to build the other contours. If you don't really need the other contours, you could use Rob Bovey's XY chart labeler add-in (www.appspro.com) to label the data points with the values in the temperature column.

Since Excel does not support 3D scatter plots, the chart trendline feature is also not going to know how to regress any kind of 3D equation [z=f(x,y)]. If you need to derive a 3D equation, you are almost certainly going to have to do this directly in the spreadsheet and not use the chart trendline feature.

I tried applied trendline to the chart, for "two period moving average" and it shows parallel line to the red line, what does it mean? I don't understand how this trendline works and how i can get formula from the chart. Could you give me an enlightenment to this matter?

Moving average means it takes 2 (or however many data points you specify) and averages them to get the trendline value. http://en.wikipedia.org/wiki/Moving_average Almost certainly not a very meaningful nor useful trendline for this kind of data.

Well, to study this type of pump is kind of hard, as i told you, this pump is quite new, there is not much information available but I am still searching for it and I hope I can get useful information to solve this problem.

However, pumps in general have been around for a long time, and the equations that describe pump operation and design have also been around for a long time. Unless we are talking about a completely new kind of pump technology for which the existing theory is completely inadequate, we should be able to use existing theory to help us describe the operation of your specific pump. Perhaps reading through Wikipedia's pump article http://en.wikipedia.org/wiki/Pump or this one from the Hydraulic Institute http://www.pumps.org/content_detail_pumps.aspx?id=1768 will provide some needed introduction to pumps.

Excel's help file, which describes the different trendlines pre-programmed into the chart: http://office.microsoft.com/en-us/ex...158.aspx?CTT=1 If the equation you want to use for a specific curve on the chart fits one of those equation types, then you may be able to regress a purely empirical curve.

Because of the limitations using the chart trendline, I almost always prefer to perform my regressions directly in the spreadsheet using either the LINEST() function (for "linear" functions) or Solver for performing non-linear regressions.

help file for LINEST(): http://office.microsoft.com/en-us/ma...823.aspx?CTT=1 It is not very well explained exactly what is meant by a "linear" function, but LINEST() can be used to regress any function that is "linear" as one would define it in linear algebra. With the exception of moving average, every trendline function available in the chart is a linear function and can easily be regressed using LINEST(). So, if you decided that your function should be something like f=a*ln(P) + b*T+c, you could use the LINEST() function to regress a, b, and c.

Originally Posted by MrShorty

So, if you decided that your function should be something like f=a*ln(P) + b*T+c, you could use the LINEST() function to regress a, b, and c.

Yea, this exactly what I want...

For non-linear regressions solver, do you have any suggestion to that? Like application or software, maybe?

If you let u=ln(P), then the above equation becomes f=a*u+b*T+c, which is a linear equation. If you then add a column for u adjacent to a column containing T, then you can use LINEST() like in example 3 from the help file to perform a multiple linear regression to obtain a, b, and c.

I don't get it.

As you can see, my objective actually just want to get the value with numbers skipped in the table. Do you know how to get those value, by only using temperature and pressure and then applying linest method?

I think, this is much more easier and after that we can get the temperature factor.

Did you try any of Microsoft's examples in the help file? Is there something specific about using the LINEST() function that doesn't make sense? It is often easier to answer specific questions than it is to answer "I don't get it."

Here's a file with a couple more examples of how to use it. I didn't bother typing all of your data in, just enough to illustrate how it works. I'm not sure that a log function is that great of a fit, but that was the example I used.

Yea, "I don't get this f=a*u+b*T+c. u=ln(P), P and T are derived from LINEST(), right?

I took that equation from post 21/22 where you indicated that this was the exact form of the equation you wanted. P and T are not derived from the LINEST() function -- the LINEST() function returns a, b, and c (the "coefficients" or "parameters" for the chosen equation form). We can choose any equation form we like. If you think there is a better equation for this, be sure to post it and we'll help you regress parameters for it.

My point is we want to know the pressure at temperature values skipped in the chart then why we need to key in pressure as well?

I'm beginning to think that I must not understand exactly what you want. All of the pictures you have shown have shown factor as a function of pressure, so I have been assuming that you want factor as the dependent variable and pressure as the independent variable. Are you really trying to regress pressure as a function of temperature -- so that factor is not even part of the equation? Intuitively, I would expect that minimum suction pressure is related to vapor pressure of water, so, using an equation that looks like the Clausius Clapeyron equation, one could regress ln(P)=A+B/T (see attachment). Is that more along the lines of what you want?

So far, been doing this, I think that LINEST() couldn't solve the problem. Because the use of LINEST() is for linear regression. And this chart is not linear, instead the chart is curvy.

I attached photo, you can see the difference between MANUAL (which I key in myself the values without any formula) and LINEST() charts. I hope you understand what I am trying to say here.

You can see that in Temperature 40, the Pressure in LINEST() is higher than MANUAL because LINEST() is only working when the data is linear, which in my case is not applicable.

The main difference I see between these latest manual and LINEST() charts is that manuel is concave up and the regressed linest curve is concave down. To me this indicates a poor choice for the regression equation rather than a failure of linest() to match the curve. In many ways, this goes back to where I suggested you start -- with enough understanding of pump design to talk about the equations that are used in designing pumps.