Función RESIDUO

[Fleming]

Estimados compañeros

Aquí van unas sugerencias por si a alguno de vosotros les puedo ahorrar los cabreos que me ha ocasionado la función RESIDUO en más de una ocasión.

Esta función no cumple siempre con sus obligaciones, como puede verse en el archivo que acompaño, por lo que en ocasiones es preciso sustituirla por otra fórmula.

En el archivo se detalla.

Un cordial saludo

RESIDUO.zip

[verzulsan]

Muy buena la explicacion Fleming como siempre y muy compacta y funcional la solucion

Viendo el archivo solo queria añadir que la funcion residuo segun veo con tus ejemplos esta limitada a (2^27)=134217728 repeticiones, pasado este numero hace exit y devuelve error. Sobre tu solucion me ha traido recuerdos de cuando estaba en primero de carrera, en analisis matematico asi es como calculabamos el residuo de un numero durante los examenes, lo llamaban (modulo X de Y) , estaba entretenido pero odiabamos a la profe xD.

Un saludo!

EDIT: Se me acaba de ocurrir que para usar la funcion residuo con numeros mas elevados valdria esto:

=RESIDUO(RESIDUO(B15;D15^2);D15)[/CODE]

Hace un residuo del residuo en base al cuadrado del numero, al multiplicarse por si mismo el dividendo seguira respetando el intervalo del modulo y luego este numero mas chico se le aplica otro residuo.

[Antoni]

Hola a todos:

Lamento contradecirte Verzulsan, pero tal como indica Fleming, el valor máximo es de 268.435.455, que corresponde a 2^29 y no a 2^27.

Con la función Mod de VBA, conseguimos llegar a 2.147.483.647, que corresponde a

2^32, valor máximo binario de 4 bytes correspondiente al tipo de datos Long.

La utilización del tipo de datos Double, no altera el resultado.

Salu2. Antoni.

[verzulsan]

No pasa nada Antoni, me da que cada dia me explico peor por que no me entendiste para nada xD, a ver si con ejemplos se me entiende mejor.

Dividendo: 268.435.455, Divisor: 2, modulo seria la cantidad de paquetes enteros (modulos) del divisor 2 sobre el numero a modular, y lo que no puede convertirse en paquetes por que esta incompleto eso seria el resto. La funcion esta solo acepta 2^27 paquetes, sea cual sea el numero, por lo que el maximo numero que funcionaria estaria condicionado por la formula:

NumeroMaximo = Divisor * (2^27). Ya constatandolo con los datos de Fleming para completar la explicacion:

Ocurre que si el dividendo es mayor que 268.435.455 y el divisor es 2, la función no responde y arroja #¡NUM!

(2 * (2^27)) -1 = 268.435.455

Otro tanto ocurre cuando el dividendo es mayor que 402.653.183 y el divisor es 3

(3 * (2^27)) -1 = 402.653.183

Y otro tanto cuando el dividendo es mayor que 536.870.911 y el divisor es 4

(4 * (2^27)) -1 = 536.870.911

Y lo mismo cuando el dividendo es mayor que 3.623.878.655 y el divisor es 27

(27 * (2^27)) -1 = 3.623.878.655

tal como indica Fleming, el valor máximo es de 268.435.455

Donde dice esto? No vi esa cifra ni por este post ni por el archivo.

Un saludo

[Fleming]

Hola Verzulsan y Antoni

Bueno, en realidad el primer nº para el que no funciona RESIDUO cuando el divisor es 2, es el número 2^28 = 268.435.456 = 268.435.455 + 1

Como soy un poco antojadizo, se me antoja que quizás tenga algo que ver con el número de filas de la hoja, ya que 2^28 = 2^16 * 2^12 y resulta que 2^16 = 65536 = número de filas de la hoja (en mi Excel 2003), y además 2^12 = 4096 = 268.435.456 dividido por 65536 que es el nº de filas. ¿Casualidad?

