+ Reply to Thread
Results 1 to 6 of 6

Max & Min with Relevant date

Hybrid View

  1. 10-22-2021, 03:22 AM #1
    Garth Vader
    Garth Vader is offline
    Registered User
    Join Date
    01-26-2021
    Location
    Johannesburg, South Africa
    MS-Off Ver
    MS 365 Subscription Product (MSO) 64 bit
    Posts
    18

    Max & Min with Relevant date

    Hello,

    Attached is a tiny shred of data from a very big file.

    How do I get the relevant date to its Max or Min value?
    I know how to use Max and Min but am not sure how to get the values corresponding date.
    I dont think an =Left would work and am not sure how an =IF would apply.

    Thank you,
    Garth
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 10-22-2021, 03:26 AM #2
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: Max & Min with Relevant date

    Hi
    see attached

    Mod Edit (for anyone unable to download an attachment):

    AliGW on MS365 Insider (Windows) 32 bit
    B
    C
    D
    E
    F
    G
    4
    07-Oct
    3.09397
    		 Max
    3.37781
    08/10/2021
    5
    08-Oct
    3.37781
    		 Min
    1.17924
    17/10/2021
    6
    09-Oct
    2.45621
    7
    10-Oct
    2.10178
    8
    11-Oct
    1.97492
    9
    12-Oct
    1.9508
    10
    13-Oct
    2.04323
    11
    14-Oct
    2.02135
    12
    15-Oct
    1.86119
    13
    16-Oct
    1.67673
    14
    17-Oct
    1.17924
    15
    18-Oct
    1.30634
    16
    19-Oct
    1.59658
    17
    20-Oct
    1.55205
    Sheet: Scrubbed

    AliGW on MS365 Insider (Windows) 32 bit
    F
    4
    =MAX(C4:C17)
    5
    =MIN(C4:C17)
    Sheet: Scrubbed

    AliGW on MS365 Insider (Windows) 32 bit
    G
    4
    =INDEX($B$4:$B$17,MATCH(MAX($C$4:$C$17),$C$4:$C$17,0))
    5
    =INDEX($B$4:$B$17,MATCH(MIN($C$4:$C$17),$C$4:$C$17,0))
    Sheet: Scrubbed
    Attached Files Attached Files
    Last edited by AliGW; 10-22-2021 at 03:59 AM.
    Reply With QuoteRegister To Reply
  3. 10-22-2021, 03:48 AM #3
    Garth Vader
    Garth Vader is offline
    Registered User
    Join Date
    01-26-2021
    Location
    Johannesburg, South Africa
    MS-Off Ver
    MS 365 Subscription Product (MSO) 64 bit
    Posts
    18

    Re: Max & Min with Relevant date

    Thank you Pepe, that is very helpful
    Reply With QuoteRegister To Reply
  4. 10-22-2021, 03:32 AM #4
    AliGW
    AliGW is online now
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (both in England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2503 (Windows 11 Home 24H2 64-bit)
    Posts
    90,516

    Re: Max & Min with Relevant date

    Sorry for off-topic interjection:

    Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

    I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.
    Ali

    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" in your thread to anyone who has offered you help. It's a universal courtesy.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
    NB:     as a Moderator, I never accept friendship requests.
    Forum Rules (updated August 2023): please read them here.
    Reply With QuoteRegister To Reply
  5. 10-22-2021, 03:54 AM #5
    Pepe Le Mokko
    Pepe Le Mokko is offline
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,610

    Re: Max & Min with Relevant date

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as a relatively new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
    Reply With QuoteRegister To Reply
  6. 10-22-2021, 04:01 AM #6
    Garth Vader
    Garth Vader is offline
    Registered User
    Join Date
    01-26-2021
    Location
    Johannesburg, South Africa
    MS-Off Ver
    MS 365 Subscription Product (MSO) 64 bit
    Posts
    18

    Re: Max & Min with Relevant date

    Thank you for the follow up, I wasn't aware I had to mark it as solved, I added a reputation also.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Searching for the most recent (date) relevant entry in a table of data
    By mlewhe in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-26-2020, 09:13 PM
  2. LOOKUP date and return relevant column header, where columns are non-contigious.
    By jayjaynz in forum Excel Formulas & Functions
    Replies: 11
    Last Post: 03-07-2020, 01:26 AM
  3. Looking To Sort Data To Separate Worksheets Based On Date
    By jsmith2018 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-04-2018, 04:02 PM
  4. Enter one value in relevant cell based on the adjacent date
    By SubwAy in forum Excel Programming / VBA / Macros
    Replies: 11
    Last Post: 09-20-2016, 05:06 AM
  5. Cash Flow and allocation of payments to the relevant week ending date
    By Gary Brown in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-15-2016, 06:52 PM
  6. [SOLVED] Creating a formula to lookup a cell and return date relevant to period
    By Marcel Coetzee in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 08-13-2013, 11:48 PM
  7. [SOLVED] Read today's date and pull relevant data
    By benjuk in forum Excel Formulas & Functions
    Replies: 15
    Last Post: 06-24-2013, 05:14 PM

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1