Count columns after hitting a SUMMING threshold

continutation.

this time, i need to count the number of cells AFTER the sum of the prior 12 has reached a threshold. last time it was just counting cells after a single cell hit a threshold.

so once the prior 12 cells sum is greater than 232,000, then i need to count the number of subsequent cells with data in them.

https://www.experts-exchange.com/questions/29115304/Count-columns-after-hitting-a-threshold.html
finnstoneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

byundtMechanical EngineerCommented:
I revised the RunningSum function so it will count the number of columns that contain non-zero values after a target sum is reached. You use the new CountAfterRunningSum function with a formula like:
=CountAfterRunningSum(H1:CA1,12,0,232000,FALSE)              'Counts all remaining columns
=CountAfterRunningSum(H1:CA1,12,12,232000,FALSE)             'Counts only next 12 columns

Open in new window


The code is:
Function CountAfterRunningSum(rgToSum As Range, LookBack As Long, LookAhead As Long, TargetValue As Double, Optional bIncludePivotCell As Boolean = False) As Variant
'Calculates the various sums of LookBack number of values in rgToSum. When one of those sums exceeds TargetValue, then returns number of LookAhead columns with non-zero values.
'If LookAhead is 0, then all remaining columns in rgToSum are included in the count
'If bIncludePivotCell is False, it is not included in either the looking back or looking forward sums.
'If bIncludePivotCell is True, it is included in both the looking back and looking forward sums
Dim cel As Range, Finish As Range, rgBack As Range, rgAhead As Range, Start As Range
Dim dLookBack As Double, dLookAhead As Double
Dim i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
CountAfterRunningSum = "Failed"

For Each cel In rgToSum.Cells
    Set rgBack = Nothing
    Set rgAhead = Nothing
    dLookBack = 0
    dLookAhead = 0
    
    If bIncludePivotCell = True Then
        Set rgBack = cel
        Set rgAhead = cel
    Else
        If Not cel.Address = Start.Address Then Set rgBack = cel.Offset(0, -1)
        If Not cel.Address = Finish.Address Then Set rgAhead = cel.Offset(0, 1)
    End If
    
    If Not rgBack Is Nothing Then
        i = Application.Max(Start.Column, rgBack.Column - LookBack + 1)
        Set rgBack = Range(rgBack, rgBack.EntireRow.Cells(1, i))
        dLookBack = Application.Sum(rgBack)
    End If

    If Not rgAhead Is Nothing Then
        j = IIf(LookAhead = 0, Finish.Column, Application.Min(Finish.Column, rgAhead.Column + LookAhead - 1))
        Set rgAhead = Range(rgAhead, rgAhead.EntireRow.Cells(1, j))
        dLookAhead = Application.CountIf(rgAhead, "<>0")
    End If
    
    If dLookBack >= TargetValue Then
        CountAfterRunningSum = dLookAhead
        Exit Function
    End If
Next
End Function

Open in new window

RunningSumTestingQ29115305.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
finnstoneAuthor Commented:
thanks!
0
finnstoneAuthor Commented:
the count seems to be short by 1 value every time, i.e., 3 instead of 4?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

byundtMechanical EngineerCommented:
I revised the code in both user defined functions so you would have the option to include the pivot cell in the lookback sum, but not the range of cells looking ahead. To invoke that option, use 2 as the last parameter in each call.
=RunningSum(H1:CA1,12,12,215000,2)
=CountAfterRunningSum(H1:CA1,12,0,215000,2)

Open in new window

The revised code is shown below:
Function RunningSum(rgToSum As Range, LookBack As Long, LookAhead As Long, TargetValue As Double, Optional IncludePivotCell As Long = 2) As Variant
'Calculates the various sums of LookBack number of values in rgToSum. When one of those sums exceeds TargetValue, then returns sum of LookAhead number of cells.
'If IncludePivotCell is 0, it is not included in either the looking back sum or looking forward sum.
'If IncludePivotCell is 1, it is included in both the looking back sum and looking forward sum
'If IncludePivotCell is 2, it is included in the looking back sum but not the looking forward sum
Dim cel As Range, Finish As Range, rgBack As Range, rgAhead As Range, Start As Range
Dim dLookBack As Double, dLookAhead As Double
Dim StartAddr As String, FinishAddr As String
Dim i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
StartAddr = Start.Address
FinishAddr = Finish.Address
RunningSum = "Failed"

