Dividing a range into sections and performing calculations on each section

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
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.
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Active Member
Joined
Sep 9, 2016
Messages
332
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?
 
Last edited:

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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




Code:
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

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.

Code:
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

Active Member
Joined
Sep 9, 2016
Messages
332
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
In my code you would have to adapt a new range to look at using this line:

Code:
Set R = Range("A2:A1000")

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

RawlinsCross

Active Member
Joined
Sep 9, 2016
Messages
332
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top