[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 276
  • 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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