Fórmulas de Excel para Estadística
Cálculo de Parámetros Iniciales
- Para obtener N (Total de datos):
=CONTAR(primer_dato:último_dato) - Para obtener D+ (Valor máximo):
=MAX(primer_dato:último_dato) - Para obtener D- (Valor mínimo):
=MIN(primer_dato:último_dato) - Para obtener R (Rango):
=D+ - D- - Para obtener Ng (Número de grupos/filas):
=1 + 3.33 * LOG(N). Posteriormente, se debe aproximar al entero superior. Ejemplo: 7.77 ≈ 8. El número mayor de la aproximación será el número de filas que tendrá la tabla. - Para obtener I (Intervalo):
=R / Ng(se debe aproximar).
Si el problema solicita calcular la media aritmética, la moda y la mediana, se deben crear 8 filas por cada columna de: LAI, LAS, LRI, LRS, Xs, F, FA, fXs. Si no se piden estos tres datos, solamente se realizan 7 filas por columna.
Construcción de la Tabla de Frecuencias
Columna de Límites Aparentes Inferiores (LAI)
El primer dato es D-. Para el segundo dato, se utiliza la fórmula: =casilla_primer_dato_LAI + I. Es fundamental fijar la celda del intervalo I colocando el símbolo de $ entre la letra y el número de la casilla. Se repite el proceso sucesivamente según el número de filas.
Columna de Límites Aparentes Superiores (LAS)
El primer dato de esta columna será el segundo dato de la columna LAI. El segundo dato se calcula como: =primer_dato_LAS + I (fijando la celda de I con el símbolo $).
Cálculo de Lr (Límite Real)
Este valor se calcula fuera de la tabla principal: =(segundo_dato_LAI - primer_dato_LAS) / 2.
Columna de Límites Reales Inferiores (LRI)
Fórmula: =primer_dato_LAI - Lr. Se debe fijar la casilla de Lr con el símbolo $ y repetir el proceso para cada fila de la columna LAI.
Columna de Límites Reales Superiores (LRS)
Fórmula: =primer_dato_LAS + Lr. Se debe fijar la casilla de Lr con el símbolo $ y repetir el proceso para cada fila de la columna LAS.
Columna de Marca de Clase (Xs)
Fórmula: =(primer_dato_LAI + primer_dato_LAS) / 2. Se repite para cada fila correspondiente.
Columna de Frecuencia Absoluta (F)
Se utiliza la fórmula matricial: {=FRECUENCIA(datos_originales; datos_LAS)}. Tras ingresar la fórmula, se arrastra el mouse para abarcar el número de filas de la tabla. Para que los valores se calculen correctamente, se debe seleccionar el rango, presionar la barra de fórmulas y ejecutar el comando Control + Shift + Enter. Si la Autosuma de esta columna coincide con N, el cálculo es correcto.
Columna de Frecuencia Acumulada (FA)
El primer dato es igual al primer dato de F. El segundo dato se calcula como: =segundo_dato_F + primer_dato_FA. Se repite el proceso sucesivamente.
Columna de fXs (Frecuencia por Marca de Clase)
Fórmula: =primer_dato_Xs * primer_dato_F. Se repite para todas las filas y, al finalizar, se suman todos los valores de la columna.
Medidas de Tendencia Central
Media Aritmética
Fórmula: =suma_fXs / suma_F (el resultado se aproxima).
Mediana
- Lugar:
=suma_F / 2 - LRI de la mediana: Corresponde al cuarto valor de la columna LRI.
- Faa (Frecuencia acumulada anterior): Es el tercer valor de la columna FA.
- F de la mediana: Es el cuarto valor de la columna F.
- I de la mediana: Es el intervalo calculado al inicio.
- Fórmula Mediana:
=LRI + ((lugar - Faa) / F) * I
Moda
- D1:
=cuarto_valor_F - tercer_valor_F - D2:
=cuarto_valor_F - quinto_valor_F - LRI: Cuarto valor de la columna LRI.
- Fórmula Moda:
=LRI + (D1 / (D1 + D2) * I)
Medidas de Posición y Dispersión
Cuartiles (Q1 y Q3)
Cálculo de Q1:
- Lugar:
=(suma_F * 1) / 4 - LRI: Tercer dato de LRI.
- Faa: Segundo valor de FA.
- F: Tercer valor de F.
- Fórmula:
=LRI + ((lugar - Faa) / F) * I
Cálculo de Q3:
- Lugar:
=(suma_F * 3) / 4 - LRI: Quinto valor de LRI.
- Faa: Cuarto valor de FA.
- F: Quinto valor de F.
- Fórmula:
=LRI + ((lugar - Faa) / F) * I
Rangos
- Rango Intercuartílico:
=Q3 - Q1 - Rango Semi-intercuartílico:
=(Q3 - Q1) / 2
Deciles (D2, D4, D6, D7, D8, D9)
Para todos los deciles, la fórmula base es =LRI + ((lugar - Faa) / F) * I, aproximando el resultado final. El lugar se calcula como (suma_F * k) / 10, donde k es el número del decil.
Percentiles (P30, P43, P50, P90)
Para los percentiles, la fórmula base es =LRI + ((lugar - Faa) / F) * I. El lugar se calcula como (suma_F * k) / 100, donde k es el número del percentil. Se debe identificar en la tabla el LRI, la Faa y la F correspondiente a la posición calculada.