Return closest data item to average in Excel

shawl

New Member
Joined
Feb 22, 2011
Messages
3
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.
 

Some videos you may like

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
Joined
Aug 24, 2010
Messages
5,778
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
Joined
Feb 22, 2011
Messages
3
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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
 

Watch MrExcel Video

Forum statistics

Threads
1,099,696
Messages
5,470,206
Members
406,685
Latest member
711excelhelp

This Week's Hot Topics

Top