Cómo encontrar desviaciones residuales en Excel (En 12 Pasos)
Las desviaciones residuales ofrecen una medida de variabilidad de datos en las líneas de regresión. Estas líneas de regresión ilustran una relación predecible entre dos variables, por lo que saber la variabilidad de los datos implica tener un cierto nivel de confianza respecto de las predicciones de la línea de regresión. Por ejemplo, una desviación residual de 20 significa que la información se acercó mucho a la línea de regresión, por lo que esta ofrece predicciones confiables. Una desviación de 2000 significa que los datos estaban mezclados, por lo que las predicciones de esa línea de regresión son dudosas. Calcular la desviación residual a mano es tedioso, pero si creas una hoja de cálculo con el Microsoft Excel, será mucho más sencillo.
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.
Referencias
