First of all, you can NEVER return a "2" which represents: "If every year since the max experienced a percent increase"
Because: If you find the MAX of the 5 years, then check it against the following year and the following year there was an INCREASE, then evidently you didn't find the MAX correctly because the following year should have been the MAX!!! Second is somewhat of a "logic" issue: you really don't need to know what the PERCENT increase/decrease per year is to determine if it is increasing/decreasing. you really just need to compare the values. 200-300 is a decrease of 100 units. (200-300)/300 is still a decrease, expressed as a %, but it's still a decrease. I THINK I could do it with Excel formulas by finding the max, then using an array formula or match() function to find the particular cell. then use a LONG IF() statement to check the other cells, but I KNOW I can do it with: '======================================================================== Option Explicit Public Function FindTrend(Rng As Range) Dim Rval, msg, Rarray, I, MaxVal, MaxInx, MaxAddr Dim TrendFlag ReDim Rarray(Rng.Count - 1) I = -1 MaxVal = -100 TrendFlag = 1 For Each Rval In Rng I = I + 1 Rarray(I) = Rval.Value If (IsNumeric(Rval.Value)) Then If Rval.Value > MaxVal Then MaxVal = Rval.Value MaxInx = I MaxAddr = Rval.Address End If End If msg = msg & Chr(13) & Rval.Value Next Rval '----------------------------------------- If MaxInx < UBound(Rarray) Then For I = MaxInx To UBound(Rarray) - 1 If Rarray(I + 1) < Rarray(I) Then If TrendFlag <> 1 Then TrendFlag = 3 Exit For End If End If Next I TrendFlag = Replace(MaxAddr, "$", "") & " : " & TrendFlag Else TrendFlag = "Most Recent" End If FindTrend = TrendFlag End Function '======================================================================== then, in an adjacent cell, use the formula: =findtrend(A1:E1) (assuming the values are in A1:E1) If your logic changes, let me know and I can help modify the code to fit. Paul ----- Original Message ---- > From: 0 1 <hhholme...@gmail.com> > To: MS EXCEL AND VBA MACROS <excel-macros@googlegroups.com> > Sent: Tue, November 16, 2010 6:35:44 PM > Subject: $$Excel-Macros$$ Calculating direction of change across several cells > > I'm trying to return a value (1,2,or3) based on the percent change in > values since a max value. For example, assume cells A1:E1 contain > these values: > > 200,300,200,50,150. > > The values represent the number of widgets produced in year 1, year 2, > up to year 5. > > The max value for this example is 300. > > The percent change from 300 to 200 is -.33% (200-300/300). > The percent change from 200 to 50 is -.75% (50-200/200). > The percent change from 50 to 150 is +2.0% (150-50/50). > > If every year since the max experienced a percent decrease, return a > 1. > If every year since the max experienced a percent increase, return a > 2. > In all other cases, e.g., a decrease in one year, an increase in the > next (as in this example), return a 3. > > If it's not possible to use a formula for this, a macro would be fine. > I'm just stumped as to how to get Excel to calculate this. It needs to > find the peak, and then determine whether all subsequent values are > constantly decreasing, increasing, or in flux. > > Thanks for any suggestions. > > > -- >---------------------------------------------------------------------------------- >- > Some important links for excel users: > 1. Follow us on TWITTER for tips tricks and links : >http://twitter.com/exceldailytip > 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 > 3. Excel tutorials at http://www.excel-macros.blogspot.com > 4. Learn VBA Macros at http://www.quickvba.blogspot.com > 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com > > To post to this group, send email to excel-macros@googlegroups.com > > <><><><><><><><><><><><><><><><><><><><><><> > Like our page on facebook , Just follow below link > http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts > -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/pages/discussexcelcom/160307843985936?v=wall&ref=ts
