• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Trying to use formula to find closest value to number from list in descending order


I have a list of number in descending order.  With a number that doesn't necessarily match one in the list, I'm trying to locate the largest number that is still less than the number I'm looking for.  I know this is possible in VBA via a loop, but I'm wondering if anyone knows how to do it with functions.  See attached.
FindClosestValue.xlsx
0
BBlu
Asked:
BBlu
  • 3
  • 3
  • 2
  • +1
3 Solutions
 
StephenJRCommented:
Here is one way:

=INDEX(D3:D20,MATCH(G8,D3:D20,-1)+1)
0
 
varontronCommented:
=OFFSET(D3,COUNT(D3:D1000)-SUM(IF(D3:D1000<G8,IF(NOT(ISBLANK(D3:D1000)),1,0))),0)

Where:  
D3 is the top of the column range containing your list
D1000 is the bottom of an arbitrary range size, it could be any size, because the COUNT returns the number of rows in the range.
G8 contains your reference value (e.g. 77)
the SUM(IF(IF(NOT(ISBLANK()))) construct returns the count of values less that your reference value in the entire range excluding blank cells.  The result of this sum function is substracted from the count of values, and then that difference is used as the row offset for the top of the range.

Remember to type CTRL-Enter to calculate as it is an array formula.

HTH,
Dave
0
 
varontronCommented:
actually there's an error -- gimme a sec
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
varontronCommented:
=OFFSET(D3,COUNT(D3:D1000)-SUM(IF(NOT(ISBLANK(D3:D1000)),IF(D3:D1000<G8,1,0))),0)

OK, there you go.  switched the IFS.
0
 
barry houdiniCommented:
I think Stephen's suggestion works except when there's an exact match, if G8 is 74 in your example then the suggested formula returns 69, I assume it should be 74?

Try this "array formula"

=MAX(IF(D3:D20<=G8,D3:D20))

confirmed with CTRL+SHIFT+ENTER

That works whatever the order of the numbers in D3:D100

regards, barry
0
 
StephenJRCommented:
Heh Barry you always spot things which I should, but don't.
0
 
barry houdiniCommented:
I'm still not sure if that's the requirement - question says "less than" but in the workbook it's "without going over"......?

regards, barry
0
 
StephenJRCommented:
Good point, OP needs to clarify.
0
 
BBluAuthor Commented:
I haven't tested Dave or Stephen's but they look right.  Barry's worked perfectly and was the easiest for me to understand.  Thanks to you all!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 3
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now