+ Reply to Thread
Results 1 to 5 of 5

How do I find the four highest and four lowest numbers (sorted by date)

  1. #1
    Registered User
    Join Date
    01-23-2009
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    3

    How do I find the four highest and four lowest numbers (sorted by date)

    I have two sheets:

    Sheet one has dates, my prices on each of those days, and the fields h1 h2 h3 h4 (h1 is the closest higher number, h2 is the next closet higher number, etc.) and l1 l2 l3 l4 (l1 is the closest lower number, etc. etc.)

    Sheet two has certain dates and prices of a competitor's prices.

    What I'm trying to do is two things (see attached):
    1) find the four closest higher and four closest lower prices (if available)
    2) use only the prices that are on or before the date in question.

    I've tried combinations like LARGE and OFFSET and so far it is not getting me anywhere.

    Can this be done???

    Let me know if you have any questions.

    Thanks!
    Attached Files Attached Files

  2. #2
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: How do I find the four highest and four lowest numbers (sorted by date)

    I may be misunderstanding the question since your sample sheet has no sample results mocked up to demonstrate your expectations. Does the attached sheet do it?
    Attached Files Attached Files
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    01-23-2009
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I find the four highest and four lowest numbers (sorted by date)

    That's pretty close (definitely on the right track), but I need it in the results area (I highlighted the fields that need to be filled in with a formula). Also, it's my prices that are determining the closest higher numbers and closest lower numbers, not the other way around like you have it.

    Currently, I have the correct answers hard-coded and I need to have it in a formula going forward....

    Let me know if you have any questions.

    Thanks!
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-29-2012
    Location
    Florida, USA
    MS-Off Ver
    2010, 2013
    Posts
    34

    Re: How do I find the four highest and four lowest numbers (sorted by date)

    Does the attached work? There are instructions in the file for additional clarity.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    01-23-2009
    Location
    Washington, DC
    MS-Off Ver
    Excel 2007
    Posts
    3

    Re: How do I find the four highest and four lowest numbers (sorted by date)

    That works! Thank you so much

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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