lunes, 9 de abril de 2012

Cuadrar suma de sumandos redondeados en Excel.

En ocasiones nuestras operaciones en Excel nos devuelven resultados inesperados, por ejemplo, algo muy típico es que al realizar un reparto proporcional sobre una cantidad, si aplicamos redondeo de dos decimales, al sumar ese reparto, el sumatorio obtenido no coincide con la cantidad repartida, siempre hay una pequeña diferencia.
Veamos la cuestión planteada:

...En un reparto proporcional realizado en excel, en el cual se incluyen y se redondea a dos decimales, el resultado de sumar con la formula =suma( ), no es el mismo
Ejemplo repartir 42,11 €
89,95 37,88 esta saldría de =D13*E7/100 de donde D13 seria 42,11 y E7 seria 85,95
2,89 1,22
2,89 1,22
1,16 0,49
0,57 0,24
2,54 1,07
Total 42,11 Estos 42,11 serian autosuma.
Pues si lo hacemos con una calculadora, el resultado seria 42,12 que el es real.
¿Como puedo hacer para que el resultado sea el real, y no tener que comprobarlo con la calculadora?...

Esto es normal, es un tema matemático (no de Excel); fijémosnos en el planteamiento, en el que se comenta que se realiza un redondeo a dos decimales cada operación, por lo que realmente estamos perdiendo precisión en el cálculo final acumulado, ya que los redondeos individuales nos llevan en algunos casos, como en el planteado a un error de +/- 0.01.
La solución más sencilla sería NO redondear las operaciones, así nos aseguraríamos que el reparto coincide con la cantidad a repartir. Pero si por algún motivo se necesitara este redondeo, habría que 'forzar' el cuadre, añadiendo a uno de los elementos esa diferencia... que es lo que vamos a explicar en este post.

Veamos en una primera imagen como aplicando una fórmula sin REDONDEAR conseguimos el reparto perfecto:
=$B$1*A2/100

Cuadrar suma de sumandos redondeados en Excel.


Como podemos observar, la suma resultante cuadra perfectamente con la cantidad de inicio, lógicamente al no aplicar redondeo en nuestra operación no tendremos resultados a cuadrar. Sin embargo, si aplicamos un REDONDEAR con dos decimales a nuestra fórmula el resultado varía:
=REDONDEAR($B$1*A2/100;2)

Cuadrar suma de sumandos redondeados en Excel.


Y es aquí cuando empieza nuestra tarea, ¿cómo conseguimos redondear nuestra fórmula que la suma acumulada de esos redondeos cuadre con la cantidad inicial a repartir?. Para ello construiremos una función que fuerce el cuadre por diferencias en el último elemento. Ejecutaremos la siguiente fórmula matricialmente (Ctrl+Mayusc+Enter) para cada elemento del reparto:
{=SI(SUMA(REDONDEAR($C$2:C2;2))<$B$1;REDONDEAR(B2;2);REDONDEAR(B2;2)-(SUMA(REDONDEAR($C$2:C2;2))-$B$1))}
Como podemos ver en la siguiente imagen:

Cuadrar suma de sumandos redondeados en Excel.


Vemos como en el último elemento, con la modificación incluida, el valor cambia para ajustar la suma acumulada al valor inicial a repartir.
La explicación de esta fórmula matricial es que mientras la suma acumulada por elementos y por fila sea inferior al valor inicial a repartir, redondearemos el importe repartido, pero en caso contrario ajustaremos por diferencias de la suma acumulada con el valor a repartir. Lo que conseguimos matricialmente con un condicional SI.

