Tramos: La Liquidación de Comisiones Pagó 99.398,40 con un Plan Que Dice 50.398,40

La liquidación del tercer trimestre salió el último viernes. Diez comerciales, 1.389.990,00 de ventas, 99.398,40 de comisión — el 7,15% de todo lo vendido. Cada porcentaje aplicado era el porcentaje correcto del baremo del plan, cada multiplicación estaba bien, y nadie había escrito un número encima de una fórmula.

Lee ese mismo baremo de la otra manera y a esos mismos diez comerciales se les deben 50.398,40. Los 49.000,00 de diferencia no son un error de fórmula. Son una frase de un plan de comisiones que se puede leer de dos maneras, y una hoja de cálculo implementará cualquiera de las dos sin insinuar jamás que había una elección que hacer.

Eso es un tramo: una regla que cambia de porcentaje al cruzar un umbral. Escalas de comisión, tramos del IRPF, descuentos por volumen, portes por peso, intereses de demora, tarifas eléctricas por bloques, aceleradores de bonus — todos tienen la misma forma y todos comparten las mismas dos preguntas. ¿En qué tramo cae esto, y el porcentaje se aplica a todo o solo a la parte que pasa de la raya?

Qué cubre esto. BUSCARV, COINCIDIR, INDICE, SI, SUMAPRODUCTO, SUMAR.SI.CONJUNTO y REDONDEAR funcionan en todas las versiones de este siglo, y todo lo esencial de aquí está construido con ellas. BUSCARX, COINCIDIRX, SI.CONJUNTO, LET y CAMBIAR requieren Microsoft 365 o Excel 2021; donde aparece alguna, al lado va el equivalente antiguo.

1) Un Baremo, Diez Comerciales, Dos Respuestas

El plan ocupa cuatro líneas. Dice: los comerciales ganan un 4% desde 50.000, un 6% desde 100.000, un 8% desde 150.000 y un 10% desde 250.000, medido sobre las ventas del trimestre.

Y esto es lo que produjo, al lado de lo que también se puede entender que dice:

Mira la última columna antes de leer nada más. Las diferencias son 2.000,00, 4.000,00, 7.000,00 y 12.000,00, y se repiten. Aisha vendió 248.900 y Yuki vendió 151.200 — 97.700 de diferencia — y las dos lecturas discrepan exactamente en 7.000,00 para las dos. Marcus vendió 52.400 y Priya 99.880, y ambas discrepan exactamente en 2.000,00.

Esa es la primera cosa útil de todo el asunto: la diferencia entre las dos lecturas no es proporcional a las ventas. Es una cantidad fija pegada al tramo, y la sección 3 explica por qué esa cantidad fija es el problema entero.

🎯 Escenario: Antes de tocar una fórmula, pon los dos totales en pantalla y etiquétalos. =SUMA(E2:E11) son 99.398,40 — lo que salió. La sección 7 te da una sola celda para los 50.398,40. Una liquidación de comisiones con un solo número encima no se puede discutir, solo creer.

2) La Frase Que Cuesta 49.000,00

"Un 4% desde 50.000" es genuinamente ambiguo, y las dos lecturas tienen nombre.

Sobre el total (también llamada de acantilado, retroactiva o el porcentaje se aplica a todo): busca el tramo y aplica su porcentaje a cada unidad vendida. Tomas, con 100.120, está en el tramo del 6%, así que 100.120 × 6% = 6.007,20.

Marginal (también llamada progresiva, incremental, diferencial o por escalones): cada porcentaje se aplica solo a la rebanada de ventas que cae dentro de su propio tramo. Tomas no gana nada por sus primeros 50.000, gana un 4% sobre los 50.000 siguientes y un 6% sobre los 120 que pasan de 100.000: 0 + 2.000,00 + 7,20 = 2.007,20.

Cuál de las dos quiere decir un plan no es cuestión de gustos; es cuestión de lo que está escrito, y cada ámbito tiene su costumbre:

El mito de "me subieron el sueldo, pasé de tramo y cobro menos" sobrevive porque la gente aplica la lectura sobre el total a un sistema marginal. Bajo una escala marginal no puede pasar — y, como muestra la sección 3, bajo una sobre el total pasa constantemente.

