Results 1 to 7 of 7

Thread: Formula to identify upward or downward trending....
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Apr 2007
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Formula to identify upward or downward trending....

    Ok, I'm going to try and explain this. I am going to have 5 data points in cells such as A2:A6

    All I need is to know if there is an upward trend or downward trend of the numbers.... I can't have something that simply takes the first and last number and checks if it's higher or lower. I need to actually see if they are trending upwards. Also how would i go about making it so that if they are trending upwards it shows an up arrow, and down arrow for downwards...

    And finally, if the trend continues at the same rate, how long it will take the number to reach a goal. Each data point will be 1 week apart, with an ultimate goal needed to be achieved at the end.
    This will be a completely separate formula on the sheet....

    Any help?

  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi Rachel,

    You can do the arrow thing with a formula like this:

    =IF(LINEST(A2:A6)>0,"↑","↓")

    You can extrapolate using the same (linear) trend like this:

    =TREND(B2:B6,A2:A6,A15,FALSE)

    where this time I assumed your values to trend were in B2:B6, the corresponding time values (or dates, etc.) were in A2:A6, and the time value you want to extrapolate the trend to is in A15.

    Damon

  3. #3
    New Member
    Join Date
    Apr 2007
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    ok I'll give it a try, I think I get it....lol

  4. #4
    New Member
    Join Date
    Apr 2007
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, I have a problem with the first formula. Works perfect if I have data in each cell, but there is a few instances where there is no data in a cell. This ends up with an error. Is there a way to make it omit and continue to figure out if there's a trend, if one of the data points has no value?
    For example A2 is 75% A3 is 60%, A4 is blank (there was no data for this period) A5 is 16% and A6 is 99%... this results in an error due to the blank cell, how do I overcome this issue?

  5. #5
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,239
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi again Rachel_23,

    There may be ways of doing this that do not require VBA, but this is all I could think of to solve your "no data" issue:

    Add the following user-defined function (UDF) code to a standard macro module in your workbook:

    Code:
    Option Base 1
    
    Function NonEmptyArray(R As Range) As Variant
       'Returns the subset of R that is not empty
       Dim Cell    As Range
       Dim V()     As Variant
       Dim N       As Long     'number of elements in V
       N = 0
       For Each Cell In R
          If Not IsEmpty(Cell) Then
             N = N + 1
             ReDim Preserve V(N)
             V(N) = Cell.Value
          End If
       Next Cell
       NonEmptyArray = V
    End Function
    [note: to do this, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane]

    Then simply change your formula to:

    =IF(LINEST(NonEmptyArray(A2:A6))>0,"↑","↓")

    The NonEmptyArray UDF converts the non-empty cells in the range to a contiguous array of values that the LINEST function can use. It may be important to note that a cell containing a zero value or a space character is not an empty cell.

    Keep Exceling.

    Damon

  6. #6
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    2 Thread(s)

    Default

    Unlike the LINEST function, the SLOPE function ignores missing data. And, that's all the information you are using from the LINEST function.

    Ok, I have a problem with the first formula. Works perfect if I have data in each cell, but there is a few instances where there is no data in a cell. This ends up with an error. Is there a way to make it omit and continue to figure out if there's a trend, if one of the data points has no value?
    For example A2 is 75% A3 is 60%, A4 is blank (there was no data for this period) A5 is 16% and A6 is 99%... this results in an error due to the blank cell, how do I overcome this issue?

  7. #7
    New Member
    Join Date
    Dec 2016
    Posts
    1
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Formula to identify upward or downward trending....

    Quote Originally Posted by Damon Ostrander View Post
    Hi again Rachel_23,

    There may be ways of doing this that do not require VBA, but this is all I could think of to solve your "no data" issue:

    Add the following user-defined function (UDF) code to a standard macro module in your workbook:

    Code:
    Option Base 1
    
    Function NonEmptyArray(R As Range) As Variant
       'Returns the subset of R that is not empty
       Dim Cell    As Range
       Dim V()     As Variant
       Dim N       As Long     'number of elements in V
       N = 0
       For Each Cell In R
          If Not IsEmpty(Cell) Then
             N = N + 1
             ReDim Preserve V(N)
             V(N) = Cell.Value
          End If
       Next Cell
       NonEmptyArray = V
    End Function
    [note: to do this, go to the VBE (keyboard Alt-TMV), insert a new macro module (Alt-IM), and paste this code into the Code pane]

    Then simply change your formula to:

    =IF(LINEST(NonEmptyArray(A2:A6))>0,"↑","↓")

    The NonEmptyArray UDF converts the non-empty cells in the range to a contiguous array of values that the LINEST function can use. It may be important to note that a cell containing a zero value or a space character is not an empty cell.

    Keep Exceling.

    Damon
    Many thanks for the above information. I have one question: what if the last result in my column is 'FALSE', how can I make excel ignore this and take the last number​? Thank you

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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