Corregir suma de porcentajes redondeados.

Cuando sumamos números que hemos redondeado suele presentarse el problema que, aparentemente, Excel no suma los números correctamente.

Supongamos que queremos repartir un bono entre distintos vendedores, de acuerdo a la parte proporcional de cada uno en el total de las ventas. Dado que somos expertos en Excel sólo nos toma unos momentos preparar el informe

Nuestro jefe nos pide que presentemos el informe con números enteros, prescindiendo de los centavos. Ningún problema! Ajustamos el formato de los números




Pero nuestro jefe tiene ojo de lince y advierte que el total de las sumas a distribuir en la columna D suman 9999! Esto por supuesto no es un “bug” de Excel ni tampoco un problema matemático. Sencillamente, la parte decimal oculta completa el 1 que nos está faltando.

Supongamos ahora que usamos la función REDONDEAR para hacer el ajuste de las sumas. También en este caso nos topamos con el problema

Hay varias formas de solucionar el problema y todo depende del gusto personal de cada uno (o del jefe de cada uno).

Una posibilidad es cargar la diferencia en forma arbitraria a alguno de los vendedores, por ejemplo al último de la lista. Hacemos esto poniendo en la celda del último vendedor la diferencia entre el total a repartir y el total de bonos repartidos a los demás vendedores

En la celda D8 ponemos la fórmula =D9-SUMA(D4:D7), donde D9 es el total a repartir y SUMA(D4:D7) el total de bonos repartidos exceptuando el último vendedor.
Esta fórmula se “autoajusta” y nos garantiza que siempre el total de las sumas redondeadas coincida con la suma a distribuir.

Otra posibilidad es usar una fórmula que asigne automáticamente la diferencia al vendedor con los mejores resultados, en nuestro caso José. Podemos hacer esto usando una columna auxiliar para determinar dinámicamente quién es el vendedor con los mejores resultados usando la función JERARQUIA

En las celdas en el campo Auxiliar (columna D) usamos la fórmula

=JERARQUIA(B4,$B$4:$B$8)

En el campo Distribución (columna E) usamos la fórmula

=REDONDEAR($B$1*C4,0)+(D4=1)

La primer parte de la fórmula calcula el bono del vendedor y lo redondea. La segunda parte controla si es el mejor vendedor (JERARQUIA =1) y en caso de ser cierto agrega 1.

Claro que esta solución es parcial y se aplica sólo al caso que la diferencia sea +1.

Para hacer esta solución más general podemos usar dos campos auxiliares. En el primero calculamos los bonos redondeados; en el segundo usamos JERARQUIA como en el caso anterior. Luego podemos usar la diferencia entre el total de Auxiliar 1 (celda D9) y bono a distribuir (celda B1) para ajustar el resultado poniendo la fórmula

=D4+((E4=1)*($B$1-$D$9))

en el campo Distribución (columna F)

Technorati Tags: MS Excel