01 / 11Los Porcentajes Redondeados Cobraron 25,70 £ de Más

RedondeoREDONDEARAuditoría de Fórmulas

Repartir un total en Excel sin que falten céntimos

Ocho Recibos de Comunidad Sumaron 12.875,70 £ por una Factura de 12.850 £, Porque la Hoja Redondeó los Porcentajes Antes que el Dinero

14 nov 20269 min de lectura

UsaREDONDEARSUMAABSMIN, MAX

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étodoTotal de recibosFrente a la factura
Porcentajes redondeados12.875,70 £25,70 £ de más
Cada parte redondeada al penique12.850,03 £3 peniques de más
Total acumulado redondeado12.850,00 £Exacto

Alcance. REDONDEAR, SUMA, MAX y ABS funcionan 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.

ABCDEFG
1
Flat
Area (m²)
Lease %
Old demand
Exact share
Rounded share
Balanced demand
2
Flat 1
65
11.6%
1490.6
1491.5179
1491.52
1491.52
3
Flat 2
88
15.7%
2017.45
2019.2857
2019.29
2019.28
4
Flat 3
85
15.2%
1953.2
1950.4464
1950.45
1950.45
5
Flat 4
62
11.1%
1426.35
1422.6786
1422.68
1422.68
6
Flat 5
49
8.8%
1130.8
1124.375
1124.38
1124.37
7
Flat 6
91
16.3%
2094.55
2088.125
2088.13
2088.13
8
Flat 7
49
8.8%
1130.8
1124.375
1124.38
1124.37
9
Flat 8
71
12.7%
1631.95
1629.1964
1629.2
1629.2
10
Total
560
100.2%
12875.7
12850
12850.03
12850
11
Bill
12850
12850
12850
12850

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.

Pruébalo en la cuadrícula

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.

PisoRecibo antiguoParte redondeadaDiferencia
Piso 31.953,20 £1.950,45 £2,75 £ de más
Piso 51.130,80 £1.124,38 £6,42 £ de más
Piso 22.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.

Pruébalo en la cuadrícula

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.

Pruébalo en la cuadrícula

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 pantalla redondea 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.

Pruébalo en la cuadrícula

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.

PisoParte exactaParte redondeadaRecibo cuadrado
Piso 22.019,28572.019,292.019,28
Piso 51.124,37501.124,381.124,37
Piso 71.124,37501.124,381.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.

Pruébalo en la cuadrícula

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.

Pruébalo en la cuadrícula

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)
CuotaREDONDEAR soloTotal acumulado
14.283,33 £4.283,33 £
24.283,33 £4.283,34 £
34.283,33 £4.283,33 £
Total12.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.

Pruébalo en la cuadrícula

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ícula

09Cinco Trampas

  1. 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.
  2. Redondear cada fila y fiarse del total. Ocho redondeos correctos sumaron 3 peniques de más. Redondea el total acumulado.
  3. Esconder los peniques con un formato. Dos decimales muestran lo que imprimen las cartas, no lo que suma la hoja.
  4. Teclear un penique encima de la parte mayor. Un 2088,12 escrito a mano cuadra este trimestre y falla en cuanto cambia la factura.
  5. 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

  1. Reprodúcelo. Monta la hoja con porcentajes redondeados y confirma que los recibos suman 12.875,70 £.
  2. Encuentra los peniques. Cambia a REDONDEAR en cada parte y confirma que el total es 12.850,03 £.
  3. Cuádralo. Añade la columna del total acumulado y confirma que suma 12.850,00 £ con cada piso a menos de un penique.
  4. 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.

Comparte este artículo:
Volver al Blog

Practícalo

REDONDEAR

Un ejercicio en una cuadrícula real

Abrir el ejercicio

Las funciones que usa este artículo

Sintaxis, ejemplos resueltos y los errores que esperar: una página de referencia para cada una.

Sigue leyendo

  1. Cómo detectar valores fijos en fórmulas de ExcelLeer el artículo
  2. Redondear a cajas completas en Excel con REDONDEAR.MASLeer el artículo
  3. Sumar horas en Excel: más de 24 y horas extraLeer el artículo
Reto diario · Día 107

Suma los kilómetros de un periodo de liquidación con SUMAR.SI.CONJUNTO

Un ejercicio nuevo cada día, resuelto en una cuadrícula real.

Resolver el reto de hoy