🎯 Escenario: Deja resuelta la lectura en una frase, por escrito, antes de construir nada: "el 6% se aplica al total de las ventas en cuanto las ventas llegan a 100.000" o "el 6% se aplica solo a las ventas que pasan de 100.000". Y después escribe la fórmula que dice exactamente eso. La mitad de las liquidaciones de comisiones equivocadas del mundo son implementaciones correctas de la frase que nadie escribió.

3) El Acantilado, con Precio

Bajo la lectura sobre el total, cruzar un umbral paga una prima que no tiene nada que ver con la venta que lo cruzó. Esto es lo que vale cada umbral en el momento de cruzarlo:

Esos escalones son las diferencias entre las cantidades fijas de la sección 1 — 0 → 2.000 → 4.000 → 7.000 → 12.000 — que es lo que significa en la práctica que "la diferencia va pegada al tramo".

Ahora pon a los comerciales al lado:

• Kwame Osei vendió 49.750 y no ganó nada. Le faltan 250 para el primer umbral. Esos 250 de venta valen 2.000,00 de comisión — un 800% de rentabilidad sobre el último pedido del trimestre, y un 0% sobre todos los anteriores.
• A Priya Nair y a Tomas Lindqvist los separan 240 de venta y 2.012,00 de nómina. Priya vendió 99.880 y se llevó 3.995,20; Tomas vendió 100.120 y se llevó 6.007,20. Bajo la lectura marginal, esos mismos 240 valen 12,00.
• A Daniel Fischer y a Yuki Tanaka los separan 2.300 de venta y 3.162,00 de nómina. En marginal, esos 2.300 valen 162,00.
• Sofia Marchetti cruzó los 250.000. Con 249.999,99 el plan paga 20.000,00. Con 250.000,00 paga 25.000,00. Un céntimo de venta vale 5.000,00 — y para ganar esos mismos 5.000,00 al 8%, un comercial tendría que vender otros 62.500.

Y ahora vuelve a mirar la columna de ventas. Tres de los diez comerciales están a menos de 120 del umbral de 100.000: 99.880, exactamente 100.000 y 100.120. Ese apelotonamiento no es casualidad ni suerte. Amontonarse justo por encima de un umbral es la firma de un plan de acantilado — es lo que hace una fuerza de ventas cuando los últimos 120 del trimestre valen 2.000,00 y los primeros 99.880 valen un 4% — y esa conducta tiene un reflejo que no sale en ningún informe: el comercial que va por 249.000 a finales de septiembre y no llega a 250.000 tiene 5.000,00 de motivos para que el pedido se firme en octubre.

🎯 Escenario: Ordena la columna de ventas y mira cómo se reparten alrededor de cada umbral. Un histograma con un pico justo encima de una raya y un hueco justo debajo es un plan cambiando la conducta, no un mercado. Es también la auditoría más barata de este artículo: una ordenación y ninguna fórmula.

4) Cómo Encuentra Excel un Tramo

Implementes la lectura que implementes, algo tiene que responder a "¿en qué tramo caen 148.900?". Eso es una búsqueda aproximada, y es el único sitio donde las funciones de búsqueda más usadas de Excel hacen su trabajo más útil.

Pon el baremo en H2:I6, umbrales de menor a mayor, una fila por tramo, y la primera fila tiene que ser el valor más bajo posible — normalmente 0:

Cuatro maneras de sacar un porcentaje de ahí, todas devolviendo el 6% para los 148.900 de Daniel:

=BUSCARV(C9;$H$2:$I$6;2;VERDADERO)         el cuarto argumento es todo el asunto
=BUSCARX(C9;$H$2:$H$6;$I$2:$I$6;;-1)       modo -1: exacto, o el siguiente menor
=INDICE($I$2:$I$6;COINCIDIR(C9;$H$2:$H$6;1))  tipo 1: el mayor valor ≤ el buscado
=BUSCAR(C9;$H$2:$H$6;$I$2:$I$6)            la forma más antigua; siempre aproximada

Todas dicen lo mismo: busca el umbral más grande que no supere al valor y quédate con su fila. Esa es exactamente la definición de un tramo, y por eso un baremo no debería escribirse nunca como una cadena de SI cuando una tabla de dos columnas hace el trabajo.

Tres reglas que la tabla tiene que cumplir, y una trampa que sale de ellas:

