viernes, noviembre 14, 2008

Un poco más sobre redondeos en Excel

Un lector dejó un comentario interesante en la nota Algo más sobre la función REDONDEAR tocando el tema de redondeo de sumas en las tiendas y supermercados. Decidí no publicar la nota ya que no estaba relacionada directamente con algún aspecto técnico de la nota y además el lector decidió quedar en el anonimato.

Nuestro anónimo lector comentaba sobre una cadena de supermercados que proponía a los clientes redondear los centavos y donar la diferencia a una institución de bien público. Por ejemplo, el monto de la compra era 99 pesos con 98 centavos, el cajero proponía al cliente redondear la suma a 100 pesos donando dos centavos. Esto parece loable, sólo que la donación era hecha a nombre de la empresa y no del cliente quien es el donante real. Otro aspecto interesante era que la cadena de supermercados podía descontar las donaciones de los impuestos generando de esta manera ganancias a cuenta del dinero de los clientes.

¿Cuál es el método de redondeo en Excel? Pues bien, hay varios y además existen diferencias entre el redondeo en Excel y en Vba, el lenguaje macro de Excel.
En la nota anterior mostrábamos que podíamos usar un redondeo negativo como

REDONDEAR(123,45;-2) = 100

El equivalente en Vba sería usar la función Round, pero esto genera un error




Sí podemos usar la función de Excel en Vba usando la propiedad WorksheetFunction de esta manera



El método común de redondeo (o simétrico o Round-Half-Up, de acuerdo a Wikipedia) que funciona de la siguiente manera:

1 - decidimos cuantos decimales queremos dejar;
2 - si el digito siguiente al que dejamos es mayor o igual a 5, redondeamos "hacia arriba" (por ejemplo, 123.45 redondeado a un decimal será 123.5);
3 - si el dígito siguiente es menor de 5 redondeamos "hacia abajo" (123.44 será 123.4).

Este es el método que usa Excel con la función redondear. La función TRUNCAR, en cambio, se limita a "cercenar" los dígitos no requeridos.

Existe un método de redondeo llamada, en inglés, Banker's rounding (o round to even, unbiased rounding, convergent rounding, statistician's rounding, Dutch rounding, Gaussian rounding, siempre de acuerdo a Wikipedia) y me disculparan el inglés pero no he encontrado nada sobre el tema en castellano.


Este método consiste en redondear siempre hacia el número par más cercano. Por ejemplo, 1.5 y 2.5 se redondean como enteros a 2; 3.5 y 4.5 se redondean a 4. La lógica de este método es minimizar el desvío que se produce al redondear siempre 0.5 "hacia arriba".
En este ejemplo



he usado la fórmula =REDONDEAR(A1,0) en la columna B. En las celdas A5 y B5 he usado la función SUMA. Como pueden ver el resultado es problemático. No existe ninguna función nativa de Excel que funcione con el método de Banker's rounding.


Pero en Vba podemos usar estas funciones para este tipo de redondeo: CByte(), CInt(), CLng(), CCur() y Round().
Por ejemplo, podemos escribir esta funcion UDF (definida por el usuario) para redondear a enteros

Function bankers_round(monto As Double)
bankers_round = CInt(monto)
End Function


Si aplicamos esta función para redondear a enteros, obtenemos estos resultados




Por supuesto, también este método puede generar desvíos. Podríamos pensar en un método de redondeo aleatorio, pero este método podría dar resultados diferentes para un mismo grupo de datos. Es decir, puede ser inconsistente.

Quien esté interesado en profundizar en el tema puede ver esta nota en la base de conocimientos de Microsoft..


Technorati Tags:

