Excel Trends :: MrExcel Message Board
 Search   Memberlist   Usergroups   Favorites   Statistics   Register

 Trends

Cossie
Board Master

Joined: 07 May 2002
Posts: 141
Location: Sydney, Australia
Flag:

Status: Offline

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

Tue May 07, 2002 9:25 pm

Yogi Anand
MrExcel MVP

Joined: 13 Mar 2002
Posts: 5440
Location: Michigan USA

Status: Offline

quote:

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"))

_________________
Regards!
Yogi Anand

Tue May 07, 2002 9:40 pm

zacemmel
Board Master

Joined: 30 Apr 2002
Posts: 513

Status: Offline

{=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 ]

Tue May 07, 2002 10:08 pm

Cossie
Board Master

Joined: 07 May 2002
Posts: 141
Location: Sydney, Australia
Flag:

Status: Offline

both work excellently thanks for the efforts

Wed May 08, 2002 3:37 am

Mark W.
MrExcel MVP

Joined: 11 Feb 2002
Posts: 7146
Location: Austin, Texas USA
Flag:

Status: Offline

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 ]

Wed May 08, 2002 3:34 pm
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First

Forum Jump:
 Jump to: Select a forum MrExcel Forums----------------Excel QuestionsInternational forumMicrosoft AccessTechnical issues and Future development Holy Macro! Books----------------Holy Macro! Products The Lounge----------------Max Cells Lounge Announcements----------------About This BoardHall of Fame WinnersTest Here

Page 1 of 1

Forum Rules:
 You cannot post new topics in this forumYou cannot reply to topics in this forumYou cannot edit your posts in this forumYou cannot delete your posts in this forumYou cannot vote in polls in this forum