1. Umbrales de menor a mayor. BUSCARV(...;VERDADERO) y COINCIDIR(...;1) hacen una búsqueda binaria sobre la columna. Sobre una tabla descendente o desordenada aterrizan en una fila que no tiene nada que ver con la respuesta, devuelven el porcentaje que encuentran allí y no dan ningún error. El -1 de BUSCARX recorre la lista de arriba abajo por defecto y es más indulgente — pero pon modo_de_búsqueda en 2 o -2 para ganar velocidad en una tabla larga y vuelve la exigencia. Ordénala y la pregunta no llega a plantearse.
2. Una fila para el suelo. Borra la fila del 0 y los 49.750 de Kwame quedan por debajo de todo lo que hay en la columna: #N/D, en las cuatro fórmulas. Entonces alguien lo envuelve en SI.ERROR(...;0), que es lo correcto para este plan y deja de serlo en cuanto un plan tenga un porcentaje mínimo.
3. Números, no texto. Un umbral escrito como 50.000 con el punto, o importado como texto, no es un número, y una búsqueda aproximada contra una columna mezclada compara peras con cadenas de texto. Tampoco da error.

La trampa: el cuarto argumento de BUSCARV vale VERDADERO por defecto y el de BUSCARX es la coincidencia exacta. Son valores por defecto opuestos, así que una búsqueda de tramo escrita como =BUSCARX(C9;$H$2:$H$6;$I$2:$I$6) devuelve #N/D para todos los comerciales que no estén justo encima de un umbral — nueve de los diez de aquí — mientras que la misma omisión en BUSCARV te deja calladamente una búsqueda de producto que adivina. Una falla a gritos en las filas correctas y la otra falla en silencio en las equivocadas.

🎯 Escenario: Añade una columna al lado de la liquidación con =BUSCARV(C2;$H$2:$I$6;2;VERDADERO) y compárala con el porcentaje que se pagó de verdad en D2:D11. Diez coincidencias significan que los tramos se aplicaron bien, que es una pregunta distinta de si se aplicaron sobre la base correcta — y resolverla primero evita que la discusión de la sección 6 vaya de dos cosas a la vez.

5) La Escalera de SI, y el Orden en Que Hay Que Escribirla

La mayoría de los baremos de la mayoría de las hojas no son tablas; son un SI anidado escrito por alguien con prisa. Funciona, y tiene un modo de fallo que vale más que el resto de esta sección junta:

=SI(C2>=250000;0,1;SI(C2>=150000;0,08;SI(C2>=100000;0,06;SI(C2>=50000;0,04;0))))

Eso es correcto, y lo es porque está escrito de arriba abajo. SI se detiene en la primera condición verdadera, así que el umbral más alto tiene que probarse primero. Escribe los mismos cuatro tramos en el orden en que aparecen en el baremo:

=SI(C2>=50000;0,04;SI(C2>=100000;0,06;SI(C2>=150000;0,08;SI(C2>=250000;0,1;0))))

y todos los comerciales por encima de 50.000 se llevan un 4%, porque la primera condición es verdadera para todos ellos y a las otras tres no se llega nunca. Nueve de estos diez cobrarían al 4%: la liquidación sumaría 53.609,60 en lugar de 99.398,40, sin error, sin #N/D, y con una fórmula que leída en voz alta suena perfectamente bien. SI.CONJUNTO tiene la misma propiedad y la esconde mejor, porque sus argumentos parecen una tabla:

=SI.CONJUNTO(C2>=250000;0,1;C2>=150000;0,08;C2>=100000;0,06;C2>=50000;0,04;VERDADERO;0)

Sigue ganando la primera verdadera. Sigue teniendo que ir de mayor a menor. El VERDADERO;0 final es el cajón de sastre; quítalo y cualquiera por debajo de 50.000 recibe #N/D.

Y luego está el borde. Rosa Alvarez vendió exactamente 100.000, que es la fila contra la que habría que probar toda escalera y contra la que casi ninguna se prueba. Con >= está en el tramo del 6% y cobra 6.000,00. Cambia un operador por > y baja al 4% y a 4.000,00 — un error de 2.000,00 que afecta a exactamente una fila de diez, en una fórmula que devuelve un número razonable para todas las demás.

Dos cosas que conviene saber sobre eso:

• Lo resuelven las palabras del propio plan. "Un 4% desde 50.000" y "un 4% sobre ventas de 50.000 o más" son >=. "Un 4% sobre ventas superiores a 50.000" es >. Si el plan dice "superiores a", los umbrales del baremo están mal por una unidad, no la fórmula.
• Las fórmulas marginales de las secciones 6 y 7 son inmunes a esto. En exactamente 100.000 la rebanada por encima de 100.000 vale cero, así que no aporta nada uses el operador que uses. El fallo del borde es un fallo de la lectura sobre el total.

