Fórmula genérica
=MAX(0,MIN(A1,1))
Resumen
Para limitar un valor porcentual para que esté entre 0% y 100%, puede usar una fórmula basada en las funciones MIN y MAX. En el ejemplo que se muestra, la fórmula en C5, copiada, es:
=MAX(0,MIN(B5,1))
El resultado es que los valores negativos se fuerzan a cero, los valores superiores a 1 tienen un límite de 1 y los valores entre 0 y 1 no se ven afectados.
Nota: todos los valores formateados con formato de número de porcentaje.
Explicación
Para comprender este problema, asegúrese de comprender cómo funciona el formato de número de porcentaje. En pocas palabras, los porcentajes son valores decimales: 0.1 es 10%, 0.2 es 20%, etc. El número 1, cuando se formatea como porcentaje, es 100%. Más sobre formatos de números aquí.
El objetivo de este ejemplo es limitar los valores porcentuales entrantes para que caigan dentro de un umbral superior e inferior. No se permiten valores negativos y valores superiores al 100%, por lo que el resultado final debe ser un número entre cero y 1 (0-100%), inclusive.
Aunque la función SI se puede utilizar para resolver este problema (ver más abajo), el resultado será algo más largo y redundante. En cambio, el ejemplo que se muestra usa una combinación de las funciones MIN y MAX en una fórmula muy compacta:
=MAX(0,MIN(B5,1))
Este es un ejemplo de anidamiento: la función MIN está anidada dentro de la función MAX. El anidamiento es un componente clave para fórmulas más avanzadas.
Trabajando desde adentro hacia afuera, la función MIN se usa para limitar los valores entrantes a 1 de esta manera:
MIN(B5,1) // get smaller value
Traducción: devuelve el menor de B5 y 1. Para cualquier valor superior a 1, se devuelve el valor de B5. En el ejemplo, B5 contiene -5% (-0,05), por lo que MIN devuelve -0,05. Este resultado se devuelve directamente a la función MAX:
=MAX(0,-0.05) // get larger value
Aquí vemos que la fórmula hace su trabajo. Como cero es mayor (mayor) que -0,05, MAX devuelve cero como resultado final. El valor original se descarta.
Función SI
Como se mencionó anteriormente, la función SI también se puede utilizar para resolver este problema. Para hacer esto, necesitamos dos funciones SI separadas. Un SI fuerza los valores negativos a cero:
IF(B5<0,0,B5) // cap at zero
El segundo IF limita los valores más grandes a 1:
=IF(B5>1,1,B5) // cap at 1
Cuando anidamos el primer SI dentro del segundo, tenemos la fórmula final:
=IF(B5>1,1,IF(B5<0,0,B5))
Este es un ejemplo de un IF anidado. Devuelve exactamente el mismo resultado que la fórmula MIN y MAX anterior, pero es un poco más compleja y redundante. Observe, por ejemplo, que la referencia a B5 ocurre tres veces distintas.
En resumen: cuando necesite tomar una decisión basada en valores más pequeños o más grandes, las funciones MIN y MAX pueden ser una forma inteligente y elegante de mantener una fórmula simple.
Función MEDIAN
Bien, ahora que hemos hablado sobre el anidamiento y hablado de la elegancia de MIN con MAX, debo mencionar que es posible resolver este problema sin ningún tipo de anidamiento con la función MEDIAN. La versión genérica de la fórmula se ve así:
=MEDIAN(0,1,A1)
Esto funciona porque la función MEDIANA devuelve la mediana (número del medio) en un grupo de números. Cuando un valor es negativo, el cero se convierte en el número medio. Cuando un número es mayor que 1, 1 se convierte en el número del medio. ¡Inteligente!
Sin embargo, tenga en cuenta que MEDIAN solo devuelve el número del medio cuando el número total de valores es impar. Si el número de valores es par, MEDIAN devuelve el promedio de los dos números en el medio. Como consecuencia, si la celda de destino (A1) está vacía, MEDIAN devolverá el promedio de 1 y cero, que es 0.5, o 50% cuando se formatea como porcentaje.
