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

1. ## 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. 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. ok I'll give it a try, I think I get it....lol

4. 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. 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. 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. ## Re: Formula to identify upward or downward trending....

Originally Posted by Damon Ostrander
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

## User Tag List

#### Posting Permissions

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