🎯 Escenario: Heredes la escalera que heredes, pruébala con los propios valores de los umbrales — 50.000, 100.000, 150.000 y 250.000 — y con esos mismos valores menos 0,01. Ocho celdas, treinta segundos, y pilla el operador, el orden y el cajón de sastre que falta en una sola pasada. Ninguna cifra de ventas real de ningún comercial prueba nada de eso.

6) La Versión Marginal: Base, Umbral y Porcentaje

La lectura marginal es donde la gente se lanza a una fórmula que va sumando rebanadas, y siempre acaba en un anidamiento ilegible. Hay una manera estándar de hacerlo, y es la manera en que todas las agencias tributarias publican sus propias tablas: darle a cada tramo una base — el total que ha ganado quien ha subido entero por todos los tramos de debajo.

Añade la columna J al baremo:

No escribas esa columna a mano. Constrúyela, en J3, y cópiala hacia abajo:

=J2+(H3-H2)*I2

Cada base es la de debajo más el ancho completo del tramo de debajo al porcentaje de ese tramo: 0 + 50.000 × 4% = 2.000, luego 2.000 + 50.000 × 6% = 5.000, luego 5.000 + 100.000 × 8% = 13.000. Una columna de bases escrita a mano es correcta el día que se escribe y queda calladamente mal la primera vez que alguien cambia un porcentaje — que es justo el día en que todo el mundo está mirando los totales y nadie está mirando la columna J.

Con eso, la comisión es una sola línea, en F2:

=BUSCARV(C2;$H$2:$J$6;3;VERDADERO) + (C2-BUSCARV(C2;$H$2:$J$6;1;VERDADERO)) * BUSCARV(C2;$H$2:$J$6;2;VERDADERO)

la base del tramo, más la parte de las ventas que pasa del umbral de ese tramo, a su porcentaje. Para los 248.900 de Aisha: 5.000,00 + (248.900 − 150.000) × 8% = 5.000,00 + 7.912,00 = 12.912,00. Para los 262.540 de Sofia: 13.000,00 + 12.540 × 10% = 14.254,00.

El BUSCARV(...;1;VERDADERO) del medio parece raro — busca el umbral dentro de su propia columna — y es el truco que convierte todo esto en una sola fórmula: devuelve el umbral del tramo en el que cayó el valor, así que nunca hace falta saber qué fila era. En el Excel moderno lo mismo se lee mejor, porque LET permite encontrar la fila una sola vez:

=LET(f; COINCIDIRX(C2;$H$2:$H$6;-1);
     INDICE($J$2:$J$6;f) + (C2-INDICE($H$2:$H$6;f)) * INDICE($I$2:$I$6;f))

Un COINCIDIRX en lugar de tres búsquedas: más rápido en una liquidación larga, y dice lo que quiere decir. El -1 es el "exacto o el siguiente menor" de COINCIDIRX, la misma idea que el VERDADERO de BUSCARV.

🎯 Escenario: Copia F2 hacia abajo y suma la columna. Si da 50.398,40, el baremo está bien conectado. Después cambia el 8% de I5 por un 9% y mira cómo se mueve sola la columna de bases — 13.000,00 pasa a 14.000,00, y con ella la comisión de Sofia. Esa columna que se actualiza sola es la razón entera para mantener el baremo como datos y no como texto dentro de una fórmula.

7) Una Sola Celda, Sin Columna de Bases: SUMAPRODUCTO

Hay una versión sin ninguna columna auxiliar, y es la que hay que conocer si alguna vez tienes que meter un cálculo marginal dentro del modelo de otra persona. Añade una columna de porcentaje diferencial — cuánto añade el porcentaje de cada tramo al del tramo de debajo — en K2, copiada hacia abajo:

=I2-I1

Y entonces el cálculo marginal entero es:

=SUMAPRODUCTO((C2>$H$2:$H$6)*(C2-$H$2:$H$6)*$K$2:$K$6)

Para Tomas, con 100.120: (100.120 − 0) × 0% + (100.120 − 50.000) × 4% + (100.120 − 100.000) × 2% = 0 + 2.004,80 + 2,40 = 2.007,20. Los dos tramos que quedan por encima de él los apaga la comparación (C2>$H$2:$H$6), que devuelve VERDADERO/FALSO y se convierte en unos y ceros al multiplicar.

