+ Reply to Thread
Results 1 to 9 of 9

find date in colum A, if value in column B is within interval

  1. #1
    Registered User
    Join Date
    06-20-2011
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2003
    Posts
    7

    find date in colum A, if value in column B is within interval

    I have one column with dates (Column A), and the next containing values (column B). I want a formula creating a list of the dates with values between a certain interval (where B is between -3 and -4).

    I got a formula that extracts dates for all values below a certain value, but I can not get it to report the intervals.

    This is what I got:

    Please Login or Register  to view this content.
    Appreciate any help solving this monster

  2. #2
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: find date in colum A, if value in column B is within interval

    You could use a filter to extract these values

  3. #3
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find date in colum A, if value in column B is within interval

    hi nerva. replace all my commas with semi-colons & try this array formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    it's been 2 years since you've joined. do update your MS-Off Ver if it's changed.

    Thanks, if you have clicked on the * and added our rep.

    If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".

    "Contentment is not the fulfillment of what you want, but the realization of what you already have."


    Tips & Tutorials I Compiled | How to Get Quick & Good Answers

  4. #4
    Registered User
    Join Date
    06-20-2011
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: find date in colum A, if value in column B is within interval

    Yes, but that would mess up my input list, which I want to keep intact.

  5. #5
    Forum Expert Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    O365 v 2402
    Posts
    13,443

    Re: find date in colum A, if value in column B is within interval

    Which solution would mess up your list? and in what way?

  6. #6
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: find date in colum A, if value in column B is within interval

    "=INDEX($A$2:$A$287,SMALL(IF($B$2:$B$287>=-4,IF($B$2:$B$287<=-3,ROW($A$2:$A$287))),ROW(1:1))-ROW($A$2)+1)"

    Interesting. I've never seen the row correction (-ROW($A$2)+1) coming after the SMALL function in these constructions. Doesn't appear to be any reason why either should be preferable, though I might need to think about it a bit more.

    And I always prefer ROWS($1:1) rather than ROW(1:1) in these situations to guard against potential (though unlikely) row insertion.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  7. #7
    Registered User
    Join Date
    06-20-2011
    Location
    Oslo, Norway
    MS-Off Ver
    Excel 2003
    Posts
    7

    Re: find date in colum A, if value in column B is within interval

    Great. Thank you! Works perfect

  8. #8
    Forum Guru benishiryo's Avatar
    Join Date
    03-25-2011
    Location
    Singapore
    MS-Off Ver
    Excel 2013
    Posts
    5,147

    Re: find date in colum A, if value in column B is within interval

    Quote Originally Posted by XOR LX View Post
    "=INDEX($A$2:$A$287,SMALL(IF($B$2:$B$287>=-4,IF($B$2:$B$287<=-3,ROW($A$2:$A$287))),ROW(1:1))-ROW($A$2)+1)"

    Interesting. I've never seen the row correction (-ROW($A$2)+1) coming after the SMALL function in these constructions. Doesn't appear to be any reason why either should be preferable, though I might need to think about it a bit more.

    And I always prefer ROWS($1:1) rather than ROW(1:1) in these situations to guard against potential (though unlikely) row insertion.

    Regards
    the array portion is in here:
    IF($B$2:$B$287>=-4,IF($B$2:$B$287<=-3,ROW($A$2:$A$287)))

    that would be the part that's going through the intense calculations. so putting it outside of the array makes it calculate faster. you can either do that or use:
    =INDEX(A:A,SMALL(IF($B$2:$B$287>=-4,IF($B$2:$B$287<=-3,ROW($A$2:$A$287))),ROW(1:1)))

    putting it outside the array was suggested to me by Tony Valko after i mentioned i would prefer not to use his method of ranging the whole column like the above. and i tested both his logic of putting outside the array or ranging the whole column. speed is around the same & faster than putting them in the array portion. you can try it yourself by downloading this:
    http://exceluser.com/blog/708
    i think you once commented about me using non-array formula too. and i told you that one wasn't faster. tested using this too

    i personally prefer ROWS($A1:A1) rather than 1:1. but i sometimes offer 1:1 when i don't know where OP wants it. if you see my compilation of Tips & Tutorials, i always use ROWS

    @nerva:
    glad to help
    =)

  9. #9
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: find date in colum A, if value in column B is within interval

    @benishiryo

    Fascinating stuff, and, yes, I can see the logic in and preference for this construction.

    I think you may have converted me now also! (Will just have to decide whether to take Tony's or your suggestion! )

    Thanks a lot.

+ 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: 01-07-2015, 08:29 PM
  2. [SOLVED] Find the date and copy the values from the colum of the date find and paste to other sheet
    By nizzcmzph in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 02-20-2013, 10:03 AM
  3. Creating a colum consisting of interval averages of an input column
    By wetfish in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 08-08-2012, 10:43 AM
  4. Creating a colum consisting of interval averages of an input column
    By wetfish in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-19-2012, 06:53 AM
  5. find date interval for unique id excel
    By quaye28 in forum Excel General
    Replies: 0
    Last Post: 10-13-2011, 10:07 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