☰

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

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Como encontrar um número mais próximo da média

Eu tenho uma amostra como esta onde 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

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

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

Acho que preciso usar índice e correspondência, mas como faria isso se tivesse 17.000 linhas com nomes de cidades duplicados e valores de peso diferentes?

Qualquer ajuda eu apreciaria

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

A maioria das respostas semelhantes não usa este conjunto. Ajude-me a configurar esta fórmula que tentei:

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

Mas observe toda a gama de peso do AC. Eu só quero que ele observe os valores de A quando estiver comparando a média de A,

E então o peso de B ao comparar a média de B,

E ASSIM POR DIANTE....

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

desde já, obrigado

Responder1

EDITAR:

Desculpe, não fiz um bom trabalho ao ler sua pergunta e só agora percebi que você disse claramente que deseja encontrar o Weightvalor mais próximo da médiaentre os valores para a cidadepara o qual a média foi calculada. Então atualizei a resposta abaixo.

Parece que você encontrouResposta do XOR LXa uma pergunta semelhante, e você está bem perto de acertar.

O XOR LX usou uma pequena fórmula muito interessante que contorna as limitações da MATCH()pesquisa em 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)(Obtenho respostas diferentes das que você mostrou acima).

e o mais próximo Weightda 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))

Observe que esta é uma fórmula de matriz, portanto deve ser inserida com CTRLShiftEnter, em vez de apenas Enter.

insira a descrição da imagem aqui ______________________________________________________________________________

Como funciona:

ABS(B$2:B$11-B14)é uma matriz das diferenças entre a média e todos os números da Weightlista. E (A$2:A$11=A14)é uma matriz de True/Falsevalores Trueonde quer que Cityseja igual A14. Multiplicar esses dois fornece uma série dessas diferenças nas posições correspondentes a City = A14, com 0todos os outros lugares.

Em seguida, queremos encontrar o mínimo dessas diferenças, mas temos que criar um array um pouco diferente, porque MIN()retornará 0se 0'shouver algum no array.

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))verifica onde City = A14e retorna as diferenças Weighte a média dessas posições, com Falsetodos os outros lugares.

Pegando o mínimo dessa matriz, MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))dá a menor diferençaapenas para os cargos onde City = A14.

Agora a igualdade (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))fornece uma matriz de True/Falsevalores Truena posição da menor diferença para o atual City. MATCH()encontra a posição de True, (que é a posição do número mais próximo) e que é alimentada para INDEX()retornar o valor real.

Espero que isso ajude e boa sorte.

informação relacionada