Por qué funciona merece diez segundos: cobrar el porcentaje extra de cada umbral sobre todo lo que pasa de ese umbral da el mismo resultado que cobrar el porcentaje completo de cada tramo sobre su propia rebanada. Es la reescritura estándar de una función por tramos, y por eso las tablas de Hacienda y esta fórmula coinciden al céntimo.

La compuerta no es opcional. Quita el (C2>$H$2:$H$6) y los tramos por encima del comercial aportan números negativos, porque ahí C2 − umbral es negativo. Tomas sale a −1.988,00 — ni error, ni cero, solo un número rotundamente equivocado con un signo menos que alguien leerá como una devolución.

Y a diferencia de las escaleras de la sección 5, a esta fórmula le da igual > que >=: en exactamente 100.000 la rebanada por encima de 100.000 vale cero de las dos maneras, y por eso la fila de Rosa da los mismos 2.000,00 con las dos.

🎯 Escenario: Construye las dos — la de la columna de bases de la sección 6 y esta — en dos columnas contiguas, y réstalas. Tienen que dar cero en las diez filas. Dos fórmulas deducidas de maneras distintas que coinciden exactamente son lo más parecido a una demostración que ofrece una hoja de cálculo; dos que coinciden porque una se copió de la otra no demuestran nada.

8) El Total No Es la Suma de las Partes

Este es el error que sobrevive a cualquier revisión, porque la fórmula está bien y el rango está mal.

Las cuatro zonas vendieron esto:

La columna de la derecha es lo que sale de hacer lo obvio: =SUMAR.SI.CONJUNTO($C$2:$C$11;$B$2:$B$11;"Norte") para las ventas de la zona, y luego la fórmula de la sección 7 sobre ese total. Sobrevalora la comisión en 41.079,60, y pasar los 1.389.990 de toda la empresa por la escala como una sola cifra es aún peor: 126.999,00, dos veces y media la verdad.

Un tramo no es aditivo. La escala está definida sobre el trimestre de una persona, así que solo se puede aplicar al trimestre de una persona. El orden correcto es: comisión por comercial primero, y después sumar lo que se quiera.

=SUMAR.SI.CONJUNTO($F$2:$F$11;$B$2:$B$11;"Norte")       suma las comisiones      → 20.015,20
=SUMAPRODUCTO(--($B$2:$B$11="Norte");$F$2:$F$11)        lo mismo, sintaxis antigua

La misma trampa tiene un eje temporal, y es la que se cuela en los planes de verdad. Paga esta escala mensualmente y un comercial que venda 46.000 en cada uno de tres meses no gana nada en absoluto — todos los meses quedan por debajo del primer umbral. Esos mismos 138.000 medidos por trimestre ganan 4.280,00 en marginal, u 8.280,00 bajo la lectura sobre el total. Ninguna de las tres cifras está mal; son respuestas a preguntas distintas. El plan tiene que nombrar el periodo sobre el que se mide la escala, y una hoja que suma tres liquidaciones mensuales no está calculando la trimestral.

🎯 Escenario: Pon dos celdas etiquetadas arriba de la hoja: =SUMA(F2:F11), las comisiones sumadas, y la misma fórmula del baremo apuntando a =SUMA(C2:C11), la escala aplicada al total de ventas. Aquí marcan 50.398,40 y 126.999,00. En cuanto esos dos números están juntos en pantalla, nadie de la sala vuelve a aplicar un baremo a un subtotal.

9) Dónde Redondear, y la Columna de Porcentajes Que Miente

Dos cosas pequeñas que acaban en reuniones de conciliación.

Redondea una vez, en la fila. La comisión es dinero, así que hay que redondearla al céntimo donde se calcula, no dejarla con quince decimales para que la redondee el formato de número:

=REDONDEAR(<la fórmula marginal>;2)

Si redondeas solo el total, el total no cuadrará con la suma de lo que cada comercial ve en su nómina — nunca por mucho, siempre por lo bastante como para costar una tarde. Si redondeas en la fila, el total es la suma de los números que la gente cobró de verdad, que es a lo que tiene que cuadrar un fichero de nóminas.

