+ Reply to Thread
Results 1 to 9 of 9

Counting days until threshold reached

  1. #1
    Registered User
    Join Date
    01-18-2022
    Location
    Miami, FL
    MS-Off Ver
    365
    Posts
    3

    Post Counting days until threshold reached

    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.
    Attached Files Attached Files

  2. #2
    Forum Expert KOKOSEK's Avatar
    Join Date
    08-03-2018
    Location
    Pole in Yorkshire, UK
    MS-Off Ver
    365/2013
    Posts
    2,742

    Re: Counting days until threshold reached

    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
    Attached Files Attached Files
    Last edited by KOKOSEK; 01-18-2022 at 11:17 AM.
    Happy with my answer * Add Reputation.
    If You are happy with solution, please use Thread tools and mark thread as SOLVED.

  3. #3
    Registered User
    Join Date
    01-18-2022
    Location
    Miami, FL
    MS-Off Ver
    365
    Posts
    3

    Re: Counting days until threshold reached

    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

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    Re: Counting days until threshold reached

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Columns I:K are populated using: =IF(E2="N/A","N/A",E2-$B2)
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    01-18-2022
    Location
    Miami, FL
    MS-Off Ver
    365
    Posts
    3

    Re: Counting days until threshold reached

    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?

  6. #6
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,144

    Re: Counting days until threshold reached

    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!
    Last edited by JohnTopley; 01-19-2022 at 05:38 AM.
    If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

  7. #7
    Registered User
    Join Date
    01-05-2022
    Location
    Indonesia
    MS-Off Ver
    Microsoft Office Professional Plus 2016
    Posts
    51

    Re: Counting days until threshold reached

    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
    Attached Files Attached Files
    Last edited by muhammadridho30; 01-19-2022 at 05:05 AM.

  8. #8
    Forum Expert
    Join Date
    05-05-2015
    Location
    UK
    MS-Off Ver
    Microsoft Excel for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20068) 64-bit
    Posts
    28,144

    Re: Counting days until threshold reached

    Please Login or Register  to view this content.
    Attached Files Attached Files

  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,522

    Re: Counting days until threshold reached

    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:
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 4
    Last Post: 12-12-2018, 12:34 AM
  2. Counting Times Threshold is Reached
    By Casval in forum Excel General
    Replies: 4
    Last Post: 02-04-2018, 05:37 AM
  3. Replies: 1
    Last Post: 11-30-2016, 08:02 PM
  4. [SOLVED] Move specific cells to a new sheet when a date is reached or 30 days from being reached
    By Albert Dirk in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 03-26-2016, 04:44 AM
  5. [SOLVED] Need to formulate trigger once a certain value or milestone or threshold has been reached
    By KaziProttoy in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-09-2015, 11:03 AM
  6. [SOLVED] Find first cell where threshold value is reached, return value from adjacent column
    By CatSqueezer in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 12-06-2013, 08:49 PM
  7. [SOLVED] Counting days till value is reached
    By Montoro22 in forum Excel General
    Replies: 11
    Last Post: 07-06-2012, 11:09 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1