Trends

[Cossie]

I need a formula that will return a value of "positive", "negative" or "no trend" from a series of numbers eg. Have tried nested if statements and TREND() but none of thses return the correct result

10 20 30 40 = Positive or
10 09 08 07 = Negative or
10 12 08 15 = No trend.

Any suggestions?

David

[Yogi Anand]

On 2002-05-07 17:25, Cossie wrote:
I need a formula that will return a value of "positive", "negative" or "no trend" from a series of numbers eg. Have tried nested if statements and TREND() but none of thses return the correct result

10 20 30 40 = Positive or
10 09 08 07 = Negative or
10 12 08 15 = No trend.

Any suggestions?

David

Nothing sofisticated , but for the data set that you presented, a nested IF function would do ...

=IF(AND(B2-A2>0,C2-B2>0,D2-C2>0),"positive",IF(AND(-B2-A2<0,C2-B2<0,D2-C2<0),"negative","notrend"))

[zacemmel]

{=IF(SUM(IF((A1:A5>OFFSET(A1:A5,1,0))*(ROW(A1:A5)<>5), 1, 0))=0, "positive", IF(SUM(IF((A1:A5>OFFSET(A1:A5,1,0))*(ROW(A1:A5)<>5), 1, 0))=(COUNTA(A1:A5)-1),"negative", "none"))}

This is an array formula. This can be used with huge ranges of numbers without lots of nested ifs. The only things that have to change are the cell references. Let me know how it goes.

[ This Message was edited by: zacemmel on 2002-05-07 18:11 ]

[Cossie]

both work excellently thanks for the efforts

[Mark W.]

As does...

=IF(OR(NOT(INDEX(LINEST(A1:D1),1,1)),INDEX(LINEST(A1:D1,,,1),3,2)),"No Trend",IF(INDEX(LINEST(A1:D1),1,1)<0,"Negative","Postive"))

[ This Message was edited by: Mark W. on 2002-05-08 12:06 ]