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

[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.

 

[Vasant Nanavati]

=MAX(B2:B4-A1)

entered as an array formula with <Ctrl> <Shift> <Enter>.

 

[Vasant Nanavati]

Sorry, read the question too fast.

 

[Paul]

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.

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

 

[Vasant Nanavati]

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>.

 

[Paul]

Great! I knew there was an easier way than my suggested method.

 

[Vasant Nanavati]

And I'm sure there's a much easier way than mine, too <g>.

 

[Tom C]

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...