linest

  1. A

    Multi Variable Non Linear LINEST application

    I currently use LINEST to generate non linear coefficients that I then use in VBA quadratic equations. However I now have the need to do this where there are multiple X values. Does anyone know of a solution to do this? Thank you 100 200 400 2 23 30 37 4 30 37 43 6 40 42 47 8 45 47...
  2. C

    Formula to Detect Trends Over Time

    Please bear with this explanation as it may be lengthy but I want to be as clear as possible with my problem.. I am trying to detect with a formula the trend over the past 6 months of my data. I do not simply want to see if the data has increased from one month to the next month; rather I want...
  3. M

    Multi-Variable Curve Fitting

    <tbody> Dependent (f) Independent 1 (x) Independent 2 (y) 54 16 2 21 14 46 89 95 9 53 26 64 56 50 22 44 23 35 12 45 41 48 69 5 74 84 49 </tbody> Hello, I a looking for a way to create a curve based on multiple independent variables. The result...
  4. S

    Linest not working - Polynomial Trend

    hello all, I am trying to use excel to make polynomial trend (2ª order) of given data. Linest function is returning "#VALUE". xls file in attach: https://ufile.io/6c91i thanks in advance
  5. C

    LINEST Array for a specific sub segment

    Hi guys, I am using LINEST Array to calculate price elasticity and its related coefficients. For a multi-product portfolio. Y values: Monthly Price elasticity for each SKU X Values: Coefficitents for 5 items: Season (4 separate coefficients) Relative price level vs substitute products Right...
  6. L

    LINEST Function

    Hello All, I have a run chart and it includes a trend line. In the spresdsheet I use a LINEST function to give the trend line its value. I am using this value to make an automatic entry of "FLAT" "MODERATE" "MAJOR" Everything works EXCEPT when the column of numbers has empty cells. This is...
  7. C

    Lookups & removing errors while using linest()

    Hi, I'm using linest to obtain the slope of a series of data. My data is three colums of X, Y and 'category'. e.g. X Y 'category 1' X Y 'category 1' X Y 'category 2' X Y 'category 3' X Y 'category 3' X Y 'category 2' ect. ect. What I'm trying to do is look up the specific categories X...
  8. P

    LINEST returning wrong R^2

    I have a data set with only 2 variables (x and y) and I am using LINEST to set the intercept to zero and return additional statistics. The r^2 value that LINEST returns is completely wrong though. LINEST Returns a r^2 value of 0.999239357 where the actual r^2 value is 0.6544 that I get from a...
  9. M

    Using named range in LINEST formula

    Hello All, I want to use a named range in the LINEST formula (European version of Excel). It works fine if I just use a dynamic range in the formula like this: =INDEX(LINEST((INDIRECT(ADDRESS($AQ$1;22;4)):INDIRECT(ADDRESS(COUNTA(B:B);22;4)))...
  10. M

    Indexing LINEST formula coefficients non-redundantly

    Hello all, What I find online about how to find LINEST formula coefficients in EXCEL cells (NOT VBA) is introducing INDEX function directly on LINEST function like below: Equation: y = (c2 * x^2) + (c1 * x ^1) + b c2: =INDEX(LINEST(y,x^{1,2}),1) C1: =INDEX(LINEST(y,x^{1,2}),1,2) b =...
  11. S

    Multivariate Polynomial LINEST with Macro

    I've dug and dug, but I can't find out to use the LINEST function to calculate the polynomial fit for multivariate data in a macro independent of the worksheet. To be clear, I have found out how to do multivariate-polynomial fits in the worksheet, I just haven't figured out how to do this in a...
  12. D

    R-Squared LINEST does not match chart value

    I have a data set of x and y values, my aim is to come up with a formula which I can use to input an x value to give me the expected y value. When I plot my data on a scatter chart, using a 3rd order polynomial trend line I get an r-squared value of 0.8402. When I use LINEST to get an r-squared...
  13. P

    Using the LINEST Function to find equation of my curve

    Hi, I'm trying to find the point of diminishing returns on some econometric data we have received that shows money invested to bookings made. To do this I have been trying to find the equation of my curve in excel. I've seen a few other posts about using the LINEST function to do this, but...
  14. M

    Access the array resulting from LinEst function

    I am trying to output the values of the R2 from the linEst function for all the rows of data I have using the following code: Sub getdeflection6() Dim xvalues() As Double, yvalues() As Double, cell As Range Dim alldata As Range Dim results As Variant Dim counter As Integer...
  15. J

    Cubic coefficients using LINEST

    Hi, I am trying to LINEST to return coefficients using a cubic function in Excel 2013 but I am getting wrong results for two of the coefficients. Using the builtin Chart Trendline produces correct coefficient for my regression but LINEST (and Data Analysis Regression for that matter) produces...
  16. P

    Can Excel VBA LinEst using dynamic Arr directly instead of Range?

    Hello all MrExcel experts, I try to fasten my vba code posted below, already use screenupdating false function, empty cache. Currently I need 3 steps to use LinEst function for an Arr: Step1, write Arr to Range Step2, get Range Step3, use Range in LinEst I want to know, can Excel VBA LinEst...
  17. D

    Ignore blanks or 0's in Linest Formula

    I have seen a couple of threads on this but haven't been able to get this to work for myself. I am trying to report on particular values over a calendar year; are these values trending upwards or downwards? The particular project started mid year so January thru May are blank - but over time...
  18. J

    Using LINEST with Conditional Arrays

    Summary Question I am trying to use LINEST to calculate the linear regression output for selected data points in a table. The selection of the data points is being determined by a conditional array. Here's an example of the formula: LINEST(IF(D2:D10000=2,E2:E10000),IF(D2:D10000=2,E2:E10000))...
  19. T

    How do I get Excel to exclude cells from LINEST based on content?

    I'm attempting to establish trends and slopes that ignore outliers or inapplicable data. Here is my problem: 1. My data range is 16 columns wide, starting at row 6 (U6 to AJ6). Downward from there is more data in the same format, so we really only need to address one line, with the columns as...
  20. J

    Error with Row() or Column() within Offset() within Intercept()

    I'm having an issue with the following formula: INTERCEPT(OFFSET($AM$6,0,0,1,MonthOffset-COLUMN($AM$6)+3),OFFSET($AM$1,0,0,1,MonthOffset-COLUMN($AM$1)+3)) MonthOffset is just an integer, 72 in this case. The above formula works fine with LINEST, rather than INTERCEPT. The issue is with the...

Watch MrExcel Video

This Week's Hot Topics

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top