+ Reply to Thread
Results 1 to 7 of 7

How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then REPEAT

  1. #1
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Question How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then REPEAT

    I need some assistance in writing a formula.

    Input Condition:
    • Cells B3 downwards contains 2015.07.17 (date weekdays only, Monday-Friday, when there is a holiday on weekdays, it will not be included)
    • Cells C3 downwards contains 0:00:00 (time with a space 5 minutes until the end of the day, but not always to 23:55:00, could be less, then back again to 0:00:00 to the next day)
    • Cells D3 and E3 downwards contains random value (any positive value)


    Output (SOLVED):
    • Cells G3 downwards contains E3-$D$3 until the end of the day. If the next day is in B6 and 0:00:00 is in C6, then the formula will be E6-$D$6, and so on. Lets say the last input data is row 21, therefore the formula for cells G3 downwards:

      Please Login or Register  to view this content.
      This formula has been completed and in accordance with what I want.


    Output Problems:
    1. In cells H3 downwards, I want to make this formula:
      If C3<=3;55;00 and G3<=-0.00025, then "YES". Other than that, it will be "NO".
      The formula for this cells downwards will be:

      Please Login or Register  to view this content.
      The problem is, I want it to be more specific. Lets say the results for H3 is "YES" and H4 is "YES", both on the same day. I want to make H4 result become "NO" because H3 result already "YES".
      In other words: If first "YES" appears, then the next "YES" will be "NO" until the end of the day and repeat it every day.
      What formula should I add into this? or should I just add a new column?

    2. In cells I3 downwards, I want to make this formula:
      For cell I3, If H3 is "YES", then find the maximum value from G3 downwards until the end of the day. Other than that is "NO". Lets say the end of the day is in cell B5 or C5, so formula for this cells downwards in 1 day will be:

      Please Login or Register  to view this content.
      The problem is, If I use this formula, I must change the MAX formula for every new day because there is a fixed sign ($) for the MAX value. I want the formula for 1 day only and repeat it every day.
      What formula should I add into this? or should I just add a new column?


    This is a sample of my workbook:
    Attachment 456597

    I need the formula so I can copy down the formula without change the formula for every new day (there is more than 300.000 data).

    Thank you.

    -Jaka-
    Attached Files Attached Files
    Last edited by Jaka Phoenix; 04-20-2016 at 08:07 AM. Reason: upload my NEW sample workbook & change the Title

  2. #2
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 2000/3/7/10/13/16
    Posts
    51,807

    Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  3. #3
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

    Hi, thank you for your reply and for the information. Sorry for the inconvenience.

    I just uploaded my workbook sample. I hope my explanation is understandable and there is someone who can help me.

    Quote Originally Posted by FDibbins View Post
    Hi, welcome to the forum

    Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
    Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

    Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.
    Last edited by Jaka Phoenix; 04-18-2016 at 02:32 PM.

  4. #4
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

    Bump no response

  5. #5
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Re: Formula: Only “NO” after first “YES” appear in 1 day and REPEAT IT EVERY NEW DAY

    Hi guys, I just uploaded my NEW workbook sample and add more explanation in this post, I hope someone can help me with this problems. Thank you.

  6. #6
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Re: How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then RE

    Bump no response

  7. #7
    Registered User
    Join Date
    04-18-2016
    Location
    Indonesia
    MS-Off Ver
    2013
    Posts
    6

    Re: How to find the MAX or MIN value from a specific cell downwards in 1 DAY only, then RE

    Bump no response

+ 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. [SOLVED] Repeat formula X times - once X times has been achieved then Y - repeat X again
    By jonnym28 in forum Excel Formulas & Functions
    Replies: 14
    Last Post: 11-25-2015, 11:53 AM
  2. [SOLVED] Count Instances of Repeat and Non-Repeat Dates
    By mgs in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 10-08-2015, 09:54 PM
  3. Replies: 4
    Last Post: 03-08-2014, 08:01 AM
  4. Repeat same formula on new page
    By knapp in forum Excel General
    Replies: 6
    Last Post: 11-16-2011, 04:35 AM
  5. Repeat Formula
    By Mick2011 in forum Excel General
    Replies: 4
    Last Post: 10-05-2011, 10:35 PM
  6. Repeat formula
    By stanja in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 08-31-2009, 06:46 PM
  7. Replies: 2
    Last Post: 08-24-2005, 12:05 PM

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