12 comentarios:

  1. Nestor (Venezuela)
    Hola, no pensado en la funcion "REDONDEAR.MENOS" o "REDONDEAR.MAS"

    ResponderBorrar
  2. Ambas funciones redondean en forma simétrica, como REDONDEAR, de manera que también con ellas se producen desvíos. En el ejemplo e la nota si usas REDONDEAR.MAS la suma de los valores será 14, y con REDONDEAR.MENOS, 10

    ResponderBorrar
  3. hola. mi comentario no tiene que ver con el tema. Es más bien una pregunta.

    Quiero ver el estado de avance de unas construcciones menores, en comparacion con el presupuesto. poder verlas desde cualquier computador. y restringir la informacion.

    la pregunta es: ¿puedo ingresar un par de columnas a un blog? sólo necesito que la columna de Avcance sea variable. y no necesito todas las funciones excel, solo poder cambiar las cifras segun el avance.

    te agredeceria la ayuda con esto, al menos para decirme que no se puede . ya que no tengo el tiempo ni dinero como para hacer una pagina web para esta necesidad.

    ResponderBorrar
  4. Mi recomendación es que uses alguna de la hojas de cálculo en línea como EditGrid o Google Spreadsheets

    ResponderBorrar
  5. Gracias por ahondar en el tema de redondeo. Mi nombre es Robert Blanco, quien habia consultado por correo, involuntariamente comente como anónimo. Algo mas sobre los supermercados o tiendas que "donan"... ellos no siempre donan el dinero... se compran ellos mismos las mercaderías, o compran las mercaderías de sus empresas satélites.. Moraleja: "Siempre lleven plata justa para el super"

    ResponderBorrar
  6. Hola mi estimado Jorge
    Se puede utilizar la misma funcion de redondear, aplicando un pequeño ajuste a la misma formula.
    con: =REDONDEAR(C5-0.01,0)

    asi: 2.49 = 2, 2.50 = 2 y 2.51 = 3

    Haciendo esto podemos ir ajustando con -0.01 o 0.001 o lo que se requiera o sumandolo inclusive.

    Felicidades esta excelente tu blog

    ResponderBorrar
  7. Ej. redondeo a 2 decimales
    =(entero((origen * 100)+0,5))/100)

    ResponderBorrar
  8. Hola, soy profe de primaria, estoy haciendo un concentrado de calificaciones y deseo aplicar un redondeo automátco a algunas casillas tomando en cuenta estos dos criterios:
    el primer criterio nos dice que si el alumno obtiene una calificación que se encuentre entre 5.1 y 5.9 se redondee a 5.
    En el caso de números de 6 a 9 se emplea otro criterio de redondeo, si por ejemplo la calificación es 6.4 se redondea a 6 y si la calificación es 6.5 ésta se redondea a 7, deseo hacer algo para que exel realice el redondeo de manera automática respetando ambos criterios en números de 5 a 9.

    ResponderBorrar
  9. ¿Cuál sería el criterio para las calificaciones entre 0 y 4.9?
    Suponiendo que entre 0 y 4.9 no hay criterio de redondeo y la calidicación se encuentra en la celda A2, podrías usar esta fórmula:

    =SI(Y(A2>=5,A2<=5.9),TRUNCAR(A2),SI(A2>5.9,REDONDEAR(A2,0),A2))

    ResponderBorrar
  10. Muchas gracias Humberto, con tu formula resolvi mi problema

    ResponderBorrar
  11. Hola jorge.

    Es posible crear una función personalizada para presentar máximo dos cifras significativas; por ejemplo cuando se tengan los siguientes valores:

    0,000256466 que excel devuelva 0,00026
    0,00356 que excel devuelva 0,0036
    0,00356 que excel devuelva 0,0036
    1,96356 que excel devuelva 2,0
    1,45 que excel devuelva 1,5
    0,1234 que excel devuelva 0,12
    9,123 que excel devuelva 9,1
    61,23 que excel devuelva 61

    Gracias por su ayuda

    ResponderBorrar
  12. La respuesta es que se puede, pero no es trivial. Si haces una búsqueda en la Internet podrás encontrar varias soluciones con Excel al tema de las cifras significativas.
    Tal vez más adelante, escriba algo sobre el tema.

    ResponderBorrar

Nota: sólo los miembros de este blog pueden publicar comentarios.