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!

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!

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" <(E-Mail Removed)> wrote in message
news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
> 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!

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" <(E-Mail Removed)> wrote in message
> news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
> > 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!
>
>
>

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!

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" <(E-Mail Removed)> wrote in message
> news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
> > 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!
>
>
>

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" <(E-Mail Removed)> wrote in message
> > news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
> > > 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!
> >
> >
> >

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"V" <(E-Mail Removed)> wrote in message
news:8AD95D48-FBAF-4ACA-ABC4-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
>> > 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!
>>
>>
>>

>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" <(E-Mail Removed)> wrote in
message news:6C6E9404-A674-4F0A-B3FD-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:46F03600-4E95-4DBD-AA87-(E-Mail Removed)...
>> > 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!
>>
>>
>>