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".

ps450 · Nov 29, 2017

Eric, thank you so much! The UDF solution works perfectly!

What would be better to use UDF solution or Formula if I have the range about 20,000 entries? How many entries would be optimal for performance?

Eric, would it be difficult to slightly modify the code? I need to change 180 days to 6 calendar months, and add them after the month of the earliest day. In my example, the earliest day falls on 02/17/17, I need to add 6 calendar months after the February. The correct response should be 08/31/17.

I'm creating Excel Pilot Logbook. And I'm trying to find a formula to track Instrument Currency. That was the most difficult part as I do not have deep knowledge of Excel.

The original requirement for Instrument experience:

(c)Instrument experience. Except as provided in paragraph (e) of this section, a person may act as pilot in command under IFR or weather conditions less than the minimums prescribed for VFR only if:

(1)Use of an airplane, powered-lift, helicopter, or airship for maintaining instrument experience. Within the 6 calendar months preceding the month of the flight, that person performed and logged at least:

(i) Six instrument approaches.
(ii) Holding procedures and tasks.
(iii) Intercepting and tracking courses through the use of navigational electronic systems.

ps450 · Nov 29, 2017

Aladin, thank you so much! Your solution also works perfectly! I do not really know which solution to use Yours or Eric's. What would you recommend?

I also want to ask you to slightly modify the formula. I need to change 180 days to 6 calendar months, and add them after the month of the earliest day. In my example, the earliest day falls on 02/17/17, I need to add 6 calendar months after the February. The correct response should be 08/31/17.

Eric W · Nov 29, 2017

I haven't thoroughly tested the formulas or UDF for performance, since I don't have your data to work with. However, my gut feeling is, if you stick with the formulas, you should go with Aladin's versions. The additions he made with the Name Manager made them more efficient. Also, I'd tend to stick with the SUBTOTAL version. In limited testing, I found that the MMULT version starting giving suspect results between 10,000 and 100,000 rows. The SUBTOTAL version worked well past 100,000 rows. If you want to change the formula so that instead of 180 days, it shows the End-of-month 6 months later, do this:

=IFERROR(EOMONTH(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)),6),"Sum not met")

And finally, if you want to use the UDF, which I suspect would be most efficient on large data sets, you just need to change this line:

Code:

AddDate = MyDates(r, 1) + 180

to

Code:

AddDate = WorksheetFunction.EoMonth(MyDates(r, 1), 6)

Glad we could help!

ps450 · Nov 30, 2017

Eric, both formula and UDF work nicely!
Is it possible to make both Formula and UDF to output also "Sum not met" if the Output Date is less than Today? See uploaded sample.

https://drive.google.com/file/d/1Yl1Z_tijvQkXeXibxjBVbUxXbMR56HIS/view?usp=sharing

Aladin Akyurek · Nov 30, 2017

ps450 said:
Eric, both formula and UDF work nicely!
Is it possible to make both Formula and UDF to output also "Sum not met" if the Output Date is less than Today? See uploaded sample.

https://drive.google.com/file/d/1Yl1Z_tijvQkXeXibxjBVbUxXbMR56HIS/view?usp=sharing

In I2 enter:

=IF(ISNUMBER(G1), IF(G1 < TODAY(), "NOT CURRENT", G1), G1)

That means you need to keep G1 as is.

Eric W · Nov 30, 2017

If you want to stick with the formula, use Aladin's update in I2. It would be too messy to try to incorporate your additional requirement into the original formula.

If you want to update the UDF, just add the line in red:

Rich (BB code):

. . .
            AddDate = WorksheetFunction.EoMonth(MyDates(r, 1), 6)
            If AddDate < Date Then AddDate = "Sum not met"
            Exit Function
. . .

Glad we could help!

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

ps450

New Member

ps450

New Member

Excel Facts

ps450

New Member

Eric W

MrExcel MVP

ps450

New Member

Aladin Akyurek

MrExcel MVP

Eric W

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

ps450

New Member

ps450

New Member

Excel Facts

ps450

New Member

Eric W

MrExcel MVP

ps450

New Member

Aladin Akyurek

MrExcel MVP

Eric W

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