Por cierto, ¿os habéis fijado si en Excel 2007 ocurre el citado fallo de RESIDUO ? Pudiera ser que al tener muchas más filas no ocurriera el fallo. Bueno, esto si lo del nº de filas no es pura lucubración mía.

Tu fórmula Verzulsan en principio creí que funcionaba, pero cuando probé con números más altos, por ejemplo de 12 ó 13 dígitos, ya no cumplía.

En fin, la que parece que puede con todo es la que propongo en el archivo =D-d*(ENTERO(D/d))

Muchas gracias compañeros por alegrar el tema.

Saludos

[Antoni]

Hola Verzulsan:

Me da que hablamos diferentes idiomas, a ver, tu dijiste:

Viendo el archivo solo queria añadir que la funcion residuo segun veo con tus ejemplos esta limitada a (2^27)=134217728 repeticiones,

Yo lo único que he pretendido decir es:

2^27 no es igual a 134.217.728, es igual a 67.108.864.

268.435.455 es el valor máximo con módulo 2 y que corresponde a (2^29) - 1, y esto además, respondiendo a tu pregunta, lo especifica Fleming en su aporte en la fila 11.

Ocurre que si el dividendo es mayor que 268.435.455 y el divisor es 2, la función no responde y arroja #¡NUM!

Espero que después de esto sigamos siendo todos amigos,, ja,ja

Saludo cordiales.

Antoni

[verzulsan]

Como soy un poco antojadizo, se me antoja que quizás tenga algo que ver con el número de filas de la hoja, ya que 2^28 = 2^16 * 2^12 y resulta que 2^16 = 65536 = número de filas de la hoja (en mi Excel 2003), y además 2^12 = 4096 = 268.435.456 dividido por el nº de filas.

Por cierto, ¿os habéis fijado si en Excel 2007 ocurre el citado fallo de RESIDUO ?

Si que ocurre en 2007, lo probe tambien, esto es mas como se comportaria una UDF, el fallo esta en la programacion como dije antes, desde ahi si podria arreglarse. Esta confirmado ya que para profesor no sirvo xD.

Espero que después de esto sigamos siendo todos amigos,, ja,ja

Yo espero que si xD, vamos que no me molesta que me corrijan cuando me equivoco pero en este caso me da que nadie se ha equivocado y por lo menos a mi me da que no se me ha entendido jeje, no pasa nada, yo si me entendi , estoy convencidisimo 100% de lo que pongo arriba es correcto, sin animo de ofender ni parecer pedante .

Ya entendi lo tuyo Antoni, pero estamos hablando de cosas diferentes.

Un saludo a los dos.

[Antoni]

Hola:

Rectifico todo lo que he dicho hasta el momento.

¡¡ Me he equivocado al calcular las potencias de 2 !!

A todas ellas hay que restarle 1 al exponente, por lo que cualquier afirmación mía efectuada en base a ello es totalmente falsa, entono un "mea culpa" y pido disculpas por ello, no se puede ser tan impulsivo.

Y, en cuanto a los valores máximos, yo creo que va mas ligado a la longitud de los campos intermedios que utiliza Excel para esta función, podrían ser 4 bytes 2^32. Lo que no me queda claro, es porqué aumenta el máximo del dividendo a medida que aumenta el divisor, cuando la lógica me dice que debería ser al revés.

En Excel 2003, para el nº de filas debe usar 2 bytes 2^16 y para el nº de columnas 1 byte 2^8. Todo ello es una opinión, y por ello, mas que discutible.

Saludos. Antoni.

[Fleming]

Vamos a ver! "¡Compañeros del alma!....¡compañeros! (que decía M. Hernández)

Con vuestras cifras ya empezaba yo a desconfiar de mi Hoja de cálculo, así que he tenido que "tirar" de calculadora científica (que es pequeña pero matona) para "desfacer el entuerto" y me confirma lo siguiente:

2^27=134.217.728 es correcto. 2^28=268.435.456 es correcto, y 2^29=536.870.912 es correcto

