Closest number match help ...
Hi,
I addapted a formula from John Walkenbach
Name A1-A3 Data
Name A5 Target
Enter this formula as an array (Shift-Ctrl-Enter
=INDIRECT(ADDRESS(ROW(data)+MATCH(MIN(ABS(target-data)),ABS(target-data),0)-1,COLUMN(data)+0))
Note that if two numbers are the dame distance from your target it will
select the first one
Hope this helps
"NP" wrote:
Looking for a way to find the number that falls closest to another number in
Excel ...
Cell A1 contains value 750
Cell A2 contains value 1500
Cell A3 contains value 3000
When I enter a value in cell A5 (e.g. 800), the result I would like retuned
in cell B5 is the number from cell a1, a2 or a3 which is closest to the
value in A5. Anyone any ideas how to do this please?
Many thanks,
NP.
|