For Each cel In rgToSum.Cells
    Set rgBack = Nothing
    Set rgAhead = Nothing
    dLookBack = 0
    dLookAhead = 0
    
    Select Case IncludePivotCell
    Case 0
        If Not cel.Address = StartAddr Then Set rgBack = cel.Offset(0, -1)
        If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
    Case 1
        Set rgBack = cel
        Set rgAhead = cel
    Case 2
        Set rgBack = cel
        If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
    End Select
    
    If Not rgBack Is Nothing Then
        i = Application.Max(Start.Column, rgBack.Column - LookBack + 1)
        Set rgBack = Range(rgBack, rgBack.EntireRow.Cells(1, i))
        dLookBack = Application.Sum(rgBack)
    End If

    If Not rgAhead Is Nothing Then
        j = Application.Min(Finish.Column, rgAhead.Column + LookAhead - 1)
        Set rgAhead = Range(rgAhead, rgAhead.EntireRow.Cells(1, j))
        dLookAhead = Application.Sum(rgAhead)
    End If
    
    If dLookBack >= TargetValue Then
        RunningSum = dLookAhead
        Exit Function
    End If
Next
End Function

Function CountAfterRunningSum(rgToSum As Range, LookBack As Long, LookAhead As Long, TargetValue As Double, Optional IncludePivotCell As Long = 2) As Variant
'Calculates the various sums of LookBack number of values in rgToSum. When one of those sums exceeds TargetValue, then returns number of LookAhead columns with non-zero values.
'If LookAhead is 0, then all remaining columns in rgToSum are included in the count
'If IncludePivotCell is 0, it is not included in either the looking back sum or looking forward count.
'If IncludePivotCell is 1, it is included in both the looking back sum and looking forward count
'If IncludePivotCell is 2, it is included in the looking back sum but not the looking forward count
Dim cel As Range, Finish As Range, rgBack As Range, rgAhead As Range, Start As Range
Dim dLookBack As Double, dLookAhead As Double
Dim StartAddr As String, FinishAddr As String
Dim i As Long, j As Long
Set Start = rgToSum.Cells(1)
Set Finish = rgToSum.Cells(rgToSum.Cells.Count)
StartAddr = Start.Address
FinishAddr = Finish.Address
CountAfterRunningSum = "Failed"

For Each cel In rgToSum.Cells
    Set rgBack = Nothing
    Set rgAhead = Nothing
    dLookBack = 0
    dLookAhead = 0
    
    Select Case IncludePivotCell
    Case 0
        If Not cel.Address = StartAddr Then Set rgBack = cel.Offset(0, -1)
        If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
    Case 1
        Set rgBack = cel
        Set rgAhead = cel
    Case 2
        Set rgBack = cel
        If Not cel.Address = FinishAddr Then Set rgAhead = cel.Offset(0, 1)
    End Select
    
    If Not rgBack Is Nothing Then
        i = Application.Max(Start.Column, rgBack.Column - LookBack + 1)
        Set rgBack = Range(rgBack, rgBack.EntireRow.Cells(1, i))
        dLookBack = Application.Sum(rgBack)
    End If

    If Not rgAhead Is Nothing Then
        j = IIf(LookAhead = 0, Finish.Column, Application.Min(Finish.Column, rgAhead.Column + LookAhead - 1))
        Set rgAhead = Range(rgAhead, rgAhead.EntireRow.Cells(1, j))
        dLookAhead = Application.CountIf(rgAhead, "<>0")
    End If
    
    If dLookBack >= TargetValue Then
        CountAfterRunningSum = dLookAhead
        Exit Function
    End If
Next
End Function

Open in new window

RunningSumTestingQ29115305-SUMMING-.xlsm
0
finnstoneAuthor Commented:
thanks! emergency help delivered. can you take a look at the same problem for the summing too-error in that.
0
finnstoneAuthor Commented:
YOU GOT IT , THANKS!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.