# Return closest data item to average in Excel

#### shawl

##### New Member
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.

### Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

#### Ruddles

##### Well-known Member
If those values are in A1:A5, place this in B1 and copy down:-
Code:
``=ABS(AVERAGE(A\$1:A\$5)-A1)``
Place this wherever you need the answer:-
Code:
``=INDIRECT("A"&MATCH(MIN(B\$1:B\$5),B1:B5,FALSE))``

#### shawl

##### New Member

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

##### MrExcel MVP
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

##### New Member
The array formula is perfect.

Thanks for both of your help.