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

1. ## 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!

2. ## 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?

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!

4. ## 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.

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

That works! Thank you so much

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

#### 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