La columna de porcentajes muestra 8% y guarda lo que guarde. D2:D11 en esta liquidación tiene formato de porcentaje sin decimales. Un porcentaje de 0,0825 se muestra como 8% con ese formato, y 0,0825 × 176.300 son 14.544,75, no 14.104,00. El formato de porcentaje sin decimales es la manera más eficaz que existe de esconder un porcentaje equivocado a plena vista, porque la columna se parece exactamente al baremo. En cualquier hoja que pague a personas, da uno o dos decimales a las columnas de porcentaje, y comprueba un porcentaje pinchando la celda, no leyendo la columna.

Y su primo ruidoso: un porcentaje introducido como 4 en lugar de 0,04 o 4%. Los 100.120 de Tomas por "4" son 600.720,00 — un error tan grande que se pilla en el mismo minuto, lo que lo convierte en el fallo menos peligroso de este artículo.

🎯 Escenario: Amplía D2:D11 a cuatro decimales durante diez segundos. Cualquier porcentaje que no sea exactamente lo que dice el baremo se delatará solo, y luego puedes devolver el formato.

10) Revisar una Liquidación Que Hizo Otro

No hace falta rehacer una liquidación de comisiones para auditarla. Hace falta una columna y dos celdas.

Recalcula la comisión de cada comercial a partir del baremo con la fórmula de la sección 7 en G2, y luego marca las diferencias:

=SI(REDONDEAR(E2-G2;2)<>0;"REVISAR";"")

El REDONDEAR(...;2) importa: comparar dos resultados en coma flotante con <> marcará filas que difieren en 0,0000000001, y una columna de "REVISAR" contra diez números idénticos enseña a todo el mundo a ignorar la columna. Y luego una celda para toda la liquidación:

=SUMA(E2:E11)-SUMA(G2:G11)

que aquí son 49.000,00 — el 97,2% de lo que la liquidación debería haber pagado, en una sola celda, en un libro donde cada fórmula individual era correcta. Medido contra las ventas, la liquidación pagó el 7,15% de 1.389.990 donde el plan describe un 3,63%. Esos dos porcentajes son la versión de todo esto sobre la que actúa un director financiero, porque un plan de comisiones se presupuesta como porcentaje de los ingresos y este va a casi el doble de su presupuesto.

🎯 Escenario: Siempre que heredes una columna calculada, reconstrúyela una vez en la columna siguiente y resta. Si la diferencia es cero, borra tu columna y fíate de la hoja. Si no lo es, has encontrado o un fallo o una regla que nadie te contó, y las dos cosas valen cinco minutos.

11) Doce Trampas

1. La tabla de tramos no está ordenada de menor a mayor. BUSCARV(...;VERDADERO) y COINCIDIR(...;1) la recorren con búsqueda binaria y devuelven el porcentaje de una fila en la que no tenían nada que hacer. Sin error, nunca.
2. Falta la fila de abajo. Sin un umbral 0, todos los que estén por debajo del primer tramo reciben #N/D — y el SI.ERROR(...;0) que viene detrás pasa a estar mal el día que el plan tenga un porcentaje mínimo.
3. BUSCARX sin el -1. Su valor por defecto es la coincidencia exacta, justo lo contrario que BUSCARV, así que una búsqueda de tramo devuelve #N/D para todos los que no estén justo encima de un umbral.
4. BUSCARV sin FALSO en otro sitio de la hoja. El mismo valor por defecto que hace cómodas las búsquedas de tramo hace que las búsquedas de producto adivinen. Las dos conviven en un mismo libro; solo una de ellas quiere VERDADERO.
5. La escalera de SI escrita de menor a mayor. Gana la primera verdadera, así que todos los que pasan del umbral más bajo cobran al porcentaje más bajo. Aquí eso paga 53.609,60 en lugar de 99.398,40, y leído en voz alta suena bien.
6. > donde el plan dice "desde". Rosa vendió exactamente 100.000. Un operador la mueve 2.000,00, y ninguna otra fila del archivo puede detectarlo.
7. Un baremo aplicado a un subtotal. Los totales por zona pasados por esta escala dan 91.478,00 frente a los 50.398,40 verdaderos; el total de la empresa da 126.999,00. La fórmula está bien y el rango está mal.
8. Una columna de bases escrita a mano. Es correcta hasta que cambia un porcentaje, y entonces se queda obsoleta en silencio. Constrúyela con =J2+(H3-H2)*I2.
9. SUMAPRODUCTO sin la compuerta (C2>umbrales). Los tramos por encima del valor aportan cantidades negativas; Tomas sale a −1.988,00 en lugar de 2.007,20.
10. Umbrales guardados como texto. 50.000 escrito con el punto, o importado de un sistema, se compara como texto contra un número y no coincide con nada con sentido.
11. Formato de porcentaje sin decimales. 0,0825 y 0,08 se muestran los dos como 8%, y solo uno de los dos está en el baremo.
12. Ventas negativas y abonos. Un pedido devuelto puede dejar a un comercial por debajo de un umbral después de haberle pagado al porcentaje de arriba; bajo un plan sobre el total, esa devolución de comisión es del tamaño de la cantidad fija, no del tamaño del abono. Decide en el plan si los tramos se recalculan sobre el año acumulado o se cierran cada trimestre, porque la hoja hará encantada cualquiera de las dos cosas.