Antoni, mira que me parece que tu calculadora anda mal de pilas. ¿Has hecho los cálculos en Excel o con una calculadora?

También en el post nº 3 dices que 2.147.483.647 equivale a 2^32, cuando en realidad 2^32 es igual a

4.294.967.296

Por cierto, con la fórmula nueva he metido 2^47=140.737.488.355.328

Veo que sois unos conversadores infatigables que amenizáis enseguida los temas.

Gracias a los dos. Ha sido un placer

Un cordial saudo

[Fleming]

Vaya Antoni, qué rápido eres.

Todos nos equivocamos. Si no, seríamos insoportables.

[verzulsan]

Hola Antoni,

Aun que de sabios sea rectificar, creo que esta vez no te has equivocado calculando las potencias de dos, lo de restar 1 esta mas relacionado con programacion que toma en muchos casos el 0 como primer elemento y a veces mezclando matematicas con programacion puede resultar en un quebradero de cabeza de cuando restar y cuando no hasta que le coges la practica.

En el ejemplo que puse antes: (2 * (2^27)) -1, le resto 1 por que es el valor que Fleming afirmaba a partir del cual da error la funcion, pero en realidad sin el 1 es el tope. Las potencias se calcularian asi:

2^1=2

2^2=2*2=4

2^3=2*2*2=8

2^4=2*2*2*2=16

...etc...

3^1=3

3^2=3*3=9

3^3=3*3*3=27 (justo aqui parece que algo tiene relacion con lo que estamos viendo)

Parece que el limite esta realmente en 2^(3^3) [2 elevado a 3 al cubo].

He adjuntado un archivo en donde se ve en dos cuadros distintos comparativos un residuo del limite y un residuo del limite menos 1, se ve como a medida que avanza el divisor, el residuo es el mismo divisor menos 1, ya que un incremento mas haria que se completara el modulo y alcanzaria el limite fuera de 2^(27).

Como ya no se como explicar, voy a recurrir a cuando aprendia de chico. Pondre un ejemplo con manzanas, asi siempre me enteraba yo de todo en el cole xD.

Viene un camion cargado con toneladas de manzanas, y hay un tipo repartiendo sacos delante del camion segun el interes del comprador, cada saco es de diferente capacidad e independientemente del tamaño de los sacos, ¡solo te dejan cargar 3 sacos (por decir una cifra).

Llega Juan, le dan un saco enorme y lo llena con 50 manzanas, lo descarga en su carreta y luego hace los otros dos sacos, total la carretilla llevaba 150 manzanas.

Llega Pedro, le dan un saco de capacidad 10, lo llena 3 veces y sin quejarse se va con sus 30 manzanas.

Cada uno se habria llevado 3 modulos del saco que les dieron, el numero de modulos era la norma del que repartia los sacos y el limite estaba en el tamaño del saco.

Una vez se ha terminado la cola de compradores, aun quedan manzanas en el camion, pero el camion esta estropeado asi que vienen tres furgonetas a llevarse las manzanas que quedan, pero cada una solo tiene capacidad de llevarse 500 manzanas, en el camion quedan 1800, por lo que se llevaran un modulo 500 de 1800 y en el camion quedaran 300 manzanas.... vamos que lo que vengo a decir es que solo te permiten 3 operaciones de recogida de manzanas y el que haya programado la funcion residuo, en lugar de 3, te permite 2^27, nada mas, no se por que no consigo que se me entienda xD, solo se permiten (134217728 OPERACIONES, operaciones digo y no limite de ninguna cantidad, 134217728 sacos de manzanas sea cual sea la cantidad que quepa en cada saco)

Bueno, creo que me ha entrado hambre xD, os dejo el archivo, mientras voy a comerme un par de manzanas, luego nos vemos.

Un saludo

Residuo ejemplo.zip

[neverdelimon1]

Hola

Maravillosas todas las explicaciones de los masters.

