sum of prior 12 threshold, now sum next 12

finnstone
finnstone used Ask the Experts™
on
need a formula to find the column where an array crosses a certain threshold

so need to find the column number where that happens

threshold is sum of prior 12 columns being greater than or equal to 232000, looking for the first time an array crosses that mark

note- an array is between columns H-CA

once it hits that sum threshold, NOWi need to sum the NEXt twelve columns of data.

so for example, if COL a -n = 190000, but columns b-M sum is greater than 232000has then this was first time we crossed threshold...and now i need sum of columns n-y
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
Commented:
The following formula checks each column from H through CA to see if the sum of the 12 preceding columns is greater than or equal to 232000. If so, if sums the following 12 columns. If this formula is not doing what you want, please post a sample worksheet and tell me what the result is supposed to be. The two parts of the formula where I add 2 and subtract 1 in the OFFSET are the parts that may need to be adjusted so that you look back and forwards to the desired columns.

As written, the formula is working on cells H1:CA1; I trust you can make the appropriate changes if your data are on a different row or different columns. The formula assumes that no columns are hidden by filters and that no values are negative. It also assumes that all the cells in row 1 are blank except for H1:CA1, and may give an unexpected result should they contain numbers.
=LOOKUP(1E40,SUBTOTAL(9,OFFSET(H1,0,COLUMN(H1:CA1)-COLUMN(H1)+2,1,12))/(SUBTOTAL(9,OFFSET(H1,0,COLUMN(H1:CA1)-COLUMN(H1)-1,1,-12))<232000))

Open in new window

The attached test workbook has a trigger sum of 300 instead of 232000. It includes tabulated values for the sum of the preceding 12 columns and the subsequent 12 columns so I could dial in the formula (the +2 and -1 mentioned in the first paragraph).
RunningSumTestingQ29115305.xlsm
byundtMechanical Engineer
Most Valuable Expert 2013
Top Expert 2013

Commented:
If you are willing to employ a user defined function (i.e. VBA), then you can make the formula much simpler. I wrote the UDF so it would only look at cells in a specified range of cells, with the look ahead and look back number of cells being user specified. I also included an optional Boolean input so the pivot cell could either be included or excluded from the sums. The UDF removes the restrictions that the other cells in the row being summed not include numeric values and that the cells being summed not contain negative numbers.
=RunningSum(H1:CA1,12,12,232000)        'Pivot cell excluded
=RunningSum(H1:CA1,12,12,232000,TRUE)         'Pivot cell included

Open in new window


Put the code in a regular module sheet (just like a recorded macro).
Function RunningSum(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 sum of LookAhead number of cells.
'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)
RunningSum = "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 = 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

Open in new window

RunningSumTestingQ29115305.xlsm

Author

Commented:
awesome

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial