martes, 17 de enero de 2012

La función DESVEST.P sujeta a una condición en Excel.

Abordaremos hoy una cuestión estadística, que en más de una ocasión me han planteado; en concreto hablaré sobre la opción de trabajar sobre muestras (entendiendo muestra como parte de la población o rango de datos en Excel) con funciones estadísticas de Excel, esto es, trabajar con rangos condicionados con ciertas funciones estadísticas, en este caso sobre la función DESVEST.P.
Veamos la cuestión planteada:

...preguntaba si existe alguna forma para calcular, aparte del promedio, la desviación estándar "condicionada". Es decir, de un rango de datos que cumplan ciertas condiciones...


Supongamos un rango de celdas con un registro o lectura de datos de toda una población, pero deseamos obtener cierta información exclusivamente para la muestra de datos 'sesgada' que cumpla cierta condición. Partimos de un ejemplo de datos:

La función DESVEST.P sujeta a una condición en Excel.


Trataremos de obtener la desviación estándar unicamente para los valores del rango A1:A6 que cumplan la condición de ser mayores a 10.
Si aplicamos la función DESVEST.P al rango A1:A6
=DESVEST.P(A1:A6)
obtendríamos la desviación estándar de todo el rango, entendiendo el rango como la población total a estudio. Esta función omite los valores lógicos y de texto. Igual proceder si los datos correspondieran ya de inicio a una muestra de datos, pero trabajando con la función DESVEST.M.
Recordemos que la desviación estándar es la medida de la dispersión de unos valores con respecto al promedio (= media).

La función DESVEST.P sujeta a una condición en Excel.


Analicemos entonces como es posible conseguir que sobre el rango de datos A1:A6 la desviación estándar condicionada sólo a los registros mayores a 10.
Para ello, deberemos conseguir un rango de datos válido para nuestra condición (valores > 10), lo que lograremos con la fórmula matricial:
SI(A1:A6>10;A1:A6;"")
Una vez contruido el rango objetivo, ya podríamos aplicarle la función estadística DESVEST.P ejecutada matricialmente (Ctrl+Maysc+Enter)
=DESVEST.P(SI(A1:A6>10;A1:A6;""))

Por supuesto, llegando al mismo resultado que si únicamente trabajáramos sobre los valores que cumplieran la condición dada, como podemos observar en la imagen siguiente:

La función DESVEST.P sujeta a una condición en Excel.