Aunque viendo la ayuda de MS Excel y dado que la funcion no especifica un límite y si revisamos los limites de MS Excel tampoco se podria derivar de ahi el 'error' me atrevo a decir que es un bug de dicha funcion, por lo que tendremos que esperar a que en futuras versiones de excel sea corregido.

Saludos cordiales

EDITO: Solo para compartir:

Abri el archivo en una hoja de calculo (de la competencia jejejej) que nuestro amigo y master verzulsan nos proporciono, y con las mismas formulas, donde arroja excel el error de #¡Num! alla si da los residuos =0

[Gerson Pineda]

Pues yo aqui no mas embelesado! con este descubrimiento de error y de no error (tal como lo ha comprobado el master never...)

Gracias a todos y especialmente a Fleming por abrir la brecha del descubrimiento

ESTO ES AYUDA EXCEL SEÑORES!!!

Saludos y que esten bien

[neverdelimon1]

Hola

Yo igual deseo agradecer de forma especial a fleming, quien fue quien descubrio tales errores (bug de Excel), ahora habria que ver la forma de hacercelos llegar a MS (eso si es que les interesa jejejejej)

saludos cordiales

[Fleming]

En efecto Neverdelimón1.

Lo ideal sería hacerlo llegar a MS para que lo arreglase, pero yo ni idea de cómo hacerlo.

Gracias y un cordial saludo

[Gerson Pineda]

Pedro (pegones1) crees que con tu experiencia y conocimiento esto lo puedas hacer llegar a MS?

Saludos

[verzulsan]

Hola,

Un comentario un poco tonto pero que ha venido a la cabeza como gracioso, igual os contratan como testers del excel y os financian la pagina por busquedas de bugs jajaja

Un saludo afectuoso Never++1, Fleming, Antoni y Gerson

PD: Seguramente Pedro lo sepa, estoy por sustituir el google de mi equipo por Pedroogle xD

[Gerson Pineda]

Puede que esta informacion sobre el termino "bug" le interese saber a mas de uno...

Historia del "bug" informático

y esta Error de Software

Saludos verzulsan

[Fleming]

Interesante la información, Gerson

Gracias

[pegones1]

Gerson me has pedido ayuda y te cuento mi experiencia con MOD (en inglés), la función RESTO.

Es un bug de Excel documentado en Wikipedia por generaciones (quiero decir releases - versiones de Microsoft).

Ni siquiera en Excel 2010 lo han solucionado:

Changes in Excel 2010

Published: May 12, 2010

