Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closest-to function
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.
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Closest-to function
"davidoo2005" wrote...
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. 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Lookup to Find Closest Date and Next Closest Date | Excel Worksheet Functions | |||
closest match | Excel Worksheet Functions | |||
Calculate the closest day | Excel Worksheet Functions | |||
Function to find closest to 0 (including neg #'s) in cloumn | Excel Discussion (Misc queries) | |||
what's the closest think to an "average if" function? | Excel Worksheet Functions |