☰

Cómo encontrar un número más cercano al promedio

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
Cómo encontrar un número más cercano al promedio

Tengo una muestra como esta donde quiero encontrar el valor más cercano al promedio

ciudad y peso son dos columnas separadas

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

Hice un pivote y calculé el peso promedio de A- que es 46,75

Necesito encontrar el número más cercano para A, que será 45 en este caso.

Creo que necesito usar índice y coincidencia, pero ¿cómo lo haría si tengo 17.000 filas con nombres de ciudades duplicados y valores de peso diferentes?

Cualquier ayuda lo agradeceria

entonces la respuesta que estoy buscando es

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

La mayoría de las respuestas similares no utilizan este conjunto, ayúdenme a configurar esta fórmula que he probado:

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

Pero analiza toda la gama de pesos de AC. Sólo quiero que mire los valores de A cuando compara el promedio de A,

Y luego el peso de B al comparar el promedio de B,

ETCÉTERA....

¿Por favor déjeme saber qué está mal con mi fórmula?

gracias de antemano

Respuesta1

EDITAR:

Lo siento, no hice un buen trabajo al leer tu pregunta y ahora me di cuenta de que dijiste claramente que querías encontrar el Weightvalor más cercano al promedio.entre los valores para la ciudadpara el cual se calculó el promedio. Así que actualicé la respuesta a continuación.

Parece que encontrasteLa respuesta de XOR LXa una pregunta similar, y estás bastante cerca de acertar.

XOR LX utilizó una pequeña fórmula realmente interesante que evita las limitaciones de MATCH()la búsqueda de datos desordenados. Explicaré cómo funciona a continuación.

En la tabla de datos que se muestra a continuación, calculé los promedios con:

=AVERAGEIF(A$2:A$11,A14,B$2:B$11)(Recibo respuestas diferentes a las que has mostrado arriba).

y el más cercano Weightal promedio con:

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

Tenga en cuenta que esta es una fórmula matricial, por lo que debe ingresarse con CTRLShiftEnter, en lugar de solo Enter.

ingrese la descripción de la imagen aquí ______________________________________________________________________________

Cómo funciona:

ABS(B$2:B$11-B14)es una matriz de las diferencias entre el promedio y todos los números de la Weightlista. Y (A$2:A$11=A14)es una matriz de True/Falsevalores Truedondequiera que Citysea igual A14. Multiplicar estos dos da una serie de esas diferencias en las posiciones correspondientes a City = A14, con 0cualquier otro lugar.

A continuación queremos encontrar el mínimo de esas diferencias, pero tenemos que crear una matriz ligeramente diferente, porque MIN()devolverá 0si 0'shay alguna en la matriz.

IF(A$2:A$11=A14,ABS(B$2:B$11-B14))comprueba dónde City = A14y devuelve las diferencias entre Weighty el promedio de esas posiciones, con Falsetodos los demás.

Tomando el mínimo de esa matriz, MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))se obtiene la diferencia más pequeña.sólo para aquellas posiciones donde City = A14.

Ahora la igualdad (A$2:A$11=A14)*ABS(B$2:B$11-B14)=MIN(IF(A$2:A$11=A14,ABS(B$2:B$11-B14)))proporciona una matriz de True/Falsevalores Trueen la posición de la diferencia más pequeña para el actual City. MATCH()encuentra la posición de True, (que es la posición del número más cercano) y se envía a an INDEX()para devolver el valor real.

Espero que esto ayude y buena suerte.

información relacionada