Formula to identify upward or downward trending....

Rachel_23

New Member
Joined
Apr 12, 2007
Messages
15
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,937
Members
448,534
Latest member
benefuexx

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
Back
Top