As part of the accuracy improvements, Excel accepts a larger range of input values. As a result, it will return a wider range of results for certain functions. For example, the MOD function is able to take larger input values. For more information about the improvements to the statistical functions, see Function Improvements in Microsoft Office Excel 2010 (http://go.microsoft.com/fwlink/?LinkId=186148).

Traducción:

Como parte de las mejoras de precisión, Excel acepta una gama más amplia de valores de entrada. Como resultado, devolverá una gama más amplia de los resultados de ciertas funciones. Por ejemplo, la función RESTO es capaz de tomar grandes valores de entrada. Para obtener más información acerca de las mejoras a las funciones estadísticas, vea Función mejoras en Microsoft Office Excel 2010 (http://go.microsoft.com/fwlink/?LinkId=186148).

Eso sí han ampliado los valores para los que no se produce error hasta:

[B]=RESTO(2251799999999;2)[/B][/CODE]

[b]2251799999999[/b]=2^A41+2^A35+2^A34+2^A30+2^A27+2^A24+2^A23+2^A21+2^A20+2^A19+2^A18+2^A16+2^A13+2^A12+2^A10+2^A8+2^A7+2^A6+2^A5+2^A4+2^A3+2^A2+2^A1+1

[CODE][B]=RESTO(2251800000000;2)[/B][/CODE]

da error #¡NUM! en Excel 2010. Los programadores de [b]Excel 2011[/b] me han prometido que aumentarán este valor

En otras versiones el límite está en:

1125899999999 para Excel 2007 (la mitad menos 0,5)

134217728=2^27 para Excel 2003 y anteriores, ver aquí

En otros foros de Excel se habla de estos límites.

Adjunto ejemplo escrito en [b]Excel 2010 en Modo de compatibilidad[/b] con versiones anteriores, por lo que al abrirlo con distintas versiones de Excel generará errores en filas distintas.

Siempre podremos usar la fórmula alternativa:

[CODE]=B41-2*ENTERO(B41/2)
=número-divisor*ENTERO(número/divisor)[/CODE]

P.D.: Quiero comentarios con la versión de Excel que usáis y la fila dónde da error.

RestoExcel.zip

[neverdelimon1]

Hola

Quede decir... ¡magnifica exposición del master pedro!...

Por lo menos ahora ya sabemos a que atenernos jejejejeje

saludos cordiales

[Fleming]

De acuerdo con Never..1

Magnífico!

Como nos pide Pedro comentarios sobre la fila en que falla la función RESIDUO, comento que, como ya cité en el post 5, el fallo se produce en 2^28, es decir en la fila 28 de su archivo. Para Excel 2003.

Por cierto, Pedro, si no es molestia, ¿puedes confirmarme que es pura casualidad lo que comento en dicho post?

Muchas gracias

Tengo Excel 2003 y falla en 2^28, es decir en la fila 28 de tu archivo.

[verzulsan]

Hola Pedro, me encantó tu explicacion,

He probado el archivo en excel 2000,2003 y 2007 y en los tres el error se produce en la fila 28, ¿sera que tengo el 2007 escacharrado por tener los 3 instalados?, ni idea...

He modificado tu archivo a partir de la fila 28 con un doble residuo para que el primero ajuste el numero elevado a una potencia inferior a 2^x, se ve como cada 2^2, 2^2^2, 2^2^2^2, etc... la necesidad de la formula aumenta exponencialmente cada 1,2,4,8,16 casillas hasta que ya no sirve a partir de la fila 59 donde opté por elevarlo al numero de fila actual.

Espero sirva para la investigacion

Saludos a todos

PD: Marqué de rojo la fila 61 que salta un error en la formula de Fleming, pero luego he visto que salta antes en la 51, ¿Por que fallara esta formula?

RestoExcel2.zip

[Gerson Pineda]

Pedro no me queda mas que darte las gracias, por atender mi llamado

Solo quedamos a espera para otra version se supere el error

Saludos

[pegones1]

Hola Pedro, me encantó tu explicacion,

He probado el archivo en excel 2000,2003 y 2007 y en los tres el error se produce en la fila 28, ¿sera que tengo el 2007 escacharrado por tener los 3 instalados?, ni idea...

@Verzulsan, adjunto otro ejemplo con divisor igual a 1 y a 0,0000000001 con las fórmulas:

=RESTO(B2;1)

=RESTO(B2;0,0000000001)[/CODE]

y se comprueba que falla antes que para el divisor por 2 en todas las versiones de Excel...

Parece ser que en Excel 2010 falla aproximadamente cuando:

[b]num>=div*1125900000000[/b]

y mucho antes en las versiones anteriores.

Por ejemplo para:

div = 0,0000000001

div*1125900000000 = 0,0000000001*1125900000000 = 112,59

[b]¡¡¡ Da fallo en el número 113 al ser mayor que 112,59 !!![/b]

Como dice la ayuda Excel para la función RESTO, lo mejor es olvidarse de esta siniestra función y expresarla así:

[CODE]En inglés: MOD(n, d) = n - d*INT(n/d)
En español: RESTO(n; d)= n - d*ENTERO(n/d)[/CODE]

que devuelven el valor correcto.

Pedro no me queda mas que darte las gracias, por atender mi llamado

Solo quedamos a espera para otra version se supere el error

Saludos

[b]@Gerson[/b], después de ésto no creo que Microsoft sepa resolver este error garrafal, a no ser que metan en C# la función equivalente que sugieren en su ayuda

RestoExcel3.zip