+ Reply to Thread
Results 1 to 27 of 27

Derive formula from chart

  1. #1
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Derive formula from chart

    Hi all,

    I would like to ask whether it is possible to derive the formula from a chart?

    Right now, i have a chart and i want to know the formula, the purpose is to expand the values that the chart has.

    Let say the chart has result at point 20, 30, 40, 50... Is it possible for me to know the point at 21, 22, 23, 24, 25 and so on (with increment 1)

    Thanks for any help!!!

  2. #2
    Forum Expert RobertMika's Avatar
    Join Date
    06-22-2009
    Location
    Haverhill, UK
    MS-Off Ver
    Excel 2003-13
    Posts
    1,530

    Re: Derive formula from chart

    Could you attached your workbook?
    If you are http://www.excelforum.com/image.php?type=sigpic&userid=125481&dateline=1392355029happy with the results, please add to the contributor's
    reputation by clicking the reputation icon (star icon).




    If you are satisfied with the solution(s) provided, please mark your thread as Solved.
    Select Thread Tools-> Mark thread as Solved.
    To undo, select Thread Tools-> Mark thread as Unsolved.
    http://www.excelaris.co.uk

  3. #3
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  4. #4
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    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!!!
    Last edited by hamidun; 04-23-2014 at 03:47 AM.

  5. #5
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  6. #6
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    I only have this picture.

    Yea, that is correct, this table is used to select a pump. For example, if water temperature is 72 F, the suction pressure should not be more or less than 30 Torr. The table only show specific numbers of temperature and pressure. And my idea is to know all the numbers.

    That is the problem, i do not have the data for this table. Actually, i got this table from one company providing this pump. The only data that is available, maybe from the picture itself.

    So, there is no way to solve this?

  7. #7
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  8. #8
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    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?
    Last edited by hamidun; 04-23-2014 at 12:09 PM.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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?

  10. #10
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Yea, maybe.

    Oh, i see. So, use scatter not line chart.

    Nope, I know some of formula, but when dealing with chart, I am kind of confused how to modify the data axis.

    Thanks for the info, I am trying to make the chart.

  11. #11
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

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

  12. #12
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Capture1.JPG

    This is what i have done so far.. I really don't know how to make it. I am confused how to command "if the water temperature is 60 F, the factor will be 1 and suction pressure will be 25 Torr" to the chart...

    I tried to change the series to secondary axis and I have no idea how the chart system works.

    Could you help me MrShorty?
    Attached Images Attached Images

  13. #13
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  14. #14
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Capture11.jpg

    Thanksss!!! I have finally made it!!! But why the lines are not connected? Do you know how to connect the lines?

    Back to the main topic. I am still trying to derive the formula from the chart. So far, from the chart I have made, I tried to use trendline, but it didn't change anything? Can you explain to me how to use trendline?

  15. #15
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  16. #16
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    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.
    Last edited by hamidun; 04-25-2014 at 04:36 AM.

  17. #17
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.
    Last edited by MrShorty; 04-25-2014 at 09:14 AM.

  18. #18
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Quote Originally Posted by MrShorty View Post
    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.
    So, most likely I will no be able to derive the formula from this chart?

  19. #19
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  20. #20
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Ok, now I think, I understand a bit about this trendline, I applied logarithm trendline and it works perfectly, but it doesn't seem to meet my need, because it is only applicable for 2D chart.

    How to use the LINEST()? Is that fucntion only applicable for linear function? I don't understand, because my chart is in 3D.

  21. #21
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  22. #22
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Quote Originally Posted by MrShorty View Post
    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?
    Last edited by hamidun; 04-28-2014 at 05:01 AM.

  23. #23
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.

  24. #24
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    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.
    Last edited by hamidun; 04-29-2014 at 01:03 AM.

  25. #25
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.
    Attached Files Attached Files

  26. #26
    Registered User
    Join Date
    03-12-2014
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    39

    Re: Derive formula from chart

    Capture2.JPG

    Yea, "I don't get this f=a*u+b*T+c. u=ln(P), P and T are derived from LINEST(), right? What I don't get is why there are a, b, c which they are the inputs from Temperature, Pressure and Factor, right? 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? If it's not for pressure, then for what? The input should only be 2, right?

    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.

  27. #27
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,668

    Re: Derive formula from chart

    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.
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Derive optimal mixture formula
    By SandPounder1 in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 02-12-2014, 05:24 PM
  2. Please Help! Need a formula(s) that derive a ratio from calendar dates
    By seannormand in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 10-12-2013, 09:28 PM
  3. [SOLVED] Formula to Derive Date from Alphanumeric Code
    By gmarie in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 01-16-2013, 09:50 AM
  4. Derive a value across 2 worksheets
    By JonUK01 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 03-26-2011, 07:31 PM
  5. derive formula from series of points
    By andyismilesaway in forum Excel General
    Replies: 1
    Last Post: 02-28-2007, 06:55 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1