Como encontrar o número mais próximo da média

Question

Eu tenho uma amostra como essa em que quero encontrar o valor mais próximo da média

cidade e peso são duas colunas separadas

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

Eu fiz um pivô e calculei a média de peso para A- que é 46,75

Preciso encontrar o número mais próximo para A, que será 45 neste caso

Eu acho que preciso usar index e match, mas como eu faria se tivesse 17.000 linhas com nomes de cidades duplicadas e diferentes valores de peso?

Qualquer ajuda que eu aprecie

então a resposta que estou procurando é

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

Respostas mais semelhantes não estão usando este conjunto, por favor me ajude a configurar esta fórmula que eu tentei:

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

Mas olhe para toda a matriz de peso de A-C. Eu só quero ver os valores de A quando está comparando a média de A,

E, em seguida, o peso de B ao comparar a média de B,

E SO ON ....

Por favor, deixe-me saber o que há de errado com a minha fórmula?

obrigado antecipadamente

    

Answer 1

EDITAR:

Desculpe, fiz um trabalho ruim ao ler sua pergunta e só agora percebi que você disse claramente que deseja encontrar o valor de Weight mais próximo da média entre os valores da cidade que a média foi calculada para. Então atualizei a resposta abaixo.

Parece que você encontrou a resposta do XOR LX para uma pergunta semelhante, e você está bem perto de acertar.

O XOR LX usou uma pequena fórmula realmente interessante que contorna as limitações de MATCH() ao pesquisar dados não ordenados. Vou explicar como funciona abaixo.

Na tabela de dados mostrada abaixo, calculei as médias com:

=AVERAGEIF(A$2:A$11,A14,B$2:B$11) (recebo respostas diferentes das que você mostrou acima).

e o mais próximo Weight da média com:

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

Note que esta é uma fórmula de array, então deve ser digitada com CTRL Deslocar Enter , ao invés de apenas Enter .

por 13.04.2018 / 19:14