Return closest data item to average in Excel

[shawl]

I have a list of data items in Excel and i want to find the item that is closest to the average (mean).

e.g.
If the list was

8
7
4
6
9

The average would be 6.8 so i would want the returned result to be 7.

I'm drawing a complete blank.

Thanks in advance.

 

[Ruddles]

If those values are in A1:A5, place this in B1 and copy down:-

=ABS(AVERAGE(A$1:A$5)-A1)

Place this wherever you need the answer:-

=INDIRECT("A"&MATCH(MIN(B$1:B$5),B1:B5,FALSE))

 

[shawl]

Thanks for your help

This worked when i did a small test but when i applied it to my data it failed to return the right result. I think this is because my data does not start at row 1, it starts at row 22 (K22:K275).

Is there a way to tweak it to take into account of the start of the range?

Thanks

 

[barry houdini]

You could use this "array formula" to get the result all in one....

=INDEX(K22:K275,MATCH(MIN(ABS(K22:K275-AVERAGE(K22:K275))),ABS(K22:K275-AVERAGE(K22:K275)),0))

confirmed with CTRL+SHIFT+ENTER

 

[shawl]

The array formula is perfect.

Thanks for both of your help.