Bonus con umbral mínimo y tope máximo: SI.CONJUNTO vs multiplicación booleana

Juan plantea un problema de cálculo de bonus con reglas de negocio concretas: un importe de 9.000 euros se reparte entre varios epígrafes, cada uno con su ponderación y porcentaje de cumplimiento. Las reglas son claras pero difíciles de plasmar en una sola fórmula:

- Si el cumplimiento es inferior al 85%, no se genera nada (resultado 0)
- Entre 85% y 115%, se aplica el porcentaje real
- Por encima del 115%, se capea al 115% (tope máximo)

Además, Juan necesita que si la celda del porcentaje está vacía, muestre "Rellenar Obligatorio". Su primer intento con SI.CONJUNTO no le funcionaba: al poner primero la condición >=85%, los valores superiores al 100% nunca llegaban a evaluarse.

Solución 1: SI.CONJUNTO con orden correcto (Iván)

Iván detecta el problema: en SI.CONJUNTO, la primera condición verdadera detiene la evaluación. Hay que poner las condiciones de más restrictiva a menos restrictiva:

``
=SI.CONJUNTO(
$J7=""; "Rellenar Obligatorio";
$J7<85%; "0";
$J7>115%; 115%$I$2G7;
$J7>=85%; $I$2G7J7
)
`

Primero comprueba si supera el 115% (y capea), y solo después aplica el rango 85%-115%. Si se pusiera >=85% antes, un valor de 120% cumpliría esa condición y nunca llegaría a la de >115%.

Solución 2: Fórmula compacta con multiplicación booleana (John)

John propone una solución radicalmente distinta que elimina toda la anidación:

`
=SI(J27="";"Rellenar Obligatorio";I$2G27(J27>=85%)*MIN(115%;J27))
`

La clave está en dos trucos:

- (J27>=85%): una condición lógica dentro de una operación matemática se convierte en 1 (VERDADERO) o 0 (FALSO). Si el porcentaje no llega al 85%, todo el producto da 0 automáticamente.

- MIN(115%;J27): establece el tope máximo comparando el valor real con el 115% y quedándose con el menor. Si J27 es 90%, devuelve 90%. Si J27 es 130%, devuelve 115%.

Combinados, estos dos factores resuelven las tres reglas de negocio en una sola multiplicación, sin necesidad de SI.CONJUNTO` ni condiciones anidadas.

El problema: un bonus con tres reglas de negocio

Juan planteó un cálculo de bonus que parece sencillo pero se resiste a caber en una fórmula. Un importe de 9.000 euros se reparte entre varios epígrafes, cada uno con su ponderación y su porcentaje de cumplimiento. Las reglas:

  • Si el cumplimiento es inferior al 85%, no se genera nada (resultado 0).
  • Entre 85% y 115%, se aplica el porcentaje real.
  • Por encima del 115%, se capea al 115% (tope máximo).
  • Y si la celda del porcentaje está vacía, debe mostrar "Rellenar Obligatorio".

Su primer intento con SI.CONJUNTO no funcionaba: al poner primero la condición de "mayor o igual al 85%", los valores por encima del 100% nunca llegaban a evaluar el tope. La comunidad dio con dos soluciones muy distintas.

Solución 1: SI.CONJUNTO con el orden correcto (Iván)

Iván detecta el fallo: en SI.CONJUNTO, la primera condición verdadera detiene la evaluación. Por eso hay que ordenar las condiciones de la más restrictiva a la menos restrictiva:

=SI.CONJUNTO(
    $J7=""; "Rellenar Obligatorio";
    $J7<85%; "0";
    $J7>115%; 115%*$I$2*G7;
    $J7>=85%; $I$2*G7*J7
)

Primero comprueba si supera el 115% (y capea), y solo después aplica el rango 85%-115%. Si la condición de "mayor o igual al 85%" fuera antes, un valor del 120% la cumpliría y jamás llegaría a la del tope.

Solución 2: multiplicación booleana (John)

John propone algo radicalmente distinto que elimina toda la anidación:

=SI(J27="";"Rellenar Obligatorio";I$2*G27*(J27>=85%)*MIN(115%;J27))

La elegancia está en dos trucos combinados:

  • (J27>=85%): una condición lógica metida dentro de una multiplicación se convierte en 1 (VERDADERO) o 0 (FALSO). Si el porcentaje no llega al 85%, todo el producto da 0 automáticamente, sin necesidad de un SI explícito.
  • MIN(115%;J27): fija el tope máximo quedándose con el menor entre el valor real y el 115%. Si J27 vale 90%, devuelve 90%; si vale 130%, devuelve 115%.

Con esos dos factores, las tres reglas de negocio se resuelven en una sola multiplicación.

Funciones clave

  • SI.CONJUNTO: evalúa condiciones en orden y se queda con la primera verdadera; el orden importa.
  • MIN: la forma más limpia de aplicar un tope máximo (o MAX para un suelo mínimo).
  • Aritmética booleana: multiplicar por una condición lógica (que vale 1 o 0) sustituye a muchos SI anidados y deja fórmulas cortísimas.

Conclusión

Dos filosofías para el mismo problema: la explícita y legible de SI.CONJUNTO (siempre que respetes el orden de las condiciones) y la compacta y matemática de la multiplicación booleana con MIN. Ambas son correctas; elige según prefieras claridad o brevedad. Casos con reglas de negocio reales como este salen cada semana en la comunidad de Influexcel, y verlos resueltos de varias maneras es la mejor escuela.

Más contenido de Excel en InflueXcel