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 to find the overall trend throughout the past 6 months of data (Please note that this data will be updated monthly and thus needs to be dynamic). I have some automation worked in with custom formatting that will output an arrow dependent on the value of the trend of the line I am analyzing. I need help with creating a formula that works consistently to detect an upwards, downwards, and flat trend. This formula will also need to ignore blanks in selection area. There are other posts with similar problems but no solutions that fit my needs.
Here is what I currently have as a formula to solve this problem. I thought I had solved it at first but this formula will only work with a certain size of numbers, anything too small or too large and it wont behave properly for my needs.
<tbody>
</tbody>
=IF(LINEST(OFFSET(A3,0,COUNTA(B3:M3),1,-6))>0.9,10,IF(LINEST(OFFSET(A3,0,COUNTA(B3:M3),1,-6))<-0.9,-10,0.5))
**Where B3:M3 is 2019 Jan-Jun data and A3 references "2019" for offset function.
When there is an upwards trend, I would like to return a positive number greater than 1 (I chose 10 just because). If there is a downwards trend I want to display a negative number less than -1 (I chose -10 just because). If there is a flat trend, I want to display a number between 0.1-0.9b (I chose 0.5 just because).
The formula I currently have works for some sized numbers but not others (for example this formula currently works with the sample data set above and returns a "-10" to represent a downward trend). The problem arises when I encounter very small or very large data sets.
<tbody>
</tbody>
With this data set above, my formula would return a "0.5" to indicate a flat trend, when it is clear that 2019 Jan-Jun is increasing.
I am open to other solutions to solve this problem but I think my formula could be close with some tweaks. I've searched all across the forums and google and have not seen any solution to solve this. Please note that detecting a flat trend is a necessity with this scenario otherwise I would be able to solve this fairly easily.
I would really appreciate any insights or solutions anyone can offer! Please don't hesitate to ask for clarification in any area and I would be glad to.
Thanks!!
Carter
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 to find the overall trend throughout the past 6 months of data (Please note that this data will be updated monthly and thus needs to be dynamic). I have some automation worked in with custom formatting that will output an arrow dependent on the value of the trend of the line I am analyzing. I need help with creating a formula that works consistently to detect an upwards, downwards, and flat trend. This formula will also need to ignore blanks in selection area. There are other posts with similar problems but no solutions that fit my needs.
Here is what I currently have as a formula to solve this problem. I thought I had solved it at first but this formula will only work with a certain size of numbers, anything too small or too large and it wont behave properly for my needs.
| Cost Trending | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 2018 | 184 | 25 | 30 | 30 | 64 | 87 | 63 | 6.8 | 35 | 66 | 21 | 81 |
| 2019 | 847 | 858 | 627 | 678 | 294 | 484 | ||||||
<tbody>
</tbody>
=IF(LINEST(OFFSET(A3,0,COUNTA(B3:M3),1,-6))>0.9,10,IF(LINEST(OFFSET(A3,0,COUNTA(B3:M3),1,-6))<-0.9,-10,0.5))
**Where B3:M3 is 2019 Jan-Jun data and A3 references "2019" for offset function.
When there is an upwards trend, I would like to return a positive number greater than 1 (I chose 10 just because). If there is a downwards trend I want to display a negative number less than -1 (I chose -10 just because). If there is a flat trend, I want to display a number between 0.1-0.9b (I chose 0.5 just because).
The formula I currently have works for some sized numbers but not others (for example this formula currently works with the sample data set above and returns a "-10" to represent a downward trend). The problem arises when I encounter very small or very large data sets.
| Cost Trending | Jan | Feb | Mar | Apr | May | Jun | Jul | Aug | Sep | Oct | Nov | Dec |
| 2018 | 2.2 | 3.1 | 2.8 | 3.1 | 1.8 | 2.1 | 1.9 | 2.5 | 3.1 | 3.2 | 2.9 | 3.2 |
| 2019 | 3.2 | 3.4 | 4.1 | 5.2 | 6.2 | 7.5 | ||||||
<tbody>
</tbody>
With this data set above, my formula would return a "0.5" to indicate a flat trend, when it is clear that 2019 Jan-Jun is increasing.
I am open to other solutions to solve this problem but I think my formula could be close with some tweaks. I've searched all across the forums and google and have not seen any solution to solve this. Please note that detecting a flat trend is a necessity with this scenario otherwise I would be able to solve this fairly easily.
I would really appreciate any insights or solutions anyone can offer! Please don't hesitate to ask for clarification in any area and I would be glad to.
Thanks!!
Carter
