Find a value that has the largest difference compared to a given reference

  • Thread starter Thread starter Tom C
  • Start date Start date
T

Tom C

How can I, with one single formula, find a value in a
range of n values that has the largest absolute difference
compared to a given reference value?

Example:
A1: 5 (reference)

B2: 3
B3: 8
B4: 6

The range to search is B2:B4, and the wanted value would be
8 (B3), as the differences are abs(3-5)=2, abs(8-5)=3, abs
(6-5)=1.
 
Tom C said:
How can I, with one single formula, find a value in a
range of n values that has the largest absolute difference
compared to a given reference value?

Example:
A1: 5 (reference)

B2: 3
B3: 8
B4: 6

The range to search is B2:B4, and the wanted value would be
8 (B3), as the differences are abs(3-5)=2, abs(8-5)=3, abs
(6-5)=1.
Click to expand...

Maybe there's an easier way, but this array formula does what you want:
=IF(ISNA(INDEX(B2:B4,MATCH(A1+MAX(ABS(B2:B4-A1)),B2:B4,0))),INDEX(B2:B4,MATC
H(A1-MAX(ABS(B2:B4-A1)),B2:B4,0)),INDEX(B2:B4,MATCH(A1+MAX(ABS(B2:B4-A1)),B2
:B4,0)))
Enter with CTRL+SHIFT+ENTER
 
Here we go:

=INDEX(B2:B4,MATCH(MAX(ABS(B2:B4-$A$1)),ABS(B2:B4-$A$1),0))

entered as an array formula with <Ctrl> <Shift> <Enter>.
 
Thank you guys for your help! I was on my way to a
solution, but I had a formula that was more complicated
than both your solutions, and it wasn't even complete...
 
Back
Top