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

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!

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

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"V" wrote:

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!

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

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!

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

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!

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

Thank you so much!

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

"Luke M" wrote:

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.
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"V" wrote:

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!

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

Thank you so much!

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!

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

It works too! Thank you so much!

"Shane Devenshire" wrote:

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" wrote:

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!

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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
Thank you so much!

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

"T. Valko" wrote:

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!

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

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.


--
Biff
Microsoft Excel MVP


"Shane Devenshire" wrote in
message ...
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" wrote:

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.

--
Biff
Microsoft Excel MVP


"V" wrote in message
...
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!