47 comentarios:

  1. Para que version de excel sirve esa formula? lo probé en excel 2007, pero con tiempo, y me da algo super distinto. Ayuda!

    ResponderEliminar
    Respuestas
    1. Hola Tinti,
      la fórmula es para Excel 2010, para Excel 2007 sería similar, con la función DESVESTP. Salvo ese detalle funciona exactamente igual y con el mismo resultado... debes tener mucho cuidado al trabajar con tiempos y su diferentes formatos, pero en general para cualquier fórmula.
      Aseguraté de haber trabajo correctamente los tiempo, en su conversión, por que en principio da igual la versión de Excel (2007 ó 2010).
      Slds

      Eliminar
  2. Hola,
    estoy buscando la funcion que busque en la colmuna A las filas que tengan la letra "a" y que calcule la desviacion standar con lo valores de la columna B. O sea la desviacion standar entre 4 y 6.

    A B
    a 4
    b 8
    a 6
    e 7

    ResponderEliminar
    Respuestas
    1. Hola,
      sólo tienes que seguir las indicaciones de esta entrada, en tu caso la fórmula, ejecutada matricialmente, sería:
      =DESVEST.P(SI(A1:A4="a";B1:B4;""))
      Un saludo

      Eliminar
    2. hey compañero con esa formula no da la desviación creo que esta mala esa formula porque dejas un espacio sin llenar entre comillas

      Eliminar
    3. Hola Hector,
      se trata de calcular la desviación únicamente de los importes correspondientes al valor 'a'.
      Slds

      Eliminar
  3. Hola, necesito calcular de desvesta de los datos negativos y positivos independientemente dentro de un amplio set de datos

    ResponderEliminar
    Respuestas
    1. Hola,
      es lo mismo que está explicado en esta entrada pero modificando algo la fórmula. Debes ejecutar matricialmente (Ctrl+Mayusc+Enter) las siguientes funciones, suponiendo en rango de datos en A1:A100:
      Para los negativos:
      =DESVESTA(SI(A1:A100<0;A1:A100;""))
      Para los positivos:
      =DESVESTA(SI(A1:A100>0;A1:A100;""))
      Slds

      Eliminar
    2. Muchas gracias....hasta que no entendí como ejecutar la matriacial fue confuso, una vez ejecutada ningún problema...

      Excelente ayuda

      Eliminar
  4. Hola, estoy intentando calcular la desviación estándar según la siguiente fórmula: StdDev = SQRT(SUM[(CLOSE — SMA(CLOSE, N))^2, N]/N) y me pregunto si es la misma que usa excel con la DESVEST.P ya que los resultados que me han salido son diferentes.

    Muchas gracias por la ayuda

    ResponderEliminar
    Respuestas
    1. Hola José,
      DESVEST.P (desviación estándar de toda la población) se calcula como la raiz cuadrada del sumatorio de la diferencia al cuadrado del valor y el promedio, todo el sumatorio dividido por el número de elementos de la población. algo así, usando tu notación:
      =SQRT[SUM((x-media)^2)/n]
      No he sabido identificar que es SMA en la fórmula que planteas.
      Pero si no te da lo mismo quizá debas emplear una desviación sobre la muestra ??
      Slds

      Eliminar
  5. necesito ayuda para calcular desviacion estandar entre valores >=-2 y <2

    ResponderEliminar
    Respuestas
    1. Hola que tal estás?
      Un placer saludarte.
      Prueba con esta función ejecutada matricialmente
      =DESVESTA(SI(A1:A10>=-2;SI(A1:A10<2;A1:A10;"")))
      suponiendo los valores en A1:A10

      Un cordial saludo

      Eliminar
  6. Hola estimado, esta fórmula podría permitir más de una condición? Por ejemplo, calcular la desviación para los datos que son positivos, y que además son "extranjeros", en la siguiente planilla?

    1 45 nacional
    2 -3 extranjero
    3 34 extranjero
    4 -12 nacional
    5 24 extranjjero......

    Muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      sería necesario incluir un segundo condicional..
      Podría ser algo así:
      =DESVEST.P(SI(A1:A6>0;SI(B1:B6="extranjero";A1:A6;"");"")
      ejecutándola matricialmente (presionando Ctrl+Mayusc+Enter)
      Saludos

      Eliminar
  7. Hola necesito saber cual es la diferencia entre la Desvest p y la Desvest m .... Y que implica usar una o la otra a un conjunto de datos

    ResponderEliminar
    Respuestas
    1. Hola Jesús,
      la diferencia es que DESVEST.P entiende trabajamos sobre la población total y DESVEST.M sobre una muestra de esa población, y por tanto las fórmulas internas que aplica son distintas, en un caso divide la diferencia del valor entre la media entre n (número de datos) y en el otro entre n-1

      Un saludo

      Eliminar
  8. Estoy intentado obtener la desviación estándar de una muestra condicionada a valores superiores a 0. Utilizo la fórmula =DESVEST.P(SI(E4:E11>0;E4:E11;"")), pero siempre obtengo la misma respuesta #VALOR!. La versión de excel que utilizo es la 2011 para Mac.
    Gracias por su ayuda. Cordiales saludos.
    Tomás Motos

    ResponderEliminar
    Respuestas
    1. Hola Tomás,
      para validar la fórmula presiona Ctrl+Mayusc+Enter en lugar de sólo Enter.
      Lo que no tengo claro es que esto funcione para versiones Mac, ya que las configuraciones son diferentes.
      Saludos

      Eliminar
  9. Muy agradecido por la rápida respuesta. Si funciona. Tomás

    ResponderEliminar
    Respuestas
    1. Perfecto!
      gracias por confirmar
      Un cordial saludo

      Eliminar
  10. Hola necesito calcular la desviación estandar de 11000 entradas, comparandola con el nombre y apellidos de una persona, teniendo en cuenta que pueden repetirse nombres y apellidos. Trato de explicarlo un poco mejor, en columna A, están los gastos de la persona, y columnas B y C nombres y apellidos respectivamente. Cada persona puede tener varias entradas, en función de fechas a lo largo del año, y la idea es conseguir la desviación de todas las entradas de una misma persona con respecto al global de entradas.

    ResponderEliminar
    Respuestas
    1. La idea sería la misma que la expuesta, pero añadiendo una condición más:
      =DESVESTP(SI(B1:B11000=E1;SI(C1:C11000=F1;A1:A11000;"")))
      ejecutada matricialmente!!!
      siendo E1 el nombre buscado y F1 el apellido
      Saludos

      Eliminar
  11. ismael te hago una consulta, estos que estas explicando funciona para una tabla con varias columnas de datos? por que estoy haciendo lo que me decis y para suma me funciona pero para el devio no. muchas gracias

    ResponderEliminar
    Respuestas
    1. Hola Fermin,
      sí, debe funcionar igual para un rango de varias columnas que para el ejemplo...
      Lo he verificado, por si acaso...

      Asegúrate que lo ejecutas matrcialmente!! (presionando Ctrl+Mayus+Enter en lugar de solo Enter)
      Saludos

      Eliminar
    2. ismael, muchas gracias tu respuesta. encontre el problema. Lo que sucede es que dentro de mi tabla celdas sin valores. entoces cuanto tiro la matricial me los toma como valor 0, y que modifique mi desvio. que solucion me recomendas para evitar eso?

      Eliminar
    3. Hola,
      siempre puedes incluir una segunda condición anidada que saque fuera del cálculo las celdas vacías:
      =DESVEST.P(SI(A1:A8<10;SI(A1:A8<>"";A1:A8;"")))
      Saludos

      Eliminar
  12. Hola, qué tal?

    Quiero calcular la desviación estándar de una columna de valores (A), pero siempre que se cumplan las condiciones de otras dos columnas, como ser hombre (D) y la otra tener más de 18 y menos o igual de 29 años (B). Le doy como matricial pero aún así el resultado está bastante alejado de que sería.

    He probado con esto:

    =DESVEST(SI(B2:B966>18;SI(B2:B966<=29;SI(D2:D966=1;A2:A966))))

    Uso excel 2013. Te agradezco si me puedes ayudar. Mi nombre es Miguel.

    ResponderEliminar
    Respuestas
    1. Hola,
      si te fijas en el ejemplo de este post, para completar la función, se añade un valor de falso como """", prueba matricialmente con:
      =DESVEST(SI(B2:B966>18;SI(B2:B966<=29;SI(D2:D966=1;A2:A966;""))))
      Saludos

      Eliminar
  13. Gracias por responder tan rápido pero incluso con el valor falso sigo sin conseguirlo. Me da 0,00 en hombres y 0,49 en mujeres. Hay alguna forma de hacerte llegar el archivo con esas columnas? si es así, cómo? Solo sé que debe dar 0,62 en hombres y 0,08 en mujeres

    Gracias y un saludo.

    ResponderEliminar
  14. hola ismael te explico mi duda: debo realizar la cuasidesviacion de una tabla de salarios pero solo la de hombres, me dan 1000 datos y no consigo sacar la formula para hacerla solo con ese parametro de busqueda para que me devuelva los datos de los hombres unicamente,, te pongo un fragmento de la tabla por si te es de ayuda
    A B
    782,12 Hombre
    1346,27 Hombre
    1256,76 Mujer
    1329,40 Hombre
    1178,02 Hombre
    1937,16 Hombre
    941,75 Mujer
    1613,40 Hombre
    1215,08 Mujer
    1337,54 Mujer

    se que deberia ser desvest.p(si(pero aqui no se muy bien que poner en especial dentro de los parametro verdadero o falso
    gracias por tu tiempo daniel

    ResponderEliminar
    Respuestas
    1. Hola Daniel,
      se trataría de aplicar una función matricial, algo de este estilo:
      =DESVEST.P(SI(B1:B10="Hombre";A1:A10))
      ejecutándola matricialmente, esto es, presionando Ctrl+Mayusc+Enter en lugar de solo con Enter

      Saludos

      Eliminar
  15. Hola; Necesito calcular la Desviación Estandar de la columna B, con los datos que están entre una fecha y otra, fecha que está definida en la columna A. El objetivo final es que vea mes a mes si mis datos son menos dispersos, con lo cual debería hacer comparaciones mensuales. Agradezco desde ya tu ayuda.

    ResponderEliminar
    Respuestas
    1. Hola D.Maria,
      puedes optar por las fórmulas matriciales comentadas más arriba comentadas por otros usuarios, o bien aplicar una Tabla dinámica, agrupando las fechas por mes y mostrando el dato resumido por desviación estándar.

      Saludos

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

    ResponderEliminar
  17. Hola,
    Tengo una duda, tengo un calculo de un promedio.si.conjunto y como condición esta que el promedio debe estar comprendido entre valores >90 y <110, y eso esta bien. El problema esta que tambien se debe cumplir que la desviaion estandar relativo debe ser menor al 2%. Entonces al yo tener por ejemplo 3 valores como 90,95 y 96, la condicion uno se cumple pero la desviacion supera el 3%, entonces como haria para que el promedio se calcule con los otros dos valores y asi la desviacion tambien disminuya y se condicione tambien. Gracias espero su respuesta
    Saludos

    ResponderEliminar
    Respuestas
    1. Hola Alejandro,
      probablemente en un foro de estadísticos te expliquen mejor el caso... si he entendido bien tu planteamiento, hasta donde yo sé la desviación es un único valor para un conjunto de datos; por ejemplo, si tu conjunto de datos es (en rango A1:A3):
      90 - 95 - 96
      su desviación estándar:
      =DESVEST.M(A1:A3) = 3,21
      y su promedio:
      =PROMEDIO(A1:A3) = 93,67

      estos cálculos están asociados a los tres datos, no de manera individual.. por tanto no aplica la función promedio.si.conjunto que evaluaría condiciones individuales para cada valor del conjunto de datos 90-95-96.

      Espero haberte orientado algo más...
      Saludos

      Eliminar
  18. Hola, quiero calcular la desviación estandar para varias filas que representan diferentes plazos, sin embargo, cada plazo tiene diferente cantidad en su población.

    días prov 1 prov 2 prov 3 prov 4 prov 5 prov 6 prov 7 prov 8 n° datos PROM
    7 0,28 s/v s/v s/v 0,36 0,305 0,62 s/v 4 0,39125
    14 0,75 s/v s/v s/v 0,8 0,735 1,1 s/v 4 0,84625
    30 1,75 1,7 1,8 2,3 1,75 1,725 2,1 1,71 8 1,854375
    60 3,55 3,6 3,68 4,375 3,75 3,555 4,23 3,6 8 3,7925
    90 5,35 5,4 5,5 6,6 5,65 5,405 6,03 5,25 8 5,648125

    ¿cuál sería la forma de correcta de hacerlo?

    ResponderEliminar
    Respuestas
    1. Hola Franco,
      creo es más un problema estadístico que de Excel :(
      Las desviaciones en Excel (con DESVEST.P, por ejemplo) se calculan para un rango de valores dado...
      Lo siento, no sabría decirte.. quizá en un foro estadístico.
      Saludos

      Eliminar
    2. excel ofrece 2 formas: desvest.p y desvest.m, para población total y para muestra, éstas se diferencian en que al aplicar las fórmulas, la división es por n y n-1 respectivamente.

      De todas formas se agradece el interés.

      Eliminar
    3. Sí, es correcto P (para la poblacional) y M (para la muestral)... pero en ambos casos deberemos seleccionar un rango de valores que el tomará como MUESTRA o POBLACIÓN total dividiendo entre n o n-1
      que era la duda que quedaba...
      Sin esos rangos poco podemos calcular
      :(
      Saludos y suerte!!!

      Eliminar
  19. Hola, Ayuda!!, ¿Cómo puedo agregar desvest.m,a mi excel 2007? No me aparece y no se como activarla.

    Gracias

    ResponderEliminar
    Respuestas
    1. Hola,
      es una función que no existía en la versión 2007... creo recordar apareció en la versión Excel 2010.
      La equivalente en 2007 es:
      DESVEST
      que calcula la desviación estándar de una muestra.

      Saludos

      Eliminar
    2. De acuerdo, muchas gracias, por último ¿la DESVESTP es la poblacional? ver. 2007?

      Saludos

      Eliminar
    3. correcto,
      P de población y M de muestra
      Slds

      Eliminar
    4. Muchas gracias, Saludos

      Eliminar