Closest-to function

G

Guest

Is there a VBA function that sorts through a number of cells containing numbers that returns the cell closest to a certain value? For instance, in a column of numbers, I would like to find the cell containing the number closest to 20. The column may or may not contain 20 as a value.
 
H

Harlan Grove

davidoo2005 said:
Is there a VBA function that sorts through a number of cells
containing numbers that returns the cell closest to a certain
value? For instance, in a column of numbers, I would like to
find the cell containing the number closest to 20. The column
may or may not contain 20 as a value.
Click to expand...

In a worksheet the usual approach would be to use the array formula

=MATCH(MIN(ABS(Range-Target)),ABS(Range-Target),0)

You could replace Range and Target in the formula above with your own
values, then feed the resulting string to Evaluate. The result would be the
index in Range at which the first (topmost or leftmost) of possibly many
closest value to Target is found.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

macro to find the closest value 4
Finding Closest Text Match 3
programming delima 9
Closest value lookup? 11
Weighted randbetween text list 3
Match function / returning closest value in VBA 2
this is killing me. INDEX/MATCH "closest to X" in a smart way? 5
closest neighbour coordinates 5

Top