How do I find the cell/data the closest/nearest to another data?

[V]

Hi everyone!

I'm sorry, I can't find my answer in the general help program.

How do I find the cell or the data from a list, that I cannot sort, the
closest to another data.

e.g. If I have 150 values in a column and I do the average of this column, I
want to know which data or cell is the closest from this average. Thank you
so much!

 

[Luke M]

Assuming the cells are in A2:A151
Select A2, go to Format, Conditional Format. Formula is:
=ABS(A2-AVERAGE($A$2:$A$151))=MIN(ABS($A$2:$A$151-AVERAGE($A$2:$A$151)))

Select a format you want to appear if cell meets your criteria. Select A2,
copy, then select rest of column, right click, paste special, formatting only.

 

[T. Valko]

Try this array formula** :

=INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-AVERAGE(A1:A15))=MIN(ABS(A1:A15-AVERAGE(A1:A15))),0))

Or, you can use a separate cell to hold the average and then reference that
cell:

B1: =AVERAGE(A1:A15)

Still array entered** :

=INDEX(A1:A15,MATCH(TRUE,ABS(A1:A15-B1)=MIN(ABS(A1:A15-B1)),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Note that if there is more than 1 instance of a closest value the formula
will return the *first* instance from top to bottom.

 

[Shane Devenshire]

Hi,

This is the perfect time to use range names with Valko's solution: If you
name the range N (for nearest) then:

=INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),))

 

[V]

Thank you so much!

It's not exactly what I want but it helps a lot. Thank you, thank you!

 

[V]

Thank you so much!

You have it right! It's fantastic! Thank you so much!

 

[V]

It works too! Thank you so much!

Hi,

This is the perfect time to use range names with Valko's solution: If you
name the range N (for nearest) then:

=INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),))

--
If this helps, please click the Yes button

Cheers,
Shane Devenshire

 

[T. Valko]

You're welcome. Thanks for the feedback!

 

[T. Valko]

This is the perfect time to use range names

There's never a perfect time!

=INDEX(N,MATCH(1,--(ABS(N-AVERAGE(N))=MIN(ABS(N-AVERAGE(N)))),))

More efficient to match TRUE.