+ Reply to Thread
Results 1 to 11 of 11

Find Lowest Value between 6 months of a date

  1. #1
    Registered User
    Join Date
    05-29-2019
    Location
    London
    MS-Off Ver
    MS 365 Subscription
    Posts
    14

    Find Lowest Value between 6 months of a date

    Dear All,

    Hello - I am stuck trying to work out way a finding the lowest value between +6 / -6 months of a date.

    for example:

    Column A contains invoice dates 10K lines of data.
    Column B contains unit prices

    I am trying to find what the lowest unit price has been 6 months + and - from a specifc date in column A
    ie Cell ref A15 is date (1-Sep-xx) I want to find what the lowest price is (Column B) from 6 months prior to and from the date in A15???


    Help cant face doing this manually?

    thank you

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Find Lowest Value between 6 months of a date

    Here is the syntax for the MINIF function Which is available in 2016 and later.

    =MINIFS (min_range, range1, criteria1, [range2], [criteria2], ...)

    Arguments

    min_range - Range of values used to determine minimum.
    range1 - The first range to evaluate.
    criteria1 - The criteria to use on range1.
    range2 - [optional] The second range to evaluate.
    criteria2 - [optional] The criteria to use on range2.

    https://exceljet.net/excel-functions...inifs-function
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Find Lowest Value between 6 months of a date

    Welcome to the forum!

    So you need to use EDATE to calculate your date criteria:

    =EDATE(A15,-6) will give six months before
    =EDATE(A15,6) will give six months after

    Hopefully you can now use these extract the results you want. If not, then attach a workbook.
    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.
    You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.

    Forum Rules (updated August 2023): please read them here.

  4. #4
    Registered User
    Join Date
    05-29-2019
    Location
    London
    MS-Off Ver
    MS 365 Subscription
    Posts
    14

    Re: Find Lowest Value between 6 months of a date

    I know I am being think how do I attached workbook ?

    so so so sorry?

  5. #5
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Find Lowest Value between 6 months of a date

    Unfortunately the attachment icon doesn't work at the moment, so to attach an Excel file you have to do the following: just before posting, scroll down to Go Advanced and then scroll down to Manage Attachments. Now follow the instructions at the top of that screen.

  6. #6
    Registered User
    Join Date
    05-29-2019
    Location
    London
    MS-Off Ver
    MS 365 Subscription
    Posts
    14

    Re: Find Lowest Value between 6 months of a date

    is it attached now?
    Attached Files Attached Files

  7. #7
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Find Lowest Value between 6 months of a date

    I have modified your sheet slightly per the attached and then built the formula as shown
    Data Range
    A
    B
    C
    D
    E
    F
    G
    H
    I
    1
    Buyer
    2. Sale To Bagitsellit
    2
    Start
    41894
    3
    End
    =B2+180
    4
    5
    6
    7
    Sales To NT
    Date
    Invoice number
    Agent
    Type
    Quantity
    Total Value
    Unit Price
    Lowest Unit Price
    8
    1. Sale 3rd Party
    41862
    0000157374
    002F
    Sales Order
    7
    23.1
    =G8/F8
    =MINIFS(H8:H702,A8:A702,B1,B8:B702,">"&B2,B8:B702,"<"&B3)
    9
    1. Sale 3rd Party
    41863
    0000157424
    003F
    Sales Order
    7
    23.1
    =G9/F9
    10
    1. Sale 3rd Party
    41863
    0000157425
    003F
    Sales Order
    7
    23.1
    =G10/F10
    11
    1. Sale 3rd Party
    41871
    0000157599
    002F
    Sales Order
    7
    23.1
    =G11/F11
    12
    1. Sale 3rd Party
    41872
    0000157654
    199F
    Sales Order
    6
    18.48
    =G12/F12

    Note: the copy paste program I am using to display this changes the dates to numbers. It is just a formatting issue.
    Last edited by alansidman; 05-29-2019 at 12:22 PM.

  8. #8
    Forum Moderator jeffreybrown's Avatar
    Join Date
    02-19-2009
    Location
    Cibolo, TX
    MS-Off Ver
    Office 365
    Posts
    10,316

    Re: Find Lowest Value between 6 months of a date

    Hi Alan,

    It seems MINIFS is only in 2019.
    HTH
    Regards, Jeff

  9. #9
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Find Lowest Value between 6 months of a date

    And Office 365.

  10. #10
    Registered User
    Join Date
    05-29-2019
    Location
    London
    MS-Off Ver
    MS 365 Subscription
    Posts
    14

    Re: Find Lowest Value between 6 months of a date

    to everyone thank you some much.

    I do have a question - I think I may have confused matters in the excel by refer to the blue cells, I wanted to do it for all sales?


    What do I change?

  11. #11
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Retired in Ipswich, Suffolk, but grew up in Sawley, Derbyshire (England)
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel v. 2404 (Windows 11 22H2 64-bit)
    Posts
    79,323

    Re: Find Lowest Value between 6 months of a date

    Remove the limiting criterion:

    =MINIFS(H8:H702,B8:B702,">"&B2,B8:B702,"<"&B3)

+ 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] how to find the lowest to highest value and display the name who is the lowest and the hig
    By jasond1992 in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 09-23-2015, 09:20 PM
  2. Find date which is 6 months back from todays date.
    By aman1234 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 01-27-2015, 07:41 AM
  3. Replies: 4
    Last Post: 07-06-2013, 04:44 PM
  4. [SOLVED] Find the lowest date value from WS names
    By jordan2322 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-15-2012, 07:13 PM
  5. Replies: 2
    Last Post: 01-04-2012, 09:15 AM
  6. Need to find lowest date after today's date
    By jerpenny in forum Excel General
    Replies: 7
    Last Post: 09-21-2010, 02:02 PM
  7. Find lowest number in groups then lowest overall.
    By swieduwi in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 11-24-2008, 01:00 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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1