1. 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...
  2. 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...
  3. 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: thanks in advance
  4. 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...
  5. 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...
  6. 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...
  7. 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...
  8. 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)))...
  9. 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 =...
  10. 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...
  11. 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...
  12. 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...
  13. 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...
  14. 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...
  15. 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...
  16. 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...
  17. 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))...
  18. 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...
  19. 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...
  20. S

    Maximize R squared using solver DOCUMENT ATTACHED (may be difficult)

    Hey guys, I'm creating an algorithm for daily fantasy basketball and I have hit a little roadblock. Here is the link to download my spreadsheet: <a href=><img src= width=127 height=145 border=0/></a><br...

Some videos you may like

This Week's Hot Topics