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.

87 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
    4. Yo sí. Está multiplicando el redondeo de 13.5 por 9.63

      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
  15. hola buenas tardes, alguien que me diga como puedo restaurar mis formula en una tabla de excel ya que ponga las cifras que sea no se me aumenta la cifra total y si la arrastro me sale error...plis me urge

    ResponderEliminar
    Respuestas
    1. Hola Enriqueta
      no comprendo muy bien, pero si no te actualizan los datos de tus fórmulas prueba a cambiar el modo de cálculo a automático (o presiona tecla de función F9)...
      Espero te pueda ayudar.
      Saludos

      Eliminar
  16. hola estoy realizando una planilla donde debo sumar un rango dividirlo por x y ademas que el resultado con decimal menor a 5 me suba al siguiente entero ejemplo 95.3 quede en 96 y otro problema es que una división de dos celdas el resultado decimal no suba al siguiente entro ejemplo si el resultado es 3,9 que quede ahí y no 4 por favor ayúdeme soy nueva en esto. atte.
    andrea

    ResponderEliminar
    Respuestas
    1. Hola,
      si entiendo bien si la parte decimal es inferior a 0.5 suba y si es superior a 0.5 se quede como está, podría ser:
      =SI(COCIENTE-ENTERO(COCIENTE)<0,5;REDONDEAR.MAS(COCIENTE;0);COCIENTE)

      siendo COCIENTE la división entre los dos valores...
      Saludos

      Eliminar
  17. HOLA BUEN DIA, TENGO UN DETALLOTE ESTOY TRTANDO DE CUADRAR ESTAS CANTIDADES 11,800.00 X 23.74 Y MI RESULTADO TIENE QUE SER ESTE 280,114.91 PERO ME DA OTRA CANTIDAD ENTONCES TENGO QUE JUGAR ALOS NUMEROS CON ESTA CANTIDAD 23.74 Y SE HA COMPLICADO MUCHO. PODRAS AYUDARME??

    ResponderEliminar
    Respuestas
    1. Hola,
      11800 x 23,74 = 280132
      si esperabas otro resultado será por que el decimal 23,74 tiene un formato de número redondeado a dos decimales, pero realmente (mira tu barra de fórmulas) el valor sería:
      23,7385516949153
      Revisa qué datos tienes en ambos productos... y aplica/quita los formatos que tengas.

      Saludos

      Eliminar
  18. hola, el resultado de una suma de valores con 2 decimales máximo me da un resultado = 0.00000000000512 lo cual no tiene lógica alguna ya que todo está redondeado a 2 decimales. Tienen alguna idea por qué me puede estar pasando esto?

    ResponderEliminar
    Respuestas
    1. Hola Guillermo,
      el redondeo es de formato o estás aplicando alguna función de redondeo real (tipo REDONDEAR)???
      Slds

      Eliminar
    2. uso formula si y pego valores encima. Es muy raro el caso, no deberian aparecer valores más allá de 2 decimales con esta lógica, pero aparecen. Gracias

      Eliminar
    3. No sabría decirte... la única explicación es que de la fórmula hereda esos decimales
      ¿???

      Eliminar
  19. Hola Ismael, a ver si tu puedes ayudarme. Tengo una columna en éxcel que es:
    10 €
    59 €
    17,60 €
    10,50 €
    35,20 €
    30,24 €
    17,44 €
    -14,75 €

    Cuando realizo la autosuma seguidamente me dice: 165€, cuando el total correcto sería 165,23€.

    Por qué no aparecen los decimales?

    Un saludo!!

    ResponderEliminar
    Respuestas
    1. Hola Teresa,
      lo primero que deberíamos comprobar es si el formato de la celda está configurado sin decimales...
      probablemente el 'fallo' esté ahí, ponle un formato de moneda con dos decimales y seguro verás el dato como quieres.
      En todo caso aunque no lo veas como quieres el tratamiento posterior que haría Excel sería sobre el cálculo correcto (los 165,23)
      Saludos

      Eliminar
  20. TENGO UNA DUDA, NECESITO TENER EL TOTAL EXACTO DE ESTAS OPERACIONES:
    (20) ES MI UNIDAD, $6,910,003.35 ES MI TOTAL LO DIVIDO ENTRE MI UNIDAD Y ME DA $345,500.1675 ESTE SERIA MI PRECIO, PERO LO NECESITO A DOS CIFRAS, CUANDO LO REDONDEO ME QUEDA $345,500.17
    PERO SI LO MULTIPLICO POR MIS 20 YA NO ME DA EL RESULTADO ORIGINAL QUE ES $6,910,003.35
    COMO LE PUEDO HACER

    ResponderEliminar
    Respuestas
    1. Hola Freddy,
      realmente el problema es matemático; al redondear a dos decimales, el cociente resultante (345500,1675) estás perdiendo precisión... lo cual hace que al volver a hacer el producto el resultante no sea el mismo (lógicamente), ya que estás multiplicando dos números diferentes.
      La única solución es que el redondeo del cociente (345500,1675) sea solo visual, mediante un cambio de formato... así las operaciones posteriores se harán con el numero exacto...

      Saludos

      Eliminar
  21. Buenas Tardes
    Yo utilizo para hacer presupuestos la sig. formula:
    =TRUNCAR(REDONDEAR(E8*F8,2),2)
    Donde E8 es la Cantidad y F8 el Precio, el Resultado de la Fórmula me da un Importe. Peeeero, en ocasiones me falla por 1 centavo, por ejemplo:
    E8: 71.75 F8: $330.90 IMPORTE: $23,742.08

    El resultado tendría que darme $23,742.07

    Como puedo utilizar mi fórmula, sin necesidad de Macros??

    ResponderEliminar
    Respuestas
    1. Hola Alejandro,
      TRUNCAR 'corta' sin redondear a los decimales que le indiques, y REDONDEAR sigue las reglas normales de redondeo... esto es, valores por encima de 5 redondea +1 la unidad siguiente.
      En tu ejemplo 71.75x330.90 = 23,742.075
      al ordenarle el redondeo queda 23,742.08, y al TRUCAR la cantidad te devuelve los mismo...lo que sería correcto.
      Si quieres quedarte con el valor 23,742.07 simplemente aplica TRUNCAR
      Saludos

      Eliminar
    2. Gracias por su respuesta, pero me gustaría un poco mas de ayuda.
      Creo que la solucion tendría que ser utilizando la fórmula SI.
      Solo que, como le haría para que si la 3er cifra despues del punto decimal fuera 5, se redondee hacia abajo (Por ejemplo 742.075=742.07) y si fuera superior a 5 se redondee hacia arriba (Ej,: 742.076=742.08).
      Esto utilizando una sola fórmula.
      Por mi cuenta voy a intentarle, si lo logro solucionar, se los comparto.

      Eliminar
    3. Hola:
      Es un problema que siempre me ha inquietado y, aunque no entiendo mucho, yo utilizo la misma fórmula que Alejandro pero al revés. Es decir, primero "trunco" cada cifra a tres decimales (así evito descuadres con las diezmilésimas) luego redondeo el valor a dos cifras.
      Este sistema es el que está reconocido como sistema de redondeo oficial del Euro por la UE. Espero que funcione siempre porque sólo lo he comprobado con una lista de números decimales concreta.

      La función sería...

      =REDONDEAR((TRUNCAR(A1;3));2)

      Siendo A1 la celda donde está el valor a cuadrar.
      Saludos.
      :-)


      Eliminar
    4. Hola Ferrán,
      lo que ocurre es que no es un redondeo al uso...
      pero gracias por el aporte
      Slds

      Eliminar
    5. Inclui la Solucion de FERRAN en el Archivo de Excel pero no siempre funciona.
      Hasta ahorita, solo las columnas de los Totales (4) y (7) coinciden con el Rsultao Esperado. - Ver el Archivo del Link. SALUDOS!
      http://www.4shared.com/file/X9DwIgekce/SOLUCION_al_Redondeo.html?

      Eliminar
  22. Ya está, Todo era cuestión de darle unas 2 horas, aunque la Formula es kilométrica:
    =SI((10*RESIDUO((100*RESIDUO(REDONDEAR(TRUNCAR(E5*F5,4),3),ENTERO(REDONDEAR(TRUNCAR(E5*F5,4),3)))),ENTERO(100*RESIDUO(REDONDEAR(TRUNCAR(E5*F5,4),3),ENTERO(REDONDEAR(TRUNCAR(E5*F5,4),3))))))>5.01,REDONDEAR.MAS(E5*F5,2),REDONDEAR.MENOS(E5*F5,2))

    Ojalá a alguien mas le sirva.

    [URL=http://www.4shared.com/file/bKuqIL76ce/SOLUCION_al_Redondeo.html][IMG]http://dc775.4shared.com/img/bKuqIL76ce/153ec869bb0/SOLUCION_al_Redondeo[/IMG][/URL]

    ResponderEliminar
    Respuestas
    1. Añado Link:
      http://www.4shared.com/file/bKuqIL76ce/SOLUCION_al_Redondeo.html

      Eliminar
    2. Otra opción algo más sencilla (creo funciona en todos los casos):
      =SI(VALOR(DERECHA(ENTERO(1000*(A1-ENTERO(A1)));1))<=5;TRUNCAR(A1;2);REDONDEAR(A1;2))
      Saludos

      Eliminar
    3. Gracias, solo le hice estos cambios:

      =SI(VALOR(DERECHA(ENTERO(1000*(REDONDEAR((E5*F5),3)-ENTERO(E5*F5))),1))<=5,TRUNCAR((E5*F5),2),REDONDEAR((E5*F5),2))

      Nota: A1 = E5*f5

      Eliminar
  23. como puedo utilizar la funcion dias360 y la funcion redondear juntas?

    ResponderEliminar
    Respuestas
    1. Hola Daniela,
      un placer saludarte igualmente.
      no parece tenga sentido emplear ambas funciones juntas, ya que DIAS360 devuelve un número entero como diferencia entre dos fechas, tomando el año comercial y meses de 30 días.. por lo que aplicar una función de REDONDEO no tendría sentido a priori, ya que no hay nada que redondear (en cuanto a decimales hablamos)
      Un cordial saludo

      Eliminar
    2. lo que sucede es que en un trabajo de la univeridad en una pregunta me piden calcular el numero de años que han transcurrido desde la fecha de ingreso de un vehiculo a un parqueadero hasta la fecha y luego redondear con cero decimales

      Eliminar
    3. Hola de nuevo Daniela,
      esta entrada te servirá para calcular tiempos entre dos fechas
      http://excelforo.blogspot.com.es/2010/05/calcular-la-edad-con-excel.html
      Un saludo

      Eliminar
  24. Hola buena tarde,
    Sucede que aplique una formula para sumar valores en casillas que tuvieran el mismo color, le di una función que por ejemplo dentro de una columna todos los valores que estuvieran marcados de color verde se sumaran, la verdad es que la formula lo hace, pero no esta sumando los decimales por ejemplo si la suma real es de 25.40 solo marca 25.00, como podría corregir esto?, Gracias por tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola,
      supongo tendrás una función VBA personalizada, algo parecido a esta:
      http://excelforo.blogspot.com.es/2015/12/vba-una-suma-acumulado-con-doble.html
      verás que es importante la definición del tipo de datos... seguramente tu tendrás definida la variable de acumulación como Long, prueba a cambiarlo como Double

      Slds

      Eliminar
  25. Este comentario ha sido eliminado por el autor.

    ResponderEliminar
  26. Hola, buenos días! A ver si me podría ayudar con una cosa... voy a intentar explicarme lo mejor posible ya que no soy experta en excel... Yo lo que quiero hacer es que si en una celda (Ej. AU3), el resultado de una fórmula da un numero con decimales, quiero que en esa celda aparezca un número entero, pero luego al pasar el valor de esa celda a la siuiente hoja me respete el numero real con decimales... no se si me explico... Pongo un ejemplo a ver si se entiende.
    En la Hoja 1 tengo en AU3 la fórmula =(AQ3+S3)-AT3, si el resultado es 0,67, lo que quiero es que a mi me muestre 0.
    Despues en la Hoja 2 quiero que AU3 me coja el resultado real, es decir, 0,67 pero me siga mostrando 0 tb en esa hoja hasta que la suma de 1 y entonces cambie el valor mostrado a 1. No se si me he explicado bien... porque no me entiendo ni yo xD. Espero me pueda ayudar. Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      si el valor de esas celdas es un número entre 0 y 1, podrías aplicar un formato (que no cambiará el valor de tu celda!!!), solo el aspecto.
      El formato personalizado podría ser:
      [<1]"0";0
      Saludos

      Eliminar
    2. Muchisimas gracias por la respuesta, la verdad es que es exactamente eso lo que queria... jajaja pues si que era facil y yo aqui rompiendome la cabeza con fórmulas raras... Lo único que no solo habrá valores menores que 1... pero trasteando he conseguido hacerlo con formatos condicionales, metiendo 1 personalizado para si es <1, otro si es >1 y <2, etc. como nunca serán valores demasiado altos... con meter por ejemplo hasta 10 creo que será suficiente, aunque quiza haya una forma más sencilla de hacerlo. Si se le ocurre alguna forma más sencilla para meter varios rangos de numeros, le agradecería la información, si no creo que así me puedo apañar con paciencia y tiempo jajaja. Muchísimas gracias de nuevo, me acaba de alegrar el día xD. Un saludo

      Eliminar
    3. ;-)
      por desgracia la manera que comentas sería la más simple (útil cuando son pocos casos).
      UN cordial saludo!

      Eliminar
  27. Hola buenos dias, (Corrigo las celdas)
    Tengo las siguientes cifras:
    B14 -2.588.879.876,35
    B15 519.226.834.574,92
    B16 517.812.118.039,53
    B17 -1.174.163.340,96

    Realizo las siguientes formulas para validar las cifras y me tocó ampliar el formato a 10 decimales para encontrar las diferencias que se genera en decimales:
    CELDA B18:
    =B14+B15-B16
    El resultado es:
    B18 -1.174.163.340,9600200000 (con 10 decimales)

    CELDA B19:
    =B17-B18
    El resultado es:
    0,0000219345 (con 10 decimales)

    Porque se genera esos decimales (0,0000219345), si las cifras todas han sido digitadas con 2 decimales. y si lo amplio a 10 digitos decimales la cifras digitas me muestra que todos los digitos decimales despues del segundo digito son cero.
    Esos digitos decimales me generan problemas al subir la información a una Web del Gobierno, pues me dice que hay diferencias en la formula.

    Gracias.

    ResponderEliminar
  28. Tengo una sumatoria de ( 2.1+2.1+2.1+2.1+2.1+2.1) y me de 10.42...? xq no me sale 10.5 q es la sumatoria total

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?
      un placer saludarte igualmente..
      quizá sea un error de transcripción, pero la suma daría en todo caso 12,6
      En cualquier caso, revisa cómo tienes escrito, de cierto, los distintos sumandos
      UN saludo

      Eliminar
  29. Buenas tardes, Sr Excelforo Ismael Romero

    Llevo varias semanas tratando de resolver el problema de redondeo, y buscando he encontrado en la red, he encontrado este foro. Después de probar todas las soluciones aportadas en este foro sobre las diferentes soluciones que se han aportado en este foro sobre el redondeo, debo decir que a mi no me funcionan, ya no se que hacer más para resolverlo.
    A modo de resumen he realizado una hoja donde integro todas las soluciones indicadas y he comprobado que todas me fallan el función del segundo decimal de la cantidad a repartir, es decir, si este decimal es 5 el valor es exacto, mientras que si es 9 falta 1 céntimo, en otros valores la diferencia es mayor.
    De término medio me faltan 2 céntimos. La verdad es que tengo que repartir un montante de costos de obra bastante grande y detallado entre mis 64 vecinos y actualmente tengo un descuadre de 126 euros de más, por lo que no puedo presentarles sus costos.

    El modelo a repartir es el siguiente:
    %VIV 80,496 429,4294126 (=34567,35/80,496)
    %PART 1,675 719,2942662 (=34567,35/1,675)
    % PART 1,679 721,0119838 (=34567,35/1,679)
    NUM VIV A 8 5754,354129 (=5754,354129*8)
    NUM VIV B 8 5768,095871 (=5768,095871*8)
    11522,45 (=5754,354129+5768,095871)
    Total a repartir 34567,35

    He subido el Excel a https://www.4shared.com/office/s0HbKpKYfi/redondeo1B.html
    Con objeto de que pueda verlo y ayudarme.
    Gracias de antemano y un saludo

    ResponderEliminar
    Respuestas
    1. Hola,
      no comprendo esas operaciones
      si quieres mándame el fichero a: excelforo@gmail.com
      Slds

      Eliminar
    2. Este comentario ha sido eliminado por el autor.

      Eliminar
  30. OK. Gracias, Te lo envío desde otra cuenta de gmail.
    Saludos
    Fco Antunez

    ResponderEliminar
  31. buenas tardes excelforo!
    tengo que realizar un calculo, donde la columna a utilizar tiene una formula determinada por redondeo (REDOND.MUL 0,5), pero en la columna que debo aplicar dicho numero obtenido, no toma el numero redondeado sino el numero con decimales. Les explico en un ejemplo
    numero con decimales 666.12, aplicada la formula de redondeo me queda 666
    en la siguiente formula debo utilizar 666 para sacar un %, ejemplo 20%, su resultado debería ser 13.20, pero me queda 13.22 porque en realidad me esta tomando el numero sin redondear , es decir 666.12
    Que debería hacer para solucionarlo?.
    Aclaro que en este caso del % no necesito redondear sino que me de exacto los 13.20 del ejemplo.

    Agradezco si hay una solución

    ResponderEliminar
    Respuestas
    1. Hola!
      vaya, qué raro ¿?,por que replicando tu ejemplo sale como resultado los 13,20 que comentas
      ¿Te has asegurado que aplicas el 20% sobre el resultado de =REDOND.MULT('Celda_con 666,12';0,5)??
      Por descartar...
      Saludos

      Eliminar
  32. Es una pena que como tantas otras veces un trabajo tan magnífico se pierda para muchos o me tenga como a mi dos horas con el mismo ejemplo exacto probando durante varias horas antes de dar con la tecla, por no representarse como los principiantes vemos, o mejor diría no vemos las cosas.

    Para que funcione hay que proceder asi:

    En la casilla D2 hay que poner la fórmula que ha puesto el autor de la misma.

    Paso a) Copiar pegar en la barra de formulas de arriba. Al hacerlo observaremos que desaparecen los dos corchetes raros el del principio y el del final.

    Paso b) Apretar a la vez las teclas Ctrl, mayusculas y enter (o intro)con lo cual la formula se guarda en la casilla D4, pero como matricial (o sea que va a tener en cuenta un grupo de casillas (el que va de C2 a C7 creo) para hacer los cálculos.

    Ahora mismo ya esta guardada como matricial. Si entramos y modificamos cualquier cosa se fastidia lo matricial. Hay que cortar ese texto, volverlo a pegar en la casilla y volver a darle a las tres teclas Ctrl+mayusculas+ enter.

    Ahora la segunda parte

    No vale arrastrar hacia abajo la formula y rellenar toda la columna D, como bien dice el autor hay que ir modificando cada formula de la columna D.

    Y se hace asi

    Casilla D3

    =SI(SUMA(REDONDEAR($C$2:C3;2))<$B$1;REDONDEAR(B3;2);REDONDEAR(B3;2)-(SUMA(REDONDEAR($C$2:C3;2))-$B$1))

    y las tres teclas, para que entre como matricial y no como afectante a solo esa celda

    Casilla D4

    =SI(SUMA(REDONDEAR($C$2:C4;2))<$B$1;REDONDEAR(B4;2);REDONDEAR(B4;2)-(SUMA(REDONDEAR($C$2:C4;2))-$B$1))

    Al terminar lo mejor es ponerse en la casilla D1 e ir bajando con las flechitas casilla por casilla para ver si por despiste no se guardo alguna como matricial.

    Comprendo que estas explicaciones no quedan elegantes, pero el poder VER las casillas siguientes es muy importante. Espero sirvan y si se pudiera añadir al menos la casilla D3 a la entrada inicial creo que seria muy util a los usuario, pues encontrar esta pagina con suerte lleva una hora y tener la miel en los labios y no poder tomarla es un dolor. Gracias de corazon. Carlos Valiña. SAntander España.

    ResponderEliminar
    Respuestas
    1. Gracias por tu comentario y aporte Carlos,
      obviamente se puede redundar en el proceso celda a celda, pero estimé que no era necesario, ya que entiendo todos sabemos copiar y pegar...

      En todo caso, muchas gracias y lo tendré presente
      Saludos cordiales

      Eliminar

Nota: solo los miembros de este blog pueden publicar comentarios.