Trends

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 5 of 5

Thread: Trends

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Sydney, Australia
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    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


  2. #2
    MrExcel MVP
    Join Date
    Mar 2002
    Location
    Michigan USA
    Posts
    11,454
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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, D.Eng, P.E.
    Energy Efficient Building Network LLC
    www.energyefficientbuild.com

  3. #3
    Board Regular zacemmel's Avatar
    Join Date
    Apr 2002
    Location
    Redmond, WA
    Posts
    636
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    May 2002
    Location
    Sydney, Australia
    Posts
    316
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    both work excellently thanks for the efforts

  5. #5
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    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 ]

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com