HOW TO MAKE SIMPLE FORMULA FOR TREND IDENTIFICATOR ?? (INCREASING/DECREASING)

[ZEBRAX]

HI I HAVE 6 CELLS (A1:F1) FILLED WITH 6 PAST WEEKS OF SALES VALUES. I NEED TO CALCULATE IN (G1) CELL IF THAT RANGE HAS INCREASING OR DECREASING TREND.
IDENTIFICATOR (G1) CAN BE WORD ''DECREASE'' OR CAN BE PERCENTAGE VALUE. WHAT WOULD FORMULA FOR BOTH IDENTIFICATOR TYPES LOOK LIKE?

 

[Kaper]

To use a mathematical approach, you could do a regression of weekly values against week numbers. If the slope of fitted line is positive, it is increasing trend, if it's negative, it is decreasing.
So G1:

=IF(SLOPE(A1:F1,COLUMN(A1:F1))>0,"increasing","decreasing")

I skiped answer 0 - when there is no change, but it can be added in nested second if, like:

=IF(SLOPE(A1:F1,COLUMN(A1:F1))>0,"increasing",IF(SLOPE(A1:F1,COLUMN(A1:F1))<0,"decreasing","steady"))

BTW: Please don't write with CapsLock activated. Writing in all capital letters is treated in internet as SHOUTING. And you probaly (I hope) don't want to shout on those who want to help you.

 

[ZEBRAX]

Thank you so much you are an angel. Can I have please one more additional question? Is it possible to improve formula by replacing simple +/- trend identificator by precise trend number like in % G1? so i can colour highlight only models that have very strong decreasing sales trend above 30%.
formula should be able to process zero values without returning error. thanks

 

[Kaper]

Hi, The answer is probably not an easy one. At first one could say, that the slope gives .... a slope of the trendline:

=SLOPE(A1:F1,COLUMN(A1:F1))

but this value would be the same for
1 2 3 ... 6
and
101 102 103 ... 106
while we would like to say, that this first is very intensive increase, and the second mere "just better than flat".

So my proposition would be to take into account also intercept.

=SLOPE(A1:F1,COLUMN(A1:F1)-1)*5/INTERCEPT(A1:F1,COLUMN(A1:F1)-1)

of course result cell shall be formatted as percent

COLUMN(A1:F1)-1) returns an artificial X axis for SLOPE and INTERCEPT with values 0 1 2 ... 5

As for errors for 0 - I dont think 0's will generate errors, except of all zero values.

The possible problem with the approach proposed is when intercept is 0. Then we will observe error (of division by 0).