Valores superior e inferior de uno dado.

En un comentario un usuario preguntaba sobre la forma de encontrar en un listado de valores cuáles eran aquellos inmediatamente superior o inferior para un número dado:

...COMO HACER PARA ENCONTRAR LOS VALORES APROXIMADOS POR ENCIMA Y DEBAJO DE UN NUMERO DADO; YA SE QUE CON ESTA FUNCION =INDICE(Hoja1!C6:C30;COINCIDIR(MIN(ABS(Hoja1!C6:C30-Hoja1!C34));ABS(Hoja1!C6:C30-Hoja1!C34);0)) HALLO EL MAS APROXIMADO PERO LO MUESTRA SEGUN LA APXROXIMACION POR ENCIMA Y DEBAJO ,YO QUIERO QUE EN UNA CELDA ME BOTE EL NUMERO ANTERIOR Y EN OTRA EL SUPERIOR...

Supondremos un listado de valores únicos, siendo nuestro objetivo encontrar, para un valor cualquiera, aquellos registros inmediatamente superior e inferior.
Importante: las funciones explicadas a continuación no están depuradas para valores buscados en los límites más altos o más bajos, es decir, para encontrar el valor superior o inferior del número más alto (2.000,00)/bajo(6,00) del listado!!!.
Nuestro listado es el siguiente:

Emplearé la función K.ESIMO.MAYOR (ver) para encontrar aquellos registros buscados. Lo haré por partes para ver correctamente los pasos (aunque podríamos anidarlos y ver en una sólo función).
La primera cuestión será encontrar las posiciones, ordenadas de mayor a menor.
Estas posiciones las definiremos con la función COINCIDIR. En la celda F4 dispondremos del valor a buscar:

• Para encontrar la posición del valor inferior al buscado (celda G3) escribiremos en forma matricial (Ctrl+Mayusc+Enter):
  {=COINCIDIR(F4;K.ESIMO.MAYOR($B$1:$B$30;FILA($B$1:$B$30));-1)}


• Para encontrar la posición del valor superior al buscado (celda H3) escribiremos en forma matricial (Ctrl+Mayusc+Enter):
  {=COINCIDIR(F4;K.ESIMO.MAYOR($B$1:$B$30;FILA($B$1:$B$30));-1)-1}

Como vimos en la imagen anterior estas fórmulas nos dicen en qué posición jerarquica (ordenada de mayor a menor) se hallan los valores inferior y superior al valor buscado en la celda F4.
La clave de estas funciones es haber conseguido ordenar (en sentido descendente, i.e., de mayor a menor) nuestro rango de valores mediante la forma matricial
K.ESIMO.MAYOR($B$1:$B$30;FILA($B$1:$B$30))
obteniendo un rango ordenado equivalente a:

sobre el rango ordenado generado, podemos aplicar una búsqueda, en este caso con la función COINCIDIR:
=COINCIDIR(valor_buscado; rango_ordenado; -1)
siendo el tercer argumento de esta función ([Tipo de coincidencia]) defiinido por:

1. 1 u omitido: encuentra el mayor valor que es menor o igual que el valor_buscado. Los valores del 'rango_ordenado' se deben colocar en orden ascendente.


2. 0 cero: encuentra el primer valor que es exactamente igual que el valor_buscado. Los valores del 'rango_ordenado' pueden estar en cualquier orden.


3. -1: encuentra el menor valor que es mayor o igual que el valor_buscado. Los valores del 'rango_ordenado' se deben colocar en orden descendente.

Llegamos al momento esperado, cuál es el valor del listado que corresponde... para ello sobre nuestras celdas G3 y H3 aplicamos unas nuevas funciones.
El valor inferior (celda G4) al 'valor_buscado' será:
=K.ESIMO.MAYOR($B$1:$B$30;G3+1)
y el valor superior (celda H4) al buscado vendrá determinado por:
=SI(ESERROR(COINCIDIR(F4;$B$1:$B$30;0));K.ESIMO.MAYOR($B$1:$B$30;H3+1);K.ESIMO.MAYOR($B$1:$B$30;H3))

Finalmente podemos ver como para un valor buscado de 1.800, con la formulación definida, obtenemos el valor inmediato inferior (1.270,00) y superior (1.892,72) del listado de valores original.

Adjunto fichero para su mejor interpretación.

Valores aproximado...

Hosted by eSnips