Results 1 to 7 of 7

Thread: What is the way to interpolate numbers in Microsoft Excel

  1. #1
    Join Date
    Jan 2012
    Posts
    25

    What is the way to interpolate numbers in Microsoft Excel

    I was trying to interpolate numbers by using Forecast, but it is not the linear interpolation result I need. I am wondering is there a way to do it. Following is Forecast help example. By using Forecast, the result is 10.6, but if using interpolation, Known Y (7,9) Known X (28,31), the result should be 8.333.
    • Known Y KnownX
    • 6 20
    • 7 28
    • 9 31
    • 15 38
    • 21 40

    Formula Description (Result) : =FORECAST(30,A2:A6,B2:B6) Predicts a value for y given an x value of 30 (10.60725). Thank you very much in advance.

  2. #2
    Join Date
    Aug 2011
    Posts
    695

    Re: What is the way to interpolate numbers in Microsoft Excel

    I think you are confusing Y and X. The example fits a straight line using all five values and bases the forecast on that fitted line. For interpolation between two points, use only the two adjacent values for the straight-line forecast:
    =FORECAST(30,A3:A4,B3:B4)
    or
    =FORECAST(30,{7,9},{28,31}) and the result is 8.3333.

  3. #3
    Join Date
    Mar 2011
    Posts
    542

    Re: What is the way to interpolate numbers in Microsoft Excel

    You could use the slope and intercept functions and calculate the numbers you need using those. That way the function doesn't get in the way of what you are trying to do. You can plot the data & insert a non-linear trend line, using the option to display the equation on the chart, then use that equation to back out your intermediate values.

  4. #4
    Join Date
    May 2011
    Posts
    448

    Re: What is the way to interpolate numbers in Microsoft Excel

    I have found the following. A trend () function to make in one lin interpolation. The formula is for me: = TREND (C7: C19, B7: B19, C27, 0). But the result does not fit somehow. If I calculate it by hand I get: Excel then calculates e_LiNE = 889.056502.
    :linear interpolation:
    (21783-20554) / (941-922) = (21266,18-20554) / (x-922)
    1229/19 = 712.18 / (x-922)
    x = 933.01
    So E_linearisert = 933.01 (at F = 21266.18). And that makes Erbenis fürmich even more sense. I would like to automate this process but with a formula to e-lin for different R-values ​​to be determined. Only the above Funtkion Excel does not seem to fit somehow.

  5. #5
    Join Date
    Jun 2011
    Posts
    635

    Re: What is the way to interpolate numbers in Microsoft Excel

    In this case, a linear regression performed on one and linear interpolation can be avoided. It should be noted, however, that a regression line in contrast an interpolation curve (usually) not exactly through all the data points is. A linear regression can, for example as has been written with the TREND function to be performed. A measure of the goodness of the regression for example, the coefficient of determination R ^ 2 (0 <= R ^ 2 <= 1). In the case of R ^ 2 = 1 all data points lie exactly on the regression line.

  6. #6
    Join Date
    Jul 2011
    Posts
    640

    Re: What is the way to interpolate numbers in Microsoft Excel

    The result of comparison is the line number within the site where the passed matrix. So if you in A4: A13 are looking for and get one back, this means that the 'Source' in the first Line of A4: A13 is and that's in the Excel spreadsheet, just the fourth row If you want to use this result now outside of this matrix further, you have to stop adding up the difference in the starting line of table and matrix, in this case 4-1 = 3.

  7. #7
    Join Date
    Jul 2011
    Posts
    634

    Re: What is the way to interpolate numbers in Microsoft Excel

    This function is used to interpolate or extrapolate using trusses or splines, which are functions-at first-cubic polynomials with pieces that fit between the points to be interpolated, so that between pairs of adjacent points is polynomial different (with exceptions). The slope and second derivative at the ends of the trusses, and matches the following values ​​in the beginning of the first and at the end of the latter are configurable depending on the type of truss that is needed, i.e., are established "end conditions". Very important note: The data must be sorted in ascending and endpoint conditions apply, the first (1 st? And v1) to the lower value of Rango_xy (1 st column) and (2 ª? And v2) for the most value of Rango_xy (1 st column). Example of using the function TRUSSES Fits a cubic truss forced start and finish going through the given points. The slope at the starting point is -1.5 and the final slope is 1.5.

Similar Threads

  1. Replies: 2
    Last Post: 17-02-2012, 05:33 PM
  2. Replies: 2
    Last Post: 17-02-2012, 01:03 PM
  3. How to calculate told of numbers combination in Microsoft Excel list
    By Ankit Zh@very in forum MS Office Support
    Replies: 3
    Last Post: 24-01-2012, 02:23 PM
  4. How to open mac .numbers extenstion files on Microsoft Excel
    By TheCarter in forum MS Office Support
    Replies: 2
    Last Post: 18-01-2012, 05:41 PM
  5. How to find frequency of lottery numbers in Microsoft Excel
    By Aarti C in forum MS Office Support
    Replies: 1
    Last Post: 14-01-2012, 01:52 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
  •  
Page generated in 1,711,691,518.77977 seconds with 17 queries