How do I find the *most* average / closest value to the average value within a set of values in Excel?
average, excel, mean, range, statistics
Solution
Assuming that the the values are from Cell A2 to A6, use this array formula
=INDEX(A2:A6,MATCH(MIN(ABS(A2:A6-AVERAGE(A2:A6))),ABS(A2:A6-AVERAGE(A2:A6)),0))
You have to press CTL + SHIFT + ENTER after you enter the formula.
SCREENSHOT
FOLLOWUP
I forgot to mention that I need to omit non-zero values from 'sales' so that any values that are 0 are not counted. Could you show me how this would be done? – alexcu 4 mins ago
Simply use `SUM` and `COUNTIF` instead of `AVERAGE` in this case
Use this formula
`=INDEX(A2:A7,MATCH(MIN(ABS(A2:A7-(SUM(A2:A7)/COUNTIF(A2:A7,"<>0") ))),ABS(A2:A7-(SUM(A2:A7)/COUNTIF(A2:A7,"<>0") )),0))`
SCREENSHOT
Problem
This is probably a really easy question to answer but it's been bugging me for ages now! Say I have a range of values in a column sorted like: ``` Sales: 1500 160 40 300 200 ``` Within this range, I know that the average is 440. What I would like is for Excel to find the most average value within this range (the closest value to 440 in that range is hence 300). So what can I do for Excel to return 300 as the closest value to the average value within that range? Thanks so much!