Dudas con funciones de excel
A ver si me puedes ayudar con estas dos cuestiones.
Me estoy creando una hoja de cálculo para calificar a mis alumnos de Educación Física. Para ello necesito calcular Percentiles de las marcas que registro de mis alumnos. Pero encuentro algunas diferencias entre el cálculo manual y el cálculo con Excel. Ahí va un ejemplo: Sea una distribución de 10 datos (1,2,3,4,5,6,7,8,9,10). Intento calcular el Percentil 60. Con el método manual me da como resultado que P(60)=6, mientras que con Excel me da P(60)=6,4.
Otro ejemplo: Sean estos 9 datos (1,2,3,4,5,6,7,8,9). Con método manual P(60)=5,9. Con Excel 5,8.
¿Por qué ocurre esto?.
Mi otro problema es que no encuentro la forma de encontrar el valor mínimo de un rango de celdas que sea mayor que cero. Utilizo la función =MIN(Rango) y me obtiene el menor valor, pero cuando este valor ha de dividir a otro, si se trata de dividir por cero da error. ¿Cómo puedo encontrar el valor mínimo mayor que cero?
Me estoy creando una hoja de cálculo para calificar a mis alumnos de Educación Física. Para ello necesito calcular Percentiles de las marcas que registro de mis alumnos. Pero encuentro algunas diferencias entre el cálculo manual y el cálculo con Excel. Ahí va un ejemplo: Sea una distribución de 10 datos (1,2,3,4,5,6,7,8,9,10). Intento calcular el Percentil 60. Con el método manual me da como resultado que P(60)=6, mientras que con Excel me da P(60)=6,4.
Otro ejemplo: Sean estos 9 datos (1,2,3,4,5,6,7,8,9). Con método manual P(60)=5,9. Con Excel 5,8.
¿Por qué ocurre esto?.
Mi otro problema es que no encuentro la forma de encontrar el valor mínimo de un rango de celdas que sea mayor que cero. Utilizo la función =MIN(Rango) y me obtiene el menor valor, pero cuando este valor ha de dividir a otro, si se trata de dividir por cero da error. ¿Cómo puedo encontrar el valor mínimo mayor que cero?
3 respuestas
Respuesta de javigo
2
Ayer estuve dándole vueltas a cómo calcula el Excel los percentiles.
Por lo que puede comprobar, la fórmula básica que utiliza es:
P(60)=(60*(n-1)/100)+1
Si el nº resultante no es entero, localiza los datos más próximos por arriba y por abajo e interpola.
En tu primer caso, con muestra=10,
P(60)=(60*9/100)+1=6,4
Luego localiza la posición 6 y la siguiente (7).
La interpolación la hace con la siguiente fórmula: parte decimal*(amplitud del intervalo)+posición inferior.
En el ejemplo:
0,4*(7-6)+6=6,4
En el segundo caso (muestra=9), sería
P(60)=(60*8/100)+1=5,8
0,8*(6-5)+5=5,8
Una solución rápida es utilizar la fórmula de Excel PERCENTIL() pero incorporando el valor 0 en la muestra, de tal forma que considere un dato más. Así la muestra (0,1,2,3,4,5,6,7,8,9,10)
P(60)=(60*10/100)+1=7 y la posición 7 es el 6.
En cuanto a tu segunda duda, sólo veo dos alternativas sencillas:
1)Reemplazar los valores 0 por celdas vacías para que la función MIN() no las considere.
2)Crear un rango auxiliar donde cada celda se compare con una fórmula condicional, p.e. si los datos están en A1:A10 pones en B1:
=SI(A1<=0;"";A1) y copias hacia abajo. Luego usas MIN() en el rango auxiliar.
Bueno, ya me contarás si te sirve. Para cualquier duda, continua la pregunta.
Por lo que puede comprobar, la fórmula básica que utiliza es:
P(60)=(60*(n-1)/100)+1
Si el nº resultante no es entero, localiza los datos más próximos por arriba y por abajo e interpola.
En tu primer caso, con muestra=10,
P(60)=(60*9/100)+1=6,4
Luego localiza la posición 6 y la siguiente (7).
La interpolación la hace con la siguiente fórmula: parte decimal*(amplitud del intervalo)+posición inferior.
En el ejemplo:
0,4*(7-6)+6=6,4
En el segundo caso (muestra=9), sería
P(60)=(60*8/100)+1=5,8
0,8*(6-5)+5=5,8
Una solución rápida es utilizar la fórmula de Excel PERCENTIL() pero incorporando el valor 0 en la muestra, de tal forma que considere un dato más. Así la muestra (0,1,2,3,4,5,6,7,8,9,10)
P(60)=(60*10/100)+1=7 y la posición 7 es el 6.
En cuanto a tu segunda duda, sólo veo dos alternativas sencillas:
1)Reemplazar los valores 0 por celdas vacías para que la función MIN() no las considere.
2)Crear un rango auxiliar donde cada celda se compare con una fórmula condicional, p.e. si los datos están en A1:A10 pones en B1:
=SI(A1<=0;"";A1) y copias hacia abajo. Luego usas MIN() en el rango auxiliar.
Bueno, ya me contarás si te sirve. Para cualquier duda, continua la pregunta.
GRACIAS javigo:
En www.exceluciones.com éncontré solución para mi segunda cuestión. Consiste en crear una fórmula matricial como esta: "{=MIN(SI(A4:A13>0;A4:A13))}", se ingresa directamente tecleando las llaves inicial y final, o bien se ingresa "=MIN(SI(A4:A13>0;A4:A13))" y en lugar de pulsar INTRO, se pulsa CTRL+MAYUS+INTRO y Excel pone las llaves.
También hallé, para la primera cuestión, la solución que tú planteas con esta función: "=PERCENTIL((N1:N10;N12);0,3)" donde N12 es la celda que contiene el cero. Lo que pasa es que no me gusta tener celdas sueltas por ahí con datos que parecen introducidos accidentalmente, pues tiendo a borrar aquello que me parece raro. Por ello quisiera poder usar esta función, pero sustituyendo N12 por un cero: "=PERCENTIL((N1:N10;0);0,3)". El problema es que da error incluso encerrándolo entre paréntesis, corchetes o llaves. Si funcionara se simplificaría mucho mi trabajo.
Gracias. Saludos
En www.exceluciones.com éncontré solución para mi segunda cuestión. Consiste en crear una fórmula matricial como esta: "{=MIN(SI(A4:A13>0;A4:A13))}", se ingresa directamente tecleando las llaves inicial y final, o bien se ingresa "=MIN(SI(A4:A13>0;A4:A13))" y en lugar de pulsar INTRO, se pulsa CTRL+MAYUS+INTRO y Excel pone las llaves.
También hallé, para la primera cuestión, la solución que tú planteas con esta función: "=PERCENTIL((N1:N10;N12);0,3)" donde N12 es la celda que contiene el cero. Lo que pasa es que no me gusta tener celdas sueltas por ahí con datos que parecen introducidos accidentalmente, pues tiendo a borrar aquello que me parece raro. Por ello quisiera poder usar esta función, pero sustituyendo N12 por un cero: "=PERCENTIL((N1:N10;0);0,3)". El problema es que da error incluso encerrándolo entre paréntesis, corchetes o llaves. Si funcionara se simplificaría mucho mi trabajo.
Gracias. Saludos
Muchas gracias javigo. Me has ayudado mucho. No obstante si descubro algo, te lo hago saber.
Saludos.
Saludos.
Me temo que no he conseguido gran cosa. Por que he podido comprobar, la fórmula PERCENTIL requiere que le indiques una matriz de datos como referencia, por lo que tienes que introducir un rango de celdas. La única opción de que admitiera el cero en la fórmula sería introduciendo todos los valores de forma manual, algo así:
=PERCENTIL({0\1\2\3\4\5\6\7\8\9\10};0,6)
Pero evidentemente esto no sirve de mucho. No hay forma de combinar un número mnetido "a mano" con un rango de celdas.
La otra opción que queda es utilizar una fórmula matemática en lugar de la función PERCENTIL, pero el resultado es un poco largo.
Suponiendo que las celdas estén en el rango A2:A11, la fórmula sería:
=(60*CONTAR(A2:A11)/100+1-ENTERO(60*CONTAR(A2:A11)/100+1))*(INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1))-INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1)-1))+INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1)-1)
En fin, que no creo que te sirva de mucho. Ya me contarás.
=PERCENTIL({0\1\2\3\4\5\6\7\8\9\10};0,6)
Pero evidentemente esto no sirve de mucho. No hay forma de combinar un número mnetido "a mano" con un rango de celdas.
La otra opción que queda es utilizar una fórmula matemática en lugar de la función PERCENTIL, pero el resultado es un poco largo.
Suponiendo que las celdas estén en el rango A2:A11, la fórmula sería:
=(60*CONTAR(A2:A11)/100+1-ENTERO(60*CONTAR(A2:A11)/100+1))*(INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1))-INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1)-1))+INDICE(A2:A11;ENTERO(60*CONTAR(A2:A11)/100+1)-1)
En fin, que no creo que te sirva de mucho. Ya me contarás.
Respuesta de villaju
1
Necesito que me aclares cómo calculas tú los percentiles "de forma manual" (perdona mi ignorancia)
GRACIAS por tu atención:
Mira, en el primer caso calculo el 60% de 10, dado que son 10 datos. El resultado es 6. Voy a los datos una vez ordenados de menor a mayor y busco el dato que está en la posición 6. En este caso el dato 6 es un 6.
En el otro caso, calculo el 60% de 9 que es 5'4. Como no existe una posición 5'4, hago una interpolación lineal entre el dato que ocupa la posición anterior (5) y el que ocupa la posición posterior (6). El resultado de la interpolación es el percentil.
Gracias. Saludos
Mira, en el primer caso calculo el 60% de 10, dado que son 10 datos. El resultado es 6. Voy a los datos una vez ordenados de menor a mayor y busco el dato que está en la posición 6. En este caso el dato 6 es un 6.
En el otro caso, calculo el 60% de 9 que es 5'4. Como no existe una posición 5'4, hago una interpolación lineal entre el dato que ocupa la posición anterior (5) y el que ocupa la posición posterior (6). El resultado de la interpolación es el percentil.
Gracias. Saludos
Primero responderé a la primera pregunta que me hiciste, cómo hallar el valor mínimo de un rango mayor que cero:suponiendo que los datos se encuentran en el rango A1:C10
introduce la siguiente fórmula en otra celda:=MIN(SI(A1:C10>0;A1:C10)), pero luego, en vez de intro, presiona Control+Mayúsculas+Intro. Si todo ha salido bien, te aparecerán unos corchetes a los lados en la fórmula: indican que se trata de una clase especial de fórmulas, las fórmulas matriciales, necesarias para solucionar la pregunta que planteas.
En cuanto a la segunda pregunta, la verdad es que no se contestarte, he estado mirando en internet, pero no se nada de estadística, quizás deberías probar si en todoexpertos hay un foro de este tema, pues en tal caso, casi seguro que habrán utilizado Excel, y te podrán ayudar, siento no poder contestar otra cosa, aunque te aseguro que lo intenté.
introduce la siguiente fórmula en otra celda:=MIN(SI(A1:C10>0;A1:C10)), pero luego, en vez de intro, presiona Control+Mayúsculas+Intro. Si todo ha salido bien, te aparecerán unos corchetes a los lados en la fórmula: indican que se trata de una clase especial de fórmulas, las fórmulas matriciales, necesarias para solucionar la pregunta que planteas.
En cuanto a la segunda pregunta, la verdad es que no se contestarte, he estado mirando en internet, pero no se nada de estadística, quizás deberías probar si en todoexpertos hay un foro de este tema, pues en tal caso, casi seguro que habrán utilizado Excel, y te podrán ayudar, siento no poder contestar otra cosa, aunque te aseguro que lo intenté.
Respuesta de blackdog2003
1
Ante todo ecibe un cordial saludo. Con respecto a tu pregunta, se debe a la manera en que excel calcula el Percentil, ya que si el argumento que no es un múltiplo de 1/(n - 1), la función PERCENTIL interpola para determinar el valor en el k-ésimo percentil.
Con respecto a tu segunda pregunta, debes crear una macro para verificar esto, si lo lo deseas dime y te estaré enviando el código con esta opción.
Con respecto a tu segunda pregunta, debes crear una macro para verificar esto, si lo lo deseas dime y te estaré enviando el código con esta opción.