sum of prior 12 threshold, now sum next 12

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
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:
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
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
byundtMechanical EngineerCommented:
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
0
finnstoneAuthor Commented:
awesome
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.