Im trying to find the minimum value between some constant and a column of values. Ideally i would like to return the row number so i can reference a different column.
normally with code (such as Matlab) it was just be as simple as
[minVal, minIdx] = min(abs(constant - arrayOfNumbers))
Then just use the index returned to reference the correct column.
How can i do this in excel?
with a constant of 4184.
1 Col A Col B
2 5040 -0.29
3 4840 -0.41
4 4620 -0.55
5 4450 -0.67
6 4200 -0.86
7 4050 -1
8 3970 -1.11
9 3880 -1.25
10 3850 -1.3
the minimum absolute difference will be abs(4184 - 4200) = 16 at row 6. Hence i would like to reference B(6) to return -0.86
Lets say your value you want to test against (4184) is in cell D1.
First step, find the minimum absolute difference: put this formula is say cell E1 as an array formula (accept with Ctrl-Alt-Enter).
=MIN(IFERROR(ABS(A:A-$D$1),""))
Then find the row this value is in: put this formula in cell F1 as a normal formula
=IFERROR(MATCH(D1-E1,A:A,0),MATCH(D1+E1,A:A,0))
Note 1: IFERROR function requires Excel 2007 or later
Note 2: if you don't want to have the intermediate formula, you can combine them (as an array formula), but this does repeat the MIN(...) part
=IFERROR(MATCH(D1-MIN(IFERROR(ABS(A:A-$D$1),"")),A:A,0),MATCH(D1+MIN(IFERROR(ABS(A:A-$D$1),"")),A:A,0))