37 comentarios:

  1. Hola ExcelForo
    Sin duda un Tema interesante, donde habrá que dedicarle algunas horas para aprender a calcular los Códigos de Barra.
    Saludos
    Lázaro.

    ResponderEliminar
  2. Necesito ayuda por favor.

    En mi caso manejo hija de calculo para hacer actas de cantidades de obra y para que me cerraran en sumas iguales lo contratado con lo ejecutado utilicé la función "validar datos" y me busco la cifra hasta ajustar a lo que quería. El problema es que al revisarmelas en calculadora la multiplicación el resultado es distinto.....como hago por favor...ingerardiaz@yahoo.es

    ResponderEliminar
    Respuestas
    1. Hola!
      habría que ver ese fichero, y verificar que operación es la correcta (calculadora o Excel). Apuesto por Excel, ya que en las calculadoras, en ocasiones, modificamos la configuración y no realizan los cálculos en la base adecuada. Por supuesto habría que revisar los cálculos de la hoja de cálculo, y qué operación estamos haciendo.
      Si quieres envíame ese fichero a
      excelforo@gmail.com
      Slds

      Eliminar
  3. TENGO UN RESULTADO DE UN VALOR REDONDEADO POR EJEMPLO 10.5 EL REDONDEO SERIA 11 PERO A ESTE VALOR LO MULTIPLICO POR 2 ME DA UN RESULTADO 21 QUE HAGO PARA QUE ME SALGUE 22 AL MULTIPLICAR POR 2

    ResponderEliminar
    Respuestas
    1. Hola, que tal estás, espero te encuntres bien.
      Lo primero decirte que, por favor, evites escribir en mayúsculas... ya que hacerlo equivale a gritar.

      Respecto a tu cuestión, aunque tu hays formateado la celda para ver el número redondeado, el valor de la celda sigue siendo 10.5, y claro está 10.5x2=21.
      Si quieres operar con el valor redondeado sin decimales (10.5 sea 11), tendrás que aplicar la función REDONDEAR. Sería:
      =REDONDEAR(10,5;0)*2
      =11 x 2 = 22

      Saludos cordiales

      Eliminar
    2. Tenga Ud muy buenas tardes le agradezco bastante por la ayuda que me distes fue una buena idea preguntarte estoy bastante agradecido con Ud.
      Muchas gracias saludos

      Eliminar
  4. Tenga ud muy buenas tardes no se si me puede ayudar con esto tengo unas notas como por ejemplo,EP= 12, EF=07, ES=13, lo que quiero remplazar es ES por el promedio mas baja que es EF, con promedios menore me sale pero con mayores me remplaza a ambas columnas a EP y EF le agradeceria bastante su ayuda

    ResponderEliminar
    Respuestas
    1. Hola!
      necesitaría saber de donde salen esos valores 12, 7 y 13, si provienen de algún cálculo o son valores estáticos.
      Supongo que, en todo caso, te podría servir aplicar la función MIN sobre las celdas de EP y EF en la celda de ES...

      Espero te sirva la idea.
      Slds cordiales

      Eliminar
    2. Buenas tardes, tengo una duda, porque, al multiplicar 14*9.63 el resultado es 130.01 en excel.
      Y la misma cantidad la hago en una calculadora y el resultado es 134.82. Revisé que el formato de celdas estuviera en número y lo está, entonces no se que esté pasando, le agradezco su enseñanza.

      Eliminar
    3. Hola!,
      no sabría decirte.. en mi equipo (en Excel) realizo ese producto y me devuelve, lógicamente, 134.82
      Tendría que ver ese fichero para poder decirte algo.
      Si quieres envíamelo a
      excelforo@gmail.com
      y le echo un vistazo.
      Slds

      Eliminar
  5. Muy buenas tardes si los numeros estos numeros son estaticos gracias por su respuesta mi estimado

    ResponderEliminar
  6. mi ejemplo que estube haciendo es que si ES es mayor que uno de las celdas que es EP y EF que me replace al valor menor, si es EP=14, EF=8 y ES=15 que me remplace a EF que es 8 gracias por su respuesta

    ResponderEliminar
    Respuestas
    1. Hola,
      puedes probar con un condicional:
      =SI(Y(ES>EP;ES>EF);MIN(EP;EF);ES)

      Slds

      Eliminar
  7. gracias por su respuesta pero si yo tengo EP=14, EF=8 y ES=15 el promedio final deberia darme osea PF=14,5, osea que ES deberia sustituir a EF, me disculpas por las molestias le agradeceria mucho sus sabio respuesta gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      envíame en un fichero Excel el detalle completo de cómo y en qué forma se debe construir la fórmula.
      envíamelo a
      excelforo@gmail.com

      Slds

      Eliminar
  8. Muy buenos dias tenga Ud. le agradesco mucho por su ayuda que me dio en el formato.
    Gracias

    ResponderEliminar
    Respuestas
    1. Gracias a tí...
      la fórmula quedó así finalmente:
      =SI(ES="";PROMEDIO(EP;EF;Otro);PROMEDIO(K.ESIMO.MENOR(EP-EF;2);ES;Otro))
      Slds cordiales

      Eliminar
  9. Muy buenas tardes amigo, le agradesco otra vez mas por su apoyo que me dio con la formula.
    Gracias muchos saludos

    ResponderEliminar
  10. Al poner: SI(SUMA(REDONDEAR($C$2:C2,2))<$B$1,REDONDEAR(B2,2),REDONDEAR(B2,2)-(SUMA(REDONDEAR($C$2:C2,2))-$B$1))
    esa columna me sale con el mismo valor de la primera celda: 37.88. sabéis que estoy haciendo mal?

    ResponderEliminar
    Respuestas
    1. Hola, que tal, un gusto saludarte.
      Se me ocurre que no estés ejecutando matricialmente la fórmula, presiona para validad, en lugar de Enter, Ctr+Mayúsc+Enter

      Slds cordiales

      Eliminar
    2. Gracias por tu respuesta, mi error es que le daba Ctr+Shif+Enter. Debe ser la costumbre creo :D

      Eliminar
    3. Me alegre demasiado rápido, al dar Ctr+Mayúsc+Enter no se ejecuta matricialmente pues la formula de cada celda es distinta y no esta entre llaves

      Eliminar
    4. Quizá tengas que ejecutar cada celda matricialmente... ???
      No está claro que quieres conseguir.
      Slds

      Eliminar
    5. Trato de seguir el ejemplo del blog, haber lo que hago es:
      -selecciono el conjunto de celdas
      -ingreso la formula en una de ellas (en la 1ra o ultima seleccionada)
      -doy CTRL+SHIF+ENTER

      Eliminar
    6. Hola,
      como se indica en el ejemplo, y te comentaba en el comentario anterior:
      'Ejecutaremos la siguiente fórmula matricialmente (Ctrl+Mayusc+Enter) para cada elemento del reparto'
      Es decir, debes ejecutar celda a celda matricialmente

      Slds

      Eliminar
    7. Puedes poner una imagen o mencionar que formula ingresas en cada celda.

      Eliminar
    8. Hola, que tal!?
      Sólo sigue las indicaciones, copiando y pegando en cada celda la primera matricial.
      Slds cordiales

      Eliminar
  11. Hola, una consulta. Tengo un libro con muchos numeros no enteros, que al aplicar formato los transformo en entero pero siguen teniendo decimales. Mi problema es que trabajo con dinero, y al sumar en excel las celdas me da un numero no acertardo comparado con los numeros enteros mostrados en las mismas. Esto se debe a los decimales, pero mi pregunta es, como puedo cambiar el formato de todos esas celdas sin colocar Redondear una por una?, pq en cada celda hay formulas distintas, y tendria que modificar las formulas añadiendoles al principio la formula de Redondear y es muchisimo trabajo. Se tendria que crear una macro con una formula que aplique al libro?

    muchas gracias.

    ResponderEliminar
    Respuestas
    1. Hola marco,
      efectivamente los formatos aplciados a celdas no tienen efecto sobre los cálculos, por lo que como indicas tendrías que o bien ir añadiendo una a una la función REDONDEAR.
      La otra opción, cuando sean muchas las celdas es aplicar una macro; he probado algunas cosas y la que mejor funciona es la siguiente:
      Sub cambiar()
      Dim formula As String
      For Each celda In Range("B1:B10")
      celda.Replace What:="=", Replacement:="", SearchOrder:=xlByColumns, MatchCase:=True
      formula = "=round(" & celda.Text & ",2)"
      celda.formula = formula
      Next celda
      End Sub

      donde pone Range("B1:B10") debes insertar tu rango de celdas..

      Saludos cordiales

      Eliminar
  12. Buenas tardes, tengo una pega y me está volviendo loca. Tengo un resultado de sumas de celdas (B6:B39) y el resultado es 668,99 (en celda B40). Necesito redondear el resultado en la misma celda que tiene la fórmula de la suma (B40) a 669.
    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      simplemente en la celda B40 introduce:
      =REDONDEAR(SUMA(B6:B39);0)
      y listo.
      Saludos cordiales

      Eliminar
  13. Saludos.
    mi Comentario, sobre como resolver los decimales según el tema del Foro

    escribimos lo siguiente, en cada celda correspondiente:
    A2 = 89.95
    A3 = 2.89
    A4 = 2.89
    A5 = 1.16
    A6 = 0.57
    A7 = 2.54


    cree 3, pero no muy diferentes formas de llegar al Resultado

    Primera Forma:
    B1 = 42.11
    B2 = VALOR(DECIMAL($B$1*A2/100))
    B3 = VALOR(DECIMAL($B$1*A3/100))
    B4 = VALOR(DECIMAL($B$1*A4/100))
    B5 = VALOR(DECIMAL($B$1*A5/100))
    B6 = VALOR(DECIMAL($B$1*A6/100))
    B7 = VALOR(DECIMAL($B$1*A7/100))
    Segunda Forma:
    B1 = 42.11
    B2 = VALOR(DECIMAL($B$1*A2%))
    B3 = VALOR(DECIMAL($B$1*A3%))
    B4 = VALOR(DECIMAL($B$1*A4%))
    B5 = VALOR(DECIMAL($B$1*A5%))
    B6 = VALOR(DECIMAL($B$1*A6%))
    B7 = VALOR(DECIMAL($B$1*A7%))
    Tercera Forma:
    B1 = 42.11%
    B2 = VALOR(DECIMAL($B$1*A2))
    B3 = VALOR(DECIMAL($B$1*A3))
    B4 = VALOR(DECIMAL($B$1*A4))
    B5 = VALOR(DECIMAL($B$1*A5))
    B6 = VALOR(DECIMAL($B$1*A6))
    B7 = VALOR(DECIMAL($B$1*A7))


    y por ultimo colocamos
    B8 = suma(B2:B7)

    Espero que sea de Ayuda.
    Atte. Paul Ahmed L.M.

    ResponderEliminar
    Respuestas
    1. Hola Paul,
      gracias por el aporte... en esencia tu propuesta de emplear DECIMAL es interesante (una nueva visión), pero equivalente a emplear REDONDEAR, ya que al omitir el segundo argumento DECIMAL emplea dos decimales..
      pero esta forma sigue sin solucionar el problema de partida, y es que al sumar B2:B7, sumará 42,12 y no 42,11 que teníamos de partida.
      Sin embargo, empleando la matricial explicada, se consigue un redondeo forzado en el último dato, para que cuadre exactamente con el valor de partida...

      En todo caso, gracias por el aporte.

      Eliminar
  14. Estimado buenas tardes,

    Tengo una consulta (para variar), trabajo en una planilla con números enteros y son montos los cuáles debo hacer cuadrar con un determinado documento. Ejemplo, columna A hasta la fila 1000 con diferentes montos, y tengo un documento por X cantidad. Para ahorrarme el estar sumando celda + celda + 20 celdas más, mi pregunta es ¿existe algún código o truco que conozcas para que me arroje la suma del documento destacando las celdas que dan el resultado? De antemano, te doy las gracias.

    ResponderEliminar
    Respuestas
    1. Hola!
      se puede emplear Solver para esa conciliación de partidas.
      Puedes leer un par de entradas al efecto... aunque desde luego no es algo infalible:
      http://excelforo.blogspot.com.es/2011/10/conciliar-una-partida-con-solver.html
      http://excelforo.blogspot.com.es/2011/12/conciliar-en-excel-por-aproximacion-con.html

      Espero te sirva
      Slds

      Eliminar