SUM last N-entries until certain value is reached and then find earliest date from that range plus 180 days

ps450 · Nov 28, 2017

I have two columns. Column A consists of dates and column C consists of numbers. I need to SUM last entries in column C until they reach value >=6, then find earliest date from that range and add 180 days to it. Is it possible to do all these calculations in Excel using FORMULA (not VBA)? Thank you so much to all who respond and help me to find solution!

The correct response would be: "08/16/17".

steve the fish · Nov 28, 2017

Simple to do with a helper column. Can you use one? If you can you could put:

=SUM($C1:C$100)

in D1 and copy down as far as required then the formula is:

=180+LOOKUP(2,1/(D1:D100=6),A1:A100)

Eidt ive just seen greater than or equal to 6:

=180+LOOKUP(2,1/(D1:D100>=6),A1:A100)

ps450 · Nov 28, 2017

Thanks for response! Helper column is not an option.

Aladin Akyurek · Nov 28, 2017

Care to post the sample in an Excel readable form directly here?

Eric W · Nov 28, 2017

Welcome to the board. Try:

Code:

=IFERROR(INDEX(A:A,LARGE(IF(SUBTOTAL(9,OFFSET($C$1,ROW($C$1:$C$13)-ROW($C$1),0,ROW($C$13)-ROW($C$1:$C$13)+1))>=6,ROW($C$1:$C$13)),1))+180,"Sum not met")

Confirmed with Control+Shift+Enter.

Eric W · Nov 28, 2017

I'm not sure which would be more efficient, but I found a somewhat shorter formula using MMULT:

Code:

=IFERROR(INDEX(A:A,LARGE(IF(MMULT(IF(ROW(C1:C13)<=TRANSPOSE(ROW(C1:C13)),1,0),C1:C13+0)>=6,ROW(C1:C13)),1))+180,"Sum not met")

also entered with CSE.

ps450 · Nov 28, 2017

Uploaded sample here: https://drive.google.com/file/d/1t6LKvSfaMZ8RuS56s4CHZ04ivI7Dl1r6/view?usp=sharing

ps450 · Nov 28, 2017

Eric thank you! Both formulas are working until I add more entries. Columns A and C could be filled up all the way down.

Eric W · Nov 28, 2017

Both formulas require that you change the ranges to match your sheet, including putting the bottom row (or the maximum expected row). I did some rough timing, and the MMULT formula is about 5% faster, but it uses more internal resources, meaning it would stop working if the range is too big. The SUBTOTAL formula should work for any size range, but it too could start going very slow if your range is too large.

Possibly someone could come up with a better formula, but you might be better served with a UDF (User-Defined Function). Try this:

Open a copy of your workbook. On the sheet with your data, right click on the sheet tab on the bottom and select View Code. From the VBA Editor menu, select Insert > Module. Paste the following code into the window that opens:

Code:

Public Function AddDate(MyDates As Range, MyVals As Range, target As Long)
Dim r As Long, MySum As Double
    
    If MyDates.Columns.Count > 1 Or MyVals.Columns.Count > 1 Then
        AddDate = "One or both of the ranges are > 1 column"
        Exit Function
    End If
    
    If MyDates.Rows.Count <> MyVals.Rows.Count Then
        AddDate = "Ranges are different sizes."
        Exit Function
    End If
    
    MySum = 0
    For r = Cells(Rows.Count, MyVals.Column).End(xlUp).Row To MyVals.Row Step -1
        MySum = MySum + MyVals(r, 1)
        If MySum >= target Then
            AddDate = MyDates(r, 1) + 180
            Exit Function
        End If
    Next r
    
    AddDate = "Sum not met"
    
End Function

Close the VBA editor (Alt-Q, or the red X). On your worksheet, enter this formula:

=AddDate(A1:A13,C1:C13,6)

You can use larger references

=AddDate(A1:A13000,C1:C13000,6)

or even whole column references:

=AddDate(A:A,C:C,6)

Let us know how this works.

Aladin Akyurek · Nov 29, 2017

If you don't want VBA, try the following.

Define Dates in the Name Manager as referring to:

=Sheet1!$A$1:INDEX(Sheet1!$A:$A,MATCH(9.99999999999999E+307,Sheet1!$C:$C))

Define Values in the Name Manager as referring to:

=Sheet1!$C$1:INDEX(Sheet1!$C:$C,MATCH(9.99999999999999E+307,Sheet1!$C:$C))

The formulas Eric suggests become...

Control+shift+enter:

=IFERROR(INDEX(Dates,LARGE(IF(SUBTOTAL(9,OFFSET(INDEX(Values,1,1),ROW(Values)-ROW(INDEX(Values,1,1)),0,ROW(INDEX(Values,ROWS(Values),1))-ROW(Values)+1))>=6,ROW(Values)-ROW(INDEX(Values,1,1))+1),1))+180,"Sum not met")

Control+shift+enter:

=IFERROR(INDEX(Dates,LARGE(IF(MMULT(IF(ROW(Dates)-ROW(INDEX(Dates,1,1))+1<=TRANSPOSE(ROW(Dates)-ROW(INDEX(Dates,1,1))+1),1,0),Values+0)>=6,ROW(Dates)-ROW(INDEX(Dates,1,1))+1),1))+180,"Sum not met")

If you also define Ivec in the Name Manager as referring to:

=ROW(Dates)-ROW(INDEX(Dates,1,1))+1

the latter formula becomes even nicer:

=IFERROR(INDEX(Dates,LARGE(IF(MMULT(IF(Ivec<=TRANSPOSE(Ivec),1,0),Values+0)>=6,Ivec),1))+180,"Sum not met")

SUM last N-entries until certain value is reached and then find earliest date from that range plus 180 days

ps450

New Member

Excel Facts

steve the fish

Well-known Member

ps450

New Member

Aladin Akyurek

MrExcel MVP

Eric W

MrExcel MVP

Eric W

MrExcel MVP

ps450

New Member

ps450

New Member

Eric W

MrExcel MVP

Aladin Akyurek

MrExcel MVP

Similar threads

Forum statistics

Share this page

SUM last N-entries until certain value is reached and then find earliest date from that range plus 180 days

New Member

Excel Facts

Well-known Member

New Member

MrExcel MVP

MrExcel MVP

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock