> you can NEVER return a "2" which represents: > "If every year since the max experienced a percent increase"
Ah! Oops. And thank you for the macro. I will try it out. As for this: > 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. My plan was to later take things a step further, and return values based on certain *thresholds* of percent change. For example, - If, since the max, there was a decrease of between 20% and 49%, in any given year, return a 1. - If, since the max, there was at least a 50% decrease, in any given year, return a 2. and so on. I suspect your code can be adjusted to easily accommodate this, and I'll give it a try. Thank you. On Nov 17, 8:22 am, Paul Schreiner <schreiner_p...@att.net> wrote: > 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 othercells, 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$$Calculatingdirectionofchangeacrossseveralcells > > > I'm trying to return a value (1,2,or3) based on the percentchangein > > values since a max value. For example, assumecellsA1: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 percentchangefrom 300 to 200 is -.33% (200-300/300). > > The percentchangefrom 200 to 50 is -.75% (50-200/200). > > The percentchangefrom 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 athttp://www.excel-macros.blogspot.com > > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com > > 5. Excel Tips and Tricks athttp://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&;... -- ---------------------------------------------------------------------------------- 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
