La función RESIDUO de Excel con números negativos.

Hemos visto diversos usos de la función RESIDUO en varias notas de este blog. Por ejemplo, para superar el problema de diferencias negativas en cálculos de horas o para generar fondos para facilitar la lectura de tablas.

En esta nota veremos algunos aspectos del uso de esta función. Empecemos por su definición. Según la ayuda en línea de Excel:

RESIDUO proporciona el residuo después de dividir un número por un divisor

Es decir RESIDUO(3,2) = 1

Si investigamos un poco más la ayuda de Excel vemos que esta función equivale a la expresión

n - d*ENTERO(n/d)

donde "n" es el número a dividir y "d" es el divisor

Veamos cuál será el residuo de dividir 30 por 4, considerando que ambos números pueden ser positivos o negativos:





Vemos que en todos los casos el resultado es el mismo, excepto por el signo que es el del divisor.

Pero si calculamos el residuo de 34 dividido por 6 vemos estos resultados



Estos resultados se deben a que el residuo se define como la diferencia entre el múltiplo entero más grande del divisor que no supera al dividendo. En el caso que estamos considerando, 30 es el múltiplo más grande (6 * 5) que no supera al dividendo (34) y la diferencia entre ellos es 4.
Pero cuando consideramos un dividendo negativo, como -34, el mayor múltiplo de 6 que no supera a -34 es -36 (6 * -6) y la diferencia es obviamente 2.

En Visual Basic existe el operador MOD cuya función es la misma que la de la función RESIDUO. Sin embargo el operador no siempre da resultados idénticos a los de la función RESIDUO.
MOD es usado como operador, es decir, para encontrar el residuo de 34 dividido por 6 escribimos en un módulo de Vba

34 Mod 6

Podemos usar la ventana Inmediate del editor de Vba para ver los resultados



Como pueden observar los resultados son inconsistentes. Para hacerlo más evidente podemos crear una función UDF (definida por el usuario) y usarla luego en la hoja para compara los resultados

Function residuo_vba(n As Double, d As Double)
residuo_vba = n Mod d
End Function


En esta tabla se puede ver que los resultados de las dos funciones son inconsistentes



Sin entrar a considerar cuál de los resultados es el correcto, tema que dejo librado a los matemáticos, podemos usar esta fórmula para obtener con funciones los mismos resultados como con el operador MOD de VBa

número - divisor *COCIENTE(número, divisor)

como podemos ver en esta tabla



Para usar la función COCIENTE (QUOTIENT en inglés) hay que tener instalado el complemento Analysis ToolPak.

Technorati Tags: MS Excel

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: MS Excel

Algo más sobre la función REDONDEAR (ROUND) de Excel.

La función REDONDEAR, una de las tantas funciones de redondeo que tiene Excel, tiene una curiosidad sobre la cual me llamaba la atención un lector.
Esta función redondea en forma simétrica a la cantidad de decimales deseada. Por ejemplo,

la fórmula =REDONDEAR(10,43;1) da 10.4

la fórmula =REDONDEAR(10,46;1) da 10.5

Si ponemos 0 como número de decimales, el redondeo es obviamente al número entero más cercano.

La curiosidad es que REDONDEAR también acepta números negativos como parámetro para el número de decimales. En ese caso, Excel redondea "hacia la izquierda" a la decena (-1), centena (-2), etc., más cercana.

Por ejemplo:

=REDONDEAR(123,45;-1) da 120

=REDONDEAR(123,45;-2) da 100

Y finalmente =REDONDEAR(123,45;-3) da 0

Si quisiéramos redondear el número 12345, los resultados serían

=REDONDEAR(12345;-1) = 12350

=REDONDEAR(12345;-2) = 12300

=REDONDEAR(12345;-3) = 12000

=REDONDEAR(12345;-4) = 10000

=REDONDEAR(12345;-5) = 0

Technorati Tags: MS Excel