Value in a row are trending up or down?

**Coume** · 03-23-2010, 06:58 AM

Hello,

I was wondering if the below could be done but if it is I have no idea how so any pointer would be welcome.

I have a row ranging from B2:BO2
Is there a way to determine if the values are trending up or down?

Thanks
Ludo

**sweep** · 03-23-2010, 07:13 AM

Hi,

You can use LINEST to return the gradient of the best fit line to your data to establish the direction of trend.

=IF(LINEST(B2:BO2,,,TRUE)>0,"Trending Up",IF(LINEST(B2:BO2,,,TRUE)<0,"trending down","Stable"))

**Coume** · 03-23-2010, 08:29 AM

Excellent! Thanks.

I works on some lines but not on all of them as my data are not continuous there are some blank cells

I tried to do something like that with OFFSET:

=LINEST(OFFSET('Unit Shipped'!B2:BO2,0,0,COUNT('Unit Shipped'!B2:BO2),1),,,TRUE

and to come up with something with ISNUMBER but I am still stuck

=LINEST(IF(ISNUMBER('Unit Shipped'!B2:BO2),'Unit Shipped'!B2:BO2,0),,,TRUE)

Any idea on how to use linest on these, will be more than welcome

Ludo

**sweep** · 03-23-2010, 08:37 AM

Hi,

This array formula should do it.

=IF(LINEST(IF(ISNUMBER(B2:BO2),B2:BO2,0),,,TRUE)>0,"up",IF(LINEST(IF(ISNUMBER(B2:BO2),B2:BO2,0),,,TRUE)=0,"Stable","Down"))

This replaces empty cells with zero, which will alter the gradient, so may affect you results depending on the amount and magnitude of the data present.

Remember to commit an array formula using CTRL, SHIFT and ENTER