Corregir suma de porcentajes redondeados.

domingo, abril 05, 2009

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:

5 comments:

Guillain Barré 08 abril, 2009 01:53  

Muchas Gracias Jorge, esto complementa a la consulta que te efectue meses atrás.
Saludos

ralde 03 mayo, 2009 07:16  

Estimado Jorge excelente el trabajo sobre efectos especiales sobre bordes.
Una inquietud, será posible que las esquinas de los bordes sean líneas curvas.
muchas gracias por tu colaboración
Saludos
Hernan Alvarado Ralde

Jorge L. Dunkelman 03 mayo, 2009 20:17  

Hernán
tendrías que haber dejado el comentario en la nota adecuada.
No se me ocurre cómo lograr bordes redondeados con la técnica que muestro en esa nota.

gatmorgan 08 mayo, 2009 20:50  

El truco básico del redondeo es más sencillo.

Suma 0,05 a cada cantidad antes de redondear.

No es a ojo, tiene explicación matemática y lógica

Jorge L. Dunkelman 09 mayo, 2009 17:57  

No me parece que funcione en todos los casos. En el ejemplo de la nota, si cambiamos la suma de las ventas de Juán a 26.761,14 y aplicamos tu propuesta, el total del bono después de prorrateo es 9.999.

Publicar un comentario

Google+ Followers

Seguidores

Google+ Badge

Términos Legales

  © Blogger template On The Road by Ourblogtemplates.com 2009

Back to TOP