Formula to Detect Trends Over Time

carterc

New Member
Joined
Aug 16, 2019
Messages
1
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.

Cost TrendingJanFebMarAprMayJunJulAugSepOctNovDec
20181842530306487636.835662181
2019847858627678294484

<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 TrendingJanFebMarAprMayJunJulAugSepOctNovDec
20182.23.12.83.11.82.11.92.53.13.22.93.2
20193.23.44.15.26.27.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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

Watch MrExcel Video

Forum statistics

Threads
1,114,472
Messages
5,548,228
Members
410,824
Latest member
Bobmn4
Top