Líneas de tendencia, con Funciones.

No quería dejar sin solucionar una entrada anterior (Líneas de tendencia) en la cual se solucionó mediante la forma gráfica el problema ha encontrar qué recta de regresión se ajustaba mejor a unas mediciones en un determinado modelo.
Se trata de encontrar cuáles son los parámetros de una recta de regresión para unas mediciones de Concentración-Absorbancia. Partiendo de los siguientes datos

Lo primero para trabajar con la funcion ESTIMACION.LINEAL será convertir nuestra tabla de datos en una de dos variables; por seguir la misma norma que en la entrada anterior, definiremos como variable X a la concentración y variable Y a la absorbancia (es importante definirlo de igual forma, ya que si intercambiaramos su situación, nuetros parámetros de recta-pendiente y constante-serían disintos).

A partir de esta nueva tabla generaremos ambos parámetros de nuestra recta de regresión. Utilizaremos la función ESTIMACION.LINEAL(conocido_y;conocido_x;constante;estadistico); para ello en alguna celda de nuestra hoja de cálculo ejecutaremos esta función, sustituyendo conocido_y por el rango $B$3:$B$17 y conocido_x por el rango $A$3:$A$17, puesto que estamos calculando una recta con constante forzaremos incluyendo un 1 en el argumento 'constante', al no interesarnos (de momento) el resto de estadísticos que nos podría devolver esta función, no cumplimentamos el último de los argumentos.
Quedaría entonces nuestra función así:
ESTIMACION.LINEAL($B$3:$B$17;$A$3:$A$17;1;0)
Esto no es todo, esta función, momentaneamente sólo nos devuelve un parámetro, usaremos entonces una nueva función para encontrar el parámetro 'constante' que nos falta para disponer de nuestra recta final. Deberemos combinar una función INDICE(matriz; fila;columna) con la función ESTIMACION.LINEAL, de la siguiente manera.
Para conocer cuál es la pendiente de la recta de regresión ente ambas variables escribiremos:
=INDICE(ESTIMACION.LINEAL($B$3:$B$17;$A$3:$A$17;1;0);1;1)
y para conocer cuál es la constante de la recta de regresión:
=INDICE(ESTIMACION.LINEAL($B$3:$B$17;$A$3:$A$17;1;0);1;2)
Obteniendo entonces la solución y = 0,0343x + 0,1153

En posteriores entradas explicaremos mejor el sentido de la función INDICE, así como todas las posibilidades de la fución ESTIMACION.LINEAL.