12) La Lista de Comprobación

• El plan dice, por escrito, si los porcentajes se aplican a todas las ventas o solo a la parte que pasa de cada umbral
• El baremo es una tabla en celdas, no números escritos dentro de una fórmula
• Umbrales de menor a mayor, una fila por tramo, y una fila para el suelo de la escala
• Umbrales y porcentajes son números, y la columna de porcentajes tiene decimales
• Toda búsqueda aproximada declara su modo de forma explícita: VERDADERO, -1 o 1
• Toda escalera de SI/SI.CONJUNTO está escrita del tramo más alto hacia abajo y termina en un cajón de sastre
• La fórmula se ha probado con cada valor de umbral exacto, y con cada uno menos 0,01
• Toda columna de bases o de diferenciales está calculada, no escrita
• La comisión se calcula por persona y por periodo, y solo después se suma
• Redondeado al céntimo en la fila, no en el total
• Alguien ha mirado cómo se reparten las ventas alrededor de cada umbral

Ejercicios

Usa la tabla de arriba y el baremo 0/50.000/100.000/150.000/250.000 al 0/4/6/8/10%.

1. Las dos lecturas. Construye la comisión sobre el total y la comisión marginal en dos columnas y suma cada una. Tienen que dar 99.398,40 y 50.398,40. Después escribe la fórmula de una celda para la diferencia.
2. Encontrar el tramo. Escribe la búsqueda del porcentaje de cuatro maneras — BUSCARV, BUSCARX, INDICE/COINCIDIR y BUSCAR — y comprueba que las cuatro devuelven un 6% para 148.900. Después ordena el baremo de mayor a menor y di cuáles de las cuatro siguen dando la respuesta correcta.
3. Rómpelo a propósito. Borra la fila del 0 del baremo. ¿Qué comercial se rompe, qué devuelve, y cuál es el cambio más pequeño que lo arregla sin un SI.ERROR?
4. El borde. Calcula la comisión de Rosa de las dos maneras con >= y con >. Di cuál es la diferencia bajo cada lectura, y por qué una de las dos no se entera.
5. Sin columna auxiliar. Escribe la comisión marginal como un solo SUMAPRODUCTO, luego borra la comparación (C2>...) y anota lo que devuelve cada uno de los diez comerciales. ¿Cuántos de los diez se van a negativo?
6. La trampa de agregar. Suma las ventas por zona con SUMAR.SI.CONJUNTO, pasa cada total de zona por la escala y compáralo con la suma de las comisiones individuales. Después di en una frase por qué la segunda es la única cifra defendible.

Resumen

Un tramo plantea dos preguntas, y una hoja de cálculo solo responderá siempre a la segunda. En qué tramo cae esto es una búsqueda — aproximada, con los umbrales ascendentes, una fila para el suelo y el modo escrito explícitamente en lugar de dejado a un valor por defecto que es distinto en BUSCARV y en BUSCARX. A qué se aplica el porcentaje no es una pregunta de hoja de cálculo en absoluto. Es una frase de un plan, y hasta que alguien la escriba, las dos respuestas están a una fórmula de distancia y ninguna de las dos dará jamás un error.

Así que: cierra primero la lectura, con palabras. Mantén el baremo como datos, con la columna de bases o de diferenciales calculada y no escrita. Prueba sobre los propios umbrales, que es donde vive cada error de una unidad. Aplica la escala a una persona y a un periodo, y suma después — nunca al revés. Y cuando heredes una liquidación, reconstruye una columna al lado y resta, porque la diferencia es un solo número y un solo número es lo único que consigue parar un pago.

La alternativa es este trimestre: diez porcentajes correctos, diez multiplicaciones correctas, 99.398,40 fuera por la puerta, y 50.398,40 en el plan que los autorizó.