Ravensworth Court reparte sus gastos trimestrales entre ocho pisos según la superficie. La factura fue de 12.850,00 £. Los ocho recibos sumaron 12.875,70 £, y el propietario del piso 5 los sumó.
| Método | Total de recibos | Frente a la factura |
|---|---|---|
| Porcentajes redondeados | 12.875,70 £ | 25,70 £ de más |
| Cada parte redondeada al penique | 12.850,03 £ | 3 peniques de más |
| Total acumulado redondeado | 12.850,00 £ | Exacto |
Alcance.
REDONDEAR,SUMA,MAXyABSfuncionan en todas las versiones de Excel.=MAX(ABS(...))sobre un rango funciona directamente en Excel 365 y 2021.En versiones anteriores, confírmala con Ctrl+Mayús+Intro.
01Los Porcentajes Redondeados Cobraron 25,70 £ de Más
La hoja convertía primero la superficie de cada piso en un porcentaje, redondeado a un decimal para que la carta mostrara una cifra limpia. Después cobraba ese porcentaje de la factura. En C2 y D2, copiadas hasta la fila 9:
C2: =REDONDEAR(B2/$B$10;3)
D2: =REDONDEAR($D$11*C2;2)
El reparto trimestral de Ravensworth Court
Ocho pisos en A2:G9, los totales en la fila 10 y la factura, 12.850,00 £, en D11. C es el porcentaje antiguo, redondeado al 0,1 %, y D el recibo calculado con él. E es la parte exacta de cada piso según su superficie, F esa parte redondeada al penique y G el recibo cuadrado con el total acumulado.
fxLas celdas con fórmulas están resaltadas en verde
Pasa el mouse sobre las celdas con fórmulas para ver la fórmula y resaltar las celdas referenciadas
Cada porcentaje se movió hasta 0,05 puntos, y los movimientos no se compensaron. Los ocho porcentajes suman el 100,2 %, así que los recibos pedían el 100,2 % de la factura: 25,70 £ más de lo que se pagó a los proveedores.
El piso 5 tiene 49 m² de 560, el 8,75 %. Redondeado pasó a 8,8 %, y el recibo fue de 1.130,80 £ frente a unas justas 1.124,38 £.
Escenario: Escribe las ocho superficies en B2:B9 y =SUMA(B2:B9) en B10. Pon =REDONDEAR(B2/$B$10;3) en C2, cópiala hacia abajo y comprueba que =SUMA(C2:C9) devuelve 1,002 y no 1.
02Parte de las Superficies y Redondea Solo el Dinero
El porcentaje es un paso que sobra. Divide la factura entre la superficie total y multiplícala por la del piso, en una sola fórmula, y redondea solo el resultado:
=REDONDEAR($D$11*B2/$B$10;2)
Cada piso queda ahora a menos de medio penique de su parte exacta, que la columna E muestra sin redondear.
Si la carta debe mostrar un porcentaje, muestra =B2/$B$10 con formato de porcentaje. El formato cambia lo que ves, no lo que cobra la fórmula.
| Piso | Recibo antiguo | Parte redondeada | Diferencia |
|---|---|---|---|
| Piso 3 | 1.953,20 £ | 1.950,45 £ | 2,75 £ de más |
| Piso 5 | 1.130,80 £ | 1.124,38 £ | 6,42 £ de más |
| Piso 2 | 2.017,45 £ | 2.019,29 £ | 1,84 £ de menos |
A seis pisos se les cobró de más y a dos de menos. Esos dos son la razón de que nadie lo notara mirando una sola carta.
Escenario: Escribe =REDONDEAR($D$11*B2/$B$10;2) en F2 y cópiala hasta F9. Comprueba que F6 devuelve 1.124,38 y que =SUMA(F2:F9) queda a pocos peniques de la factura de D11.
03Redondear Cada Parte Dejó 3 Peniques de Más
La columna corregida suma 12.850,03 £. Redondear cada fila al penique sigue siendo ocho pequeños errores, y nada hace que se compensen.
En Ravensworth Court todos fueron en la misma dirección. Los pisos 5, 6 y 7 dan exactamente medio penique: 1.124,375, 2.088,125 y 1.124,375 £.
REDONDEAR aleja de cero la mitad, así que los tres subieron, y los otros cinco también se redondearon hacia arriba por casualidad.
Tres peniques parecen nada. Pero los recibos son un libro de cuentas, y un libro que no cuadra con la factura falla en todas las conciliaciones posteriores.
Escenario: Escribe =$D$11*B2/$B$10 en E2 y cópiala hacia abajo, y después da formato a E2:E9 con cuatro decimales. Comprueba que E6, E7 y E8 terminan en 0,0050, los medios peniques que REDONDEAR sube.
04Los Decimales Esconden los Peniques, No los Quitan
Un atajo tentador es dejar la columna E sin redondear y darle formato con dos decimales. La columna suma entonces exactamente 12.850,00 £, porque las fracciones ocultas se suman.
Pero las cartas muestran 1.124,38 para los pisos 5 y 7, y los propietarios pagan lo que se imprime. Los peniques que esconde el formato son los mismos 3, cobrados en el banco en lugar de en la hoja.
Archivo > Opciones > Avanzadas > Establecer precisión de pantallaredondea cada valor guardado a su formato.Cambia todo el libro de forma permanente y no se puede deshacer, así que no sirve para corregir una columna.
Escenario: Da formato a E2:E9 con dos decimales y escribe =SUMA(E2:E9) debajo. Comprueba que muestra 12.850,00, y después escribe las ocho cifras que ves en otra columna y súmalas.
05Redondea el Total Acumulado y Cobra la Diferencia
La solución es redondear donde se acumulan las partes, no fila a fila. Redondea cada total acumulado al penique y cobra a cada piso el salto desde el anterior. En G2, copiada hasta G9:
=REDONDEAR($D$11*SUMA($B$2:B2)/$B$10;2)-SUMA(G$1:G1)
SUMA($B$2:B2) es la superficie hasta ese piso, así que la primera parte es lo que deben todos hasta él. SUMA(G$1:G1) es lo que ya se ha cobrado arriba.
SUMA ignora el encabezado de texto de G1, así que la primera fila no necesita un caso especial.
El último total acumulado es toda la superficie, así que se redondea a la factura completa. La columna G suma 12.850,00 £ por construcción, sean cuales sean las superficies.
| Piso | Parte exacta | Parte redondeada | Recibo cuadrado |
|---|---|---|---|
| Piso 2 | 2.019,2857 | 2.019,29 | 2.019,28 |
| Piso 5 | 1.124,3750 | 1.124,38 | 1.124,37 |
| Piso 7 | 1.124,3750 | 1.124,38 | 1.124,37 |
Tres pisos pagan un penique menos de lo que les daba REDONDEAR. Todos siguen a menos de un penique de su parte exacta.
El orden de las filas decide qué piso se queda con el penique, así que ordena la lista igual cada trimestre.
Escenario: Escribe =REDONDEAR($D$11*SUMA($B$2:B2)/$B$10;2)-SUMA(G$1:G1) en G2 y cópiala hasta G9. Comprueba que G6 devuelve 1.124,37 y que =SUMA(G2:G9) devuelve 12.850,00.
06Ajustar la Última Fila Funciona, con un Coste
La otra solución habitual es una cifra de ajuste. Redondea todas las filas menos la última y cobra a la última lo que quede:
=$D$11-SUMA(H2:H8)
Cuadra, pero el redondeo de todas las filas cae en un solo piso. Al piso 8 se le cobran 1.629,17 £ frente a unas exactas 1.629,1964 £, casi 3 peniques de menos.
Con ocho pisos son peniques. En un reparto de costes de 400 líneas, la última recoge el redondeo de 399 filas, y lo paga el centro de coste que por casualidad queda el último. El total acumulado mantiene cada fila a menos de un penique.
Un tercer método, el del mayor resto, redondea todas las partes hacia abajo y reparte los peniques sobrantes entre las filas con mayor fracción.
Es el reparto más justo, pero necesita una columna de clasificación y da los mismos totales que el total acumulado.
Escenario: Escribe =REDONDEAR($D$11*B2/$B$10;2) en H2:H8 y =$D$11-SUMA(H2:H8) en H9. Comprueba que H9 devuelve 1.629,17 y compáralo con E9.
07Las Cuotas Iguales Necesitan el Mismo Truco
Dividir un importe en pagos iguales es el mismo problema con pesos iguales. =REDONDEAR($D$11/3;2) da 4.283,33 £, y tres cuotas así suman 12.849,99 £.
Numera las cuotas del 1 al 3 en A14:A16 y vuelve a usar el total acumulado:
=REDONDEAR($D$11*A14/3;2)-REDONDEAR($D$11*(A14-1)/3;2)
| Cuota | REDONDEAR solo | Total acumulado |
|---|---|---|
| 1 | 4.283,33 £ | 4.283,33 £ |
| 2 | 4.283,33 £ | 4.283,34 £ |
| 3 | 4.283,33 £ | 4.283,33 £ |
| Total | 12.849,99 £ | 12.850,00 £ |
El penique va a la cuota del medio porque es ahí donde el total acumulado se redondea hacia arriba por primera vez. Cambia el 3 por 12 para pagos mensuales.
Escenario: Escribe 1, 2 y 3 en A14:A16 y la fórmula de arriba en B14, y cópiala hasta B16. Comprueba que B15 devuelve 4.283,34 y que =SUMA(B14:B16) devuelve 12.850,00.
08Comprueba el Reparto Antes de Enviarlo
Dos comprobaciones detectan los dos fallos de esta hoja. La primera pregunta si los recibos suman la factura:
=REDONDEAR(SUMA(G2:G9)-$D$11;2)
Debe devolver 0. Apuntada a la columna D devuelve 25,7, y a la columna F, 0,03. El REDONDEAR evita que una cola binaria como 0,0000000001 haga fallar un reparto correcto.
La segunda pregunta si algún piso sale perjudicado:
=MAX(ABS(G2:G9-E2:E9))
Devuelve 0,0057 con el total acumulado, menos de un penique. Con la columna ajustada devuelve 0,0264, y con la antigua 6,425, el cobro de más de los pisos 5, 6 y 7.
Escenario: Escribe las dos comprobaciones encima de los recibos y apúntalas a D2:D9. Comprueba que la primera devuelve 25,7, y después cámbialas a G2:G9 y comprueba que devuelve 0.
Pruébalo en la cuadrícula09Cinco Trampas
- Redondear un porcentaje y después multiplicar. Cada peso redondeado está un poco mal, y la factura se multiplica por todos ellos. Redondea el dinero, nunca el peso.
- Redondear cada fila y fiarse del total. Ocho redondeos correctos sumaron 3 peniques de más. Redondea el total acumulado.
- Esconder los peniques con un formato. Dos decimales muestran lo que imprimen las cartas, no lo que suma la hoja.
- Teclear un penique encima de la parte mayor. Un
2088,12escrito a mano cuadra este trimestre y falla en cuanto cambia la factura. - Reordenar la lista entre trimestres. El total acumulado da el penique suelto a la fila que cruza la mitad. Mantén el orden fijo.
10Cuatro Ejercicios
- Reprodúcelo. Monta la hoja con porcentajes redondeados y confirma que los recibos suman 12.875,70 £.
- Encuentra los peniques. Cambia a
REDONDEARen cada parte y confirma que el total es 12.850,03 £. - Cuádralo. Añade la columna del total acumulado y confirma que suma 12.850,00 £ con cada piso a menos de un penique.
- Divídelo. Reparte 12.850,00 £ en 12 cuotas mensuales y confirma que suman la factura.
Qué Llevarse
Las superficies eran correctas y la factura también. La hoja redondeó los pesos antes que el dinero, y después redondeó ocho partes que fueron todas en la misma dirección.
Cobra a partir de las superficies, redondea el total acumulado y comprueba la suma y la peor fila antes de enviar los recibos.