Counting days until threshold reached

[larynx]

Hi!

I have the following problem, when calculating stats for agronomical uses. I need to know what is the date when a certain crop reaches maturity based on temperature sums. I have attached a table below.
Thanks.

[KOKOSEK]

I am not sure that I understand correctly:

Is it properly marked (in post you've said 'what is the date')

	A	B	C	D	E	F	G
1	Plant	date	rain	TEMP	200	300	400
2	A	01/01/2018	0.50	8.0
3	A	02/01/2018	2.20	2.0
4	A	03/01/2018	1.10	9.0
5	A	04/01/2018	0.25	55.0
6	A	05/01/2018	0.33	4.0
7	A	06/01/2018	2.20	15.0
8	A	07/01/2018	1.10	23.0
9	A	08/01/2018	2.20	32.0
10	A	09/01/2018	1.10	31.4
11	A	10/01/2018	2.20	34.2	X
12	A	11/01/2018	1.10	37.1
13	A	12/01/2018	0.25	39.9
14	A	13/01/2018	4.30	42.8		X
15	A	14/01/2018	2.23	45.6
16	A	15/01/2018	2.35	48.5			X
17	A	16/01/2018	2.46	51.4
18	A	17/01/2018	2.57	54.2
19	A	18/01/2018	2.68	57.1
20	A	19/01/2018	2.80	59.9
21	A	20/01/2018	2.91	62.8
22	A	21/01/2018	3.02	65.6
23	A	22/01/2018	3.13	68.5

or you want to know how many days passed (as in thread title is 'counting') from 'initial date' in particular date like:

	A	B	C	D	E	F	G
10	A	09/01/2018	1.10	31.4
11	A	10/01/2018	2.20	34.2	9
12	A	11/01/2018	1.10	37.1
13	A	12/01/2018	0.25	39.9
14	A	13/01/2018	4.30	42.8		12
15	A	14/01/2018	2.23	45.6
16	A	15/01/2018	2.35	48.5			14

[larynx]

Hi Kokosek,

Thank you for your reply. This is closing to what am I was looking for, but, I need a rolling kind of variable formula in the columns 200,300,400 because if I sow crop A on 15/01/2018, it reaches maturity on 18/1/2018 since it is passing the 200 threshold. Hope this helps to get closer to the answer.

Thanks once again.
Regards,
Peter

[JeteMc]

I am a bit confused myself as to whether the request is for the date or the number of days, so I put dates in columns E:G and number of days in columns I:K.
Column H is a helper column populated using: =SUMIFS(D$2:D2,A$2:A2,A2)
Columns E:G are populated using:

=IFERROR(INDEX($B$2:$B$82,MATCH(INDEX($H$2:$H$82,AGGREGATE(15,6,(ROW($H$2:$H$82)-ROW($H$1))/($A$2:$A$82=$A2)/($H$2:$H$82-IF($A1=$A2,$H1,0)>=E$1),1)),$H$2:$H$82,0)),"N/A")

Columns I:K are populated using: =IF(E2="N/A","N/A",E2-$B2)
Let us know if you have any questions.

[larynx]

Thank you! This is the closest to what am I was looking for. My original excel has over 118k rows and using these formulas crashes my pc. Do you know any solutions/workarounds for that?

[JohnTopley]

I think you will need VBA for this, not least because you need a "moving target" of each threshold (200,300,400) for each starting date i.e column H in JeteMc's file repeated 27 times (number of unique dates) _ or have I mis-understood ???

with 118K rows the number dates is going to considerably more than 27!

[muhammadridho30]

I need more explanation about what to do on rain and temp? or like 200/300/400-(rain or temp) every day until the end of the list then get the row of the first negative value?

Edit : I try as far as I understand, i am sorry if it doesn't meet the request

[JohnTopley]

Sub get_dates()


Dim i As Long, j As Long, k As Long, l As Long, m As Long, n As Long, r As Long, c As Long, n1 As Long, n2 As Long
Dim lastrow As Long, Lastcol As Long
Dim inarr, outarr

Dim ws1 As Worksheet, ws2 As Worksheet, ws3 As Worksheet

'Optimize Macro Speed
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  'Application.Calculation = xlCalculationManual

Set ws1 = Worksheets("Sheet1")
ws1.Activate

With ws1

    lastrow = .Cells(Rows.Count, 1).End(xlUp).Row
    Lastcol = .Cells(1, Columns.Count).End(xlToLeft).Column
    
    inarr = .Range(.Cells(2, 1), .Cells(lastrow, Lastcol))
    
    ReDim outarr(1 To UBound(inarr, 1), 1 To 3)
    
    Crop = inarr(1, 1)
    c = 1
    
    For l = 200 To 400 Step 100
        
        For r = 1 To UBound(inarr, 1)
        
            If inarr(r, 1) <> Crop Then Crop = inarr(r, 1)
               tsum = 0
               j = r
               Do While tsum < l
                 If inarr(j, 1) <> Crop Then GoTo nextr
                  tsum = tsum + inarr(j, 4)
                  j = j + 1
                  If j > UBound(inarr, 1) Then
                    If tsum >= l Then outarr(r, c) = WorksheetFunction.Max(inarr(j - 1, 2) - inarr(r, 2) + 1, 0)
                    GoTo nextr
                  End If
               Loop
               outarr(r, c) = WorksheetFunction.Max(inarr(j - 1, 2) - inarr(r, 2) + 1, 0)
nextr:
        Next r
     c = c + 1
    Next l

End With

ws1.Range("E2").Resize(UBound(outarr, 1), UBound(outarr, 2)) = outarr

'Reset Macro Optimization Settings
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True


End Sub

[JeteMc]

I feel that John has come up with the better solution.
However, in the interest of responding to post #5:
If you only want to know the number of days then modify the formula in E2 so that it reads:

=IFERROR(INDEX($B$2:$B$82,MATCH(INDEX($H$2:$H$82,AGGREGATE(15,6,(ROW($H$2:$H$82)-ROW($H$1))/($A$2:$A$82=$A2)/($H$2:$H$82-IF($A1=$A2,$H1,0)>=E$1),1)),$H$2:$H$82,0))-$B2,"N/A")

If you don't need to see the three thresholds at one time put a drop down in cell E1 with the list 200, 300, 400.
At this point only two columns of calculation, E and H, are needed.
Let us know if you have any questions.