Cómo encontrar desviaciones residuales en Excel

Hoja de cálculo.
Por c. taylor

Paso 1

Abre el Microsoft Excel.


Paso 2

Ingresa la primera serie de datos en la columna A y los demás datos relacionados con estos en la B. Los estadísticos suelen utilizar la "X" para representar el primer valor y la "Y" para el segundo, por lo que adoptaremos esa convención a los fines de este artículo.


Paso 3

Ingresa las etiquetas "Significa (X)", "St Dev [desviación estándar] (X)", "Significa (Y)", "St Dev [desviación estándar] (Y)", "Covarianza", "b", "a", "Fórmula lineal" y "Desviación residual" en las celdas E1 a E9.


Paso 4

Ingresa "=AVERAGE(A:A)" sin las comillas en la celda F1 y "=AVERAGE(B:B)" en la F3. De esta manera, calculas el promedio de cada columna.


Paso 5

Ingresa "=STDEV.S(A:A)" en la celda F2 y "=STDEV.S(B:B)" en la F4. De esta manera, calculas la desviación estándar de cada columna.


Paso 6

Ingresa "=COVARIANCE.S(A:A,B:B)" en la celda F5. De esta manera, calculas la covarianza entre ambas columnas.


Paso 7

Ingresa "=F5/F2^2" en la celda F6. Las líneas de regresión utilizan la ecuación "Y=bX+a". Esta fórmula calcula el valor de "b" en esa ecuación.


Paso 8

Ingresa "=F3-F6*F1" en la celda F7 para calcular el valor "a" de la ecuación de la línea de regresión.


Paso 9

Ingresa la siguiente fórmula en la celda F9: =CONCATENATE("Y = ",ROUND(F6,2),"X",IF(SIGN(F7)=1," + "," - "),ABS(ROUND(F7,2))) Esto no se utiliza para el cálculo, pero muestra la fórmula específica para la línea de regresión de tus datos, para la cual la desviación residual muestra variabilidad de datos.


Paso 10

Ingresa la fórmula "=$F$6*A1+$F$7" en la celda C1 y cópiala hacia abajo de la columna C, hasta el último valor. De esta manera, calculas la predicción del valor de "Y" por cada "X" dentro de la línea de regresión. Para poder copiar estas fórmulas de manera sencilla, haz clic en la celda C1 para seleccionarla. Haz clic y arrastra la esquina inferior derecha de la celda hacia abajo. Cuando sueltes el mouse, las fórmulas se copiarán de forma automática.


Paso 11

Ingresa la fórmula "=B1-C1" en la celda D1 y cópiala hacia abajo en la columna D hasta el último valor. De esta manera, calculas la diferencia entre los valores esperados y reales de "Y", que se utiliza para el cálculo de la desviación residual.


Paso 12

Ingresa "=SUMSQ(D:D)/(COUNT(D:D)-2)" en la celda F9 para calcular la desviación residual.