如何在Excel中的一组值中找到*most* average /最接*均值的值?

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?

在这个范围内，我知道平均值是440。我想让Excel找到这个范围内的最大平均值(这个范围内最接近440的值是300)。那么Excel怎么做才能返回300作为最接近这个范围内平均值的值呢?

Thanks so much!

非常感谢!

1 个解决方案

#1

Assuming that the the values are from Cell A2 to A6, use this array formula

假设值是从单元A2到A6，使用这个数组公式

=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.

输入公式后，必须按CTL + SHIFT + ENTER键。

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

我忘了说，我需要从“sales”中省略非零值，这样任何值为0的值都不会被计算。你能告诉我怎么做吗?- 4分钟前

Simply use SUM and COUNTIF instead of AVERAGE in this case

用SUM和COUNTIF代替AVERAGE

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))

=指数(A2:A7,匹配(MIN(ABS(A2:A7 -(总和(A2:A7)/条件统计(A2:A7," < > 0 ")))),ABS(A2:A7 -(总和(A2:A7)/条件统计(A2:A7," < > 0 "))),0))

SCREENSHOT

截图