Dividing a range into sections and performing calculations on each section

[RawlinsCross]

Good day, I'm looking for some advice on how to do the following:

I have a range variable named rYvalues to which I assign a range which can be of varying length (7 rows to 365 rows). I would like to do the following:

step 1 - divide the range up into sections, the number of which is taken from the value of a combo box.
step 2 - take the average of each section
step 3 - compare the first and last section (% increase or decrease)

I'll put the result from step three in a label on a userform. This part I think I can manage. The tricky part is step 1 - any thoughts on how I can do this?

Cheers.

 

[Jeffrey Mahoney]

So, if you're just trying to get the percent comparison to the last n and the first n values then maybe this formula could help:
=SUM(OFFSET($C$1,COUNTA(OFFSET($C$1,1,0,MATCH(1E+300,$C:$C)-ROW($C$1),1))-ryValues+1,0,ryValues,1))/SUM(OFFSET($C$1,1,0,ryValues,1))

As far as the average per section, a table would have to be created showing the row at the beginning of each section. I would use the offset formula similar to what is above.

Jeff

 

[Jeffrey Mahoney]

So what happens if the sections aren't easily divided? Let's say there are 906 rows and the divider is 25. That leaves 36.24 sections. That's about 6 rows just hanging out there. Add them in the beginning, or the end, or try to evenly split them among the sections, let the first 6 sections have one extra?

 

[RawlinsCross]

Hey Jeff, thanks for your reply. I should have mentioned perhaps this is all in the realm of VBA coding. I have a userform that has two DTPickers which the user can change (thus the changing size of the range). There is also a combo box which has values between 7 and 30 - so the user can chop of the range in 7 to 30 sections. So the user picks the dates, the dates determine the length of the range. Then there's a command button called "Calculate" and this is where I am now.

You're right on the "sections not easily divided" issue - I've thought about that myself. I would think I'd have to use a modulus function in vba? Do I need to deal with additional arrays?

 

[Jeffrey Mahoney]

Getting down to the bones of it. Getting averages from Arrays is not as easy as getting an average of a range. But you can set ranges easily using the UNION command.

This is a work in progress. I hard coded the ranges and set some variable values that should come from your form.

Jeff

Public BegDate As Date
Public EndDate As Date
Public NumSections As Long


Sub GetSections()
  Dim CurDt As Date
  Dim Cel As Range
  Dim R As Range
  Dim v As Double
  Dim X As Long
  Dim s As Long
  Dim rCount As Long
  Dim SecCnt As Long
  Dim m As Single
  Dim u As Range
  Dim CurSec As Long
  Dim TotaluSec As Long
  Dim RealSecCount As Long
  Dim AvgSec() As Double
  
  BegDate = DateSerial(2019, 3, 1)      'Needed a value, should come from the form
  EndDate = DateSerial(2019, 9, 6)      'Needed a value, should come from the form
  NumSections = 3                       'Needed a value, should come from the form
  
  ReDim AvgSec(1 To NumSections)        'Array of averages per section
  
  Set R = Range("A2:A1000")             'Need to make more dynamic
  
  'Count of dates that fall within the date range
  rCount = Application.CountIfs(R, ">" & BegDate - 1, R, "<" & EndDate + 1)
  SecCnt = rCount / NumSections                                             'Count per section
  m = (rCount Mod NumSections)                                              'remainder
  
  CurSec = 1
  s = 0
  If m > 0 Then                       'Add one to each of the first few ????
    RealSecCount = SecCnt + 1
    m = m - 1
  Else
    RealSecCount = SecCnt
  End If
  
  For Each Cel In R                   'Look at every cell
    s = s + 1
    CurDt = Cel.Value
    If s > RealSecCount Then          'into next section; summarize current section
      If Not u Is Nothing Then
        AvgSec(CurSec) = Application.Average(u)
      End If
      CurSec = CurSec + 1
      If CurSec > NumSections Then Exit For           'All done
      If m > 0 Then                                   'Load count for next section
        RealSecCount = SecCnt + 1
        m = m - 1
      Else
        RealSecCount = SecCnt
      End If
      If CurDt >= BegDate And CurDt <= EndDate Then 'the first value of the next section
        s = 1
        Set u = Cel.Offset(0, 1)
      Else
        s = 0
        Set u = Nothing
      End If
    Else
      If CurDt >= BegDate And CurDt <= EndDate Then     'Date falls into beg and end dates
        If Not u Is Nothing Then                        'Save range for average
          Set u = Union(u, Cel.Offset(0, 1))
        Else
          Set u = Cel.Offset(0, 1)
        End If
      End If
    End If
    
  Next Cel
  
  For X = 1 To NumSections                                'Display results
    MsgBox "Section " & X & " average is: " & AvgSec(X)
  Next X
    
End Sub

 

[Jeffrey Mahoney]

I didn't answer the question of needing the percent change from the first to the last section. Let me know if you need help with that. You should be able to add some variables to store the totals of section 1 and section 2 then divide them.

 

[DanteAmor]

With the following you have the data range of the first section and the last section according to your name "rYvalues"

For your comments in that range you have dates, then you need to define which column has the values ​​or where the values ​​are to average.
If you can give an example of your data or You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Private Sub CommandButton1_Click()
    Dim wValues As Range, wDate As Range
    Dim ini As String, fin As String, firstsection As String, lasttsection As String
    Dim section As Double, dif As Long
    
    Set wValues = Range("rYvalues")
    For Each wDate In wValues
        If wDate.Value = DTPicker1.Value Then ini = wDate.Address
        If wDate.Value = DTPicker2.Value Then fin = wDate.Address
    Next
    dif = Range(fin).Row - Range(ini).Row + 1
    section = Int(dif / Val(ComboBox1.Value))
    firstsection = Range(ini).Resize(section).Address
    lastsection = Range(fin).Offset(-section + 1).Resize(section).Address
    
    MsgBox "Range fisrtsection : " & firstsection & vbCr & vbCr & _
           "Range lastsection : " & lastsection
End Sub

 

[RawlinsCross]

Thanks for your replies gentlemen. I'm working through your codes and trying to get something together. I'll post the results. One question though. If I have a named range (i.e. rYValues) that is only a single column of values, how do I average say the first 7 positions?

 

[Jeffrey Mahoney]

In my code you would have to adapt a new range to look at using this line:

Set R = Range("A2:A1000")

You may want to specify on your form what range of cells you want to use.

 

[RawlinsCross]

Hi again Jeff,

I was more looking to index from the range. The named range is question is one dimensional - i.e. a single column of variable length (determined by the DTPicker values). But let's say for argument that the length of the range is 365 items. How could I average, say, items 45 - 100?