Как найти ближайшее число к среднему

Question

У меня есть такой пример, где я хочу найти ближайшее к среднему значению

город и вес две отдельные колонки

city  weight
A      23
A      22
A      45
A      97
B      34
B      22
B      23
C      76
C      23
C      23

Я сделал круг и вычислил средний вес для A-, который составляет 46,75

Мне нужно найти ближайший номер для A, который будет 45 в этом случае

Я думаю, что мне нужно использовать индекс и соответствие, но как бы я это сделал, если бы у меня было 17 000 строк с дублирующимися названиями городов и разными значениями веса?

Любая помощь, я был бы признателен

поэтому я ищу ответ

Row Labels  Average of WEIGHT     nearest number
A                 46.75            45
B                 38.75            34
C                  23              23

Большинство похожих ответов не используют этот набор, пожалуйста, помогите мне настроить формулу, которую я попробовал:

INDEX(rawdata,MATCH(MIN(ABS(weight-$B2)),ABS(weight-$B2),0),2)

Но это смотреть на весь массив веса от переменного тока. Я только хочу, чтобы он посмотрел на значения для А, когда он сравнивает среднее значение А,

А затем вес B при сравнении среднего B,

И ТАК ДАЛЕЕ....

Пожалуйста, дайте мне знать, что не так с моей формулой?

заранее спасибо

Answer 1

РЕДАКТИРОВАТЬ:

Извините, я плохо прочитал ваш вопрос и только сейчас понял, что вы четко сказали, что хотите найти ближайшее значение Weight к среднему среди значений для города , для которого было рассчитано среднее значение. Поэтому я обновил ответ ниже.

Похоже, вы нашли ответ XOR LX на похожий вопрос, и вы достаточно близки к тому, чтобы сделать это правильно.

XOR LX использовал действительно аккуратную маленькую формулу, которая обходит ограничения MATCH() при поиске в неупорядоченных данных. Я объясню, как это работает ниже.

В приведенной ниже таблице данных я вычислил средние значения с помощью:

=AVERAGEIF(A$2:A$11,A14,B$2:B$11) (я получаю ответы, отличные от того, что вы показали выше).

и ближайший Weight к среднему с:

=INDEX((A$2:A$11=A14)*(B$2:B$11),MATCH(TRUE,(A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))),0))

Обратите внимание, что это формула массива, поэтому ее нужно вводить с помощью CTRL Shift Enter, а не просто Enter.

______________________________________________________________________________

Как это устроено:

ABS(B$2:B$11-B14) представляет собой массив различий между средним и всеми числами в списке Weight . И (A$2:A$11=A14) представляет собой массив значений True/False с True где City равен A14 . Умножение этих двух значений вместе дает массив этих различий в позициях, соответствующих City = A14 , где 0 везде.

Далее мы хотим найти минимум этих различий, но нам нужно создать немного другой массив, потому что MIN() вернет 0 если в массиве есть любые 0's .

IF(A$2:A$11=A14,ABS(B$2:B$11-B14)) проверяет, где City = A14 , и возвращает разницу между Weight и средним для этих позиций, причем False везде.

Взяв минимум этого массива, MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))) дает наименьшую разницу только для тех позиций, где City = A14 .

Теперь равенство (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14))) дает массив значений True/False с True в позиции наименьшей разницы для текущего City . MATCH() находит позицию True , (которая является позицией ближайшего числа) и которая подается в INDEX() для возврата фактического значения.

Надеюсь, это поможет, и удачи.