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 Weight
valor 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 Weight
al 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.
______________________________________________________________________________
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 Weight
lista. Y (A$2:A$11=A14)
es una matriz de True/False
valores True
dondequiera que City
sea igual A14
. Multiplicar estos dos da una serie de esas diferencias en las posiciones correspondientes a City = A14
, con 0
cualquier otro lugar.
A continuación queremos encontrar el mínimo de esas diferencias, pero tenemos que crear una matriz ligeramente diferente, porque MIN()
devolverá 0
si 0's
hay alguna en la matriz.
IF(A$2:A$11=A14,ABS(B$2:B$11-B14))
comprueba dónde City = A14
y devuelve las diferencias entre Weight
y el promedio de esas posiciones, con False
todos 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/False
valores True
en 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.