Nearest number to a match
Posted by Don on July 18, 2000 5:16 AM
Using Excel or VBA, does anyone know how to find the nearest number to a match in a list of numbers? Example; The number 41.166245 is calculated and the list contains the number 41.1875, the nearest match. How do I get the computer to tell me that?
Posted by AB on July 18, 0100 6:39 AM
I have a "Lookup.xls" example file you might find interesting. In particular, one of the sheets is labeled "NotSoSimple Lookups". It shows how to find the closest value and then gives two options for a split: default to lower or default to higher number.
It also provides the solution in two forms: standard cell logic and a VBA custom function (CLOSEST). Stephen Bullen helped me out with the cell logic solution on this one. It's worth a peek.
-Aaron ;)The Excel Logic Page
