Funciones estadísticas de Excel: GROWTH

Resumen

En este artículo se describe la función GROWTH en Excel, se muestra cómo se usa la función y se comparan los resultados de la función para Excel 2003 y para versiones posteriores de Excel con resultados de GROWTH en versiones anteriores de Excel. GROWTH se evalúa llamando a la función relacionada LINEST. Se resumen los cambios exhaustivos de LINEST para Excel 2003 y para versiones posteriores de Excel y sus implicaciones para GROWTH.

Información de Microsoft Excel 2004 para Macintosh

Las funciones estadísticas de Excel 2004 para Mac se actualizaron mediante los mismos algoritmos que se usaron para actualizar las funciones estadísticas en Excel 2003 y en versiones posteriores de Excel. Cualquier información de este artículo que describa cómo funciona una función o cómo se modificó una función para Excel 2003 o para versiones posteriores de Excel también se aplica a Excel 2004 para Mac.

Más información

La función GROWTH(known_y, known_x, new_x, constante) se usa para realizar un análisis de regresión donde se ajusta una curva exponencial. Se utiliza un criterio de mínimos cuadrados, y GROWTH intenta encontrar el mejor ajuste según ese criterio. Known_y representa los datos de la "variable dependiente" y known_x representan los datos de una o varias "variables independientes". El archivo de ayuda GROWTH describe casos poco frecuentes en los que se puede omitir el segundo o tercer argumento.

Suponiendo que hay variables de predicción p, GROWTH básicamente llama a LOGEST. LOGEST se ajusta a una ecuación de la forma:

y = b * (m1^x1) * (m2^x2) * ... * (mp^xp)

Los valores de los coeficientes, b, m1, m2, ..., mp se determinan que dan el mejor ajuste a los datos y.

Si el último argumento "constante" se establece en TRUE, quiere que el modelo de regresión incluya el coeficiente multiplicativo b en el modelo de regresión. Si se establece en FALSE, b se excluye al configurarlo esencialmente en 1. El último argumento es opcional; si se omite el argumento, se interpreta como TRUE.

Para facilitar la exposición en el resto de este artículo, supongamos que los datos están organizados en columnas para que known_y sea una columna de datos y y known_x sea una o varias columnas de datos x. Las dimensiones (longitudes) de cada una de estas columnas deben ser iguales. también se supone que los New_x se organizan en columnas y debe haber el mismo número de columnas para new_x que para known_x. Todas nuestras observaciones siguientes son igualmente verdaderas si los datos no están organizados en columnas, pero es más fácil analizar el caso único (más usado).

Después de calcular el mejor modelo de regresión de ajuste (mediante esencialmente una llamada a la función LOGEST de Excel), GROWTH devuelve los valores predichos asociados a los new_x.

En este artículo se usan ejemplos para mostrar cómo GROWTH se relaciona con LOGEST y para señalar problemas con LOGEST en versiones de Excel anteriores a Excel 2003 que se traducen en problemas con GROWTH. GROWTH llama eficazmente a LOGEST, ejecuta LOGEST, usa coeficientes de regresión en la salida de LOGEST en su cálculo de los valores de y previstos que están asociados con cada fila de new_x, y presenta esta columna de valores de y predichos. Por lo tanto, debe conocer los problemas en la ejecución de LOGEST. Cuando se llama a LOGEST, llama eficazmente a LINEST. Aunque el código para GROWTH y LOGEST no se ha reescrito para Excel 2003 y para versiones posteriores de Excel, se han realizado cambios extensos (y mejoras) en el código LINEST.

Como suplementos de este artículo, se recomienda encarecidamente el siguiente artículo sobre LINEST. Contiene varios ejemplos y problemas de documentos con LINEST en versiones de Excel anteriores a Excel 2003.

Para obtener más información sobre LINEST, seleccione el número de artículo siguiente para ver el artículo en Microsoft Knowledge Base:

828533 Descripción de la función LINEST en Excel 2003 y en Excel 2004 para Mac

También se recomienda el archivo de ayuda LINEST, como se ha revisado para Excel 2003.

En el siguiente artículo sobre LOGEST se explica cómo INTERACTÚA LOGEST con LINEST. Estos detalles se omiten aquí.

Para obtener más información, vea Funciones estadísticas de Excel: LOGEST.

Dado que el enfoque de este artículo se centra en problemas numéricos en versiones de Excel anteriores a Excel 2003, este artículo no tiene muchos ejemplos prácticos del uso de GROWTH. El archivo de ayuda de GROWTH contiene ejemplos útiles.

Sintaxis

GROWTH(known_y's, known_x's, new_x's, constant)

Los argumentos, known_y, known_x y new_x deben ser matrices o intervalos de celdas que tengan dimensiones relacionadas. Si known_y es una columna por filas m, las columnas de known_x son columnas c por filas m, donde c es mayor o igual que una. C es el número de variables de predicción; m es el número de puntos de datos. New_x deben ser matrices de c columnas por r filas, donde r es mayor o igual a uno. (Las relaciones similares en las dimensiones deben mantenerse si los datos se colocan en filas en lugar de columnas). Constante es un argumento lógico que debe establecerse en TRUE o FALSE (o como 0 o 1, que Excel interpreta como FALSE o TRUE, respectivamente). Los tres últimos argumentos para GROWTH son opcionales; vea el archivo de ayuda GROWTH para ver las opciones de omitir el segundo argumento, tercer argumento o ambos; Si se omite el cuarto argumento, se interpreta como TRUE.

El uso más común de GROWTH incluye dos rangos de celdas que contienen los datos, como GROWTH(A1:A100, B1:F100, B101:F108, TRUE). Dado que normalmente hay más de una variable de predicción, el segundo argumento de este ejemplo contiene varias columnas. En este ejemplo, hay 100 sujetos, un valor de variable dependiente (known_y) para cada asunto y cinco valores de variable dependientes (known_x) para cada asunto. Hay ocho temas hipotéticos más en los que quiere usar GROWTH para calcular los valores y previstos.

Ejemplo de uso

Se proporciona un ejemplo de hoja de cálculo de Excel para ilustrar los siguientes conceptos clave:

• Cómo interactúa CRECIMIENTO con LOGEST
• Problemas que se producen con GROWTH (o LOGEST y LINEST) debido a los x conocidos colineales en versiones de Excel anteriores a Excel 2003

Nota:

En el artículo sobre LINEST se proporciona una amplia explicación del segundo elemento con viñetas en el contexto de LINEST.

Para ilustrar la función GROWTH, cree una hoja de cálculo de Excel en blanco, copie la tabla siguiente, seleccione la celda A1 en la hoja de cálculo de Excel en blanco y pegue las entradas para que la tabla siguiente rellene las celdas A1:K35 en la hoja de cálculo.

Un	B	C	D	E	F	G	H	Yo	J	K
y:	de x:
=EXP(F2)	1	2	1		1
=EXP(F3)	3	4	1		2
=EXP(F4)	4	5	1		3
=EXP(F5)	6	7	1		4
=EXP(F6)	7	8	1		5
nuevas x's	9	11
	12	14
CRECIMIENTO usando las columnas B,C:				Valores para Excel 2002 y para versiones anteriores de Excel:
			Valores para Excel 2003 y para versiones posteriores de Excel:
=GROWTH(A2:A6,B2:C6,B7:C8,VERDADERO)				#NUM!				472,432432563203
=GROWTH(A2:A6,B2:C6,B7:C8,TRUE)				#NUM!				3400.16400895377
CRECIMIENTO usando solo la columna B
=GROWTH(A2:A6,B2:B6,B7:B8,VERDADERO)				472.432432563203				472,432432563203
=GROWTH(A2:A6,B2:B6,B7:B8,TRUE)				3400.16400895377				3400.16400895377
Los valores ajustados de los resultados de LOGEST en Excel 2003 y versiones posteriores de Excel
Usando las columnas B, C		Uso de Col B
=EXP(LN(K24)*1 + LN(J24)*B7 + LN(I24)*C7)		=EXP(LN(J31)*1 + LN(I31)*B7)
=EXP(LN(K24)*1 + LN(J24)*B8 + LN(I24)*C8)		=EXP(LN(J31)*1 + LN(I31)*B8)
LOGEST usando las columnas B, C:				Valores para Excel 2002 y para versiones anteriores de Excel:				Valores para Excel 2003 y para versiones posteriores de Excel:
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		1	1,9307233720034	1,26724101129183
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0	0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		0.986842105263158	0.209426954145848	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		225	3	#N/A
=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)	=LOGEST(A2:A6,B2:C6,TRUE,TRUE)		#NUM!	#NUM!	#NUM!		9.86842105263158	0.131578947368421	#N/A
LOGEST solamente con la columna B
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,VERDADERO,VERDADERO)			1.9307233720034	1,26724101129183			1,9307233720034	1,26724101129183
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.0438596491228071	0.206652964726136			0.043859649122807	0.206652964726136
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			0.986842105263158	0.209426954145848			0.986842105263158	0.209426954145848
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			224.999999999999	3			225	3
=LOGEST(A2:A6,B2:B6,TRUE,TRUE)	=LOGEST(A2:A6,B2:B6,TRUE,TRUE)			9.86842105263158	0.131578947368421			9.86842105263158	0.131578947368421

Nota:

Después de pegar esta tabla en la nueva hoja de cálculo de Excel, seleccione el botón Opciones de pegado y, a continuación, seleccione Coincidir formato de destino. Con el intervalo pegado aún seleccionado, use uno de los procedimientos siguientes, según corresponda para la versión de Excel que está ejecutando:

• En Microsoft Office Excel 2007, seleccione la pestaña Inicio , seleccione Formato en el grupo Celdas y, a continuación, seleccione AutoAjustar ancho de columna.
• En Excel 2003, seleccione Columna en el menú Formato y, a continuación, seleccione Selección de autoajuste.

Los datos de GROWTH se encuentran en las celdas A1:C8. (Las entradas de las celdas D2:D6 no forman parte de los datos, pero se usan para la ilustración siguiente). Los resultados de GROWTH para dos modelos diferentes para las versiones anteriores de Excel y para versiones posteriores de Excel se presentan en las celdas E10:E16 e I10:116, respectivamente. Los resultados de las celdas A10:A16 corresponden a la versión de Excel que usa. Por ahora, céntrese en los resultados de Excel 2003 y para versiones posteriores de Excel cuando investigue cómo GROWTH llama a LOGEST y cómo GROWTH usa los resultados LOGEST.

GROWTH y LOGEST se pueden ver como interactuando en los pasos siguientes:

1. Llamas a GROWTH(known_y, known_x, new_x, constante)
2. GROWTH llama a LOGEST(y_conocidos, x_conocidos, constante, VERDADERO)
3. Se obtienen coeficientes de regresión de esta llamada a LOGEST. Estos coeficientes aparecen en la primera fila de la tabla de salida de LOGEST.
4. Para cada fila de new_x, el valor y previsto se calcula en función de estos coeficientes LOGEST y los valores del new_x de esa fila.
5. El valor calculado del paso 4 se devuelve en la celda adecuada para la salida GROWTH que corresponde a esa fila de new_x.

Si GROWTH va a devolver los resultados adecuados, LOGEST debe generar los resultados adecuados en el paso 3. Dado que la evaluación de LOGEST en el paso 3 requiere una llamada a LINEST, es esencial que LINEST se comporte bien. Los problemas con LINEST en versiones de Excel anteriores a Excel 2003 proceden de columnas predictoras colineales. (Hay otros problemas con LINEST y LOGEST en las versiones anteriores de Excel que se producen cuando el último argumento de GROWTH se establece en FALSE. Sin embargo, esos problemas no afectan a los resultados del CRECIMIENTO y no se tratan aquí).

Las columnas de predicción (known_x's) son colineales si al menos una columna, c, se puede expresar como suma de múltiplos de otras columnas, c1, c2 y otras más. La columna c se denomina con frecuencia redundante porque la información que contiene se puede construir a partir de las columnas c1, c2 y otras columnas. El principio fundamental en la existencia de colinealidad es que los resultados no deben verse afectados por la inclusión o eliminación de una columna redundante en los datos originales. Dado que LINEST en versiones de Excel anteriores a la 2003 no buscaba colinealidad, este principio se violaba fácilmente. Las columnas de predicción son casi collineadas si al menos una columna, c, se puede expresar como casi igual a una suma de múltiplas de otras, c1, c2 y otras columnas. En este caso, "casi igual" significa una pequeña suma de desviaciones cuadradas de las entradas de c respecto a las entradas correspondientes en la suma ponderada de c1, c2 y columnas adicionales. "Muy pequeño" podría ser menor que 10^(-12), por ejemplo.

El primer modelo, en las filas de 10 a 12, usa las columnas B y C como predictores y solicitudes de Excel para modelar la constante (último argumento establecido en TRUE). A continuación, Excel inserta eficazmente una columna de predicción adicional que se parece a las celdas D2:D6. Es fácil observar que las entradas de la columna C de las filas 2 a 6 son exactamente iguales a la suma de las entradas correspondientes en las columnas B y D. Por lo tanto, hay colinealidad presente porque la columna C es una suma de múltiplos de los siguientes elementos:

• Columna B
• La columna adicional de 1s de Excel que se inserta porque el tercer argumento de LOGEST fue omitido o es TRUE (el caso "normal")

Esto provoca problemas numéricos que las versiones de Excel anteriores a Excel 2003 no pueden calcular los resultados. Por lo tanto, la tabla de salida GROWTH se rellena con #NUM!.

El segundo modelo, en las filas de 14 a 16, es uno que cualquier versión de Excel puede controlar correctamente. No hay ninguna colinearidad y el usuario vuelve a solicitar a Excel que modele la constante. Este modelo se incluye aquí por los siguientes motivos:

• En primer lugar, es más típico de los casos prácticos: que no hay colinealidad presente. Estos casos se manejan suficientemente en cada versión de Excel. Debe ser alentador saber que no es probable que ocurran problemas numéricos en el caso más común en la práctica si tiene una versión anterior de Excel.
• En segundo lugar, este ejemplo se usa para comparar el comportamiento de Excel 2003 y de versiones posteriores de Excel en los dos modelos. La mayoría de los paquetes estadísticos principales analizan la collinearidad, quitan una columna que es una suma de múltiplos de otros del modelo y avisan al usuario con un mensaje como "la columna C depende linealmente de otras columnas de predicción y se ha quitado del análisis".

En Excel 2003 y en versiones posteriores de Excel, este mensaje se transmite no en una alerta o en una cadena de texto, sino en la tabla de salida LOGEST. GROWTH no tiene ningún mecanismo para entregar este mensaje al usuario. En la tabla de salida LOGEST, un coeficiente de regresión que es uno y cuyo error estándar es cero, corresponde a un coeficiente de una columna que se ha quitado del modelo. Las tablas de salida LOGEST se incluyen en las filas 23 a 35 correspondientes a la salida GROWTH de las filas 10 a 16. Las entradas de las celdas I24:I25 muestran una columna de predicción redundante eliminada. En este caso, LOGEST eligió quitar la columna C (coeficientes en las celdas I24, J24, K24 corresponden a las columnas C, B y la columna constante de Excel, respectivamente). Cuando hay una colinealidad presente, se puede quitar cualquiera de las columnas implicadas y la opción es arbitraria.

En el segundo modelo entre las filas 30 y 35, no hay colinealidad ni columna eliminada. Puede ver que los valores y previstos son los mismos en ambos modelos. Este problema se produce porque quitar una columna redundante que es una suma de múltiplos de otros no reduce la bondad del ajuste del modelo resultante. Estas columnas se quitan precisamente porque no representan ningún valor agregado al intentar encontrar el mejor ajuste por mínimos cuadrados. Además, si examina la salida LOGEST en las celdas I23:K35 en Excel 2003 y en versiones posteriores de Excel, observe que las últimas tres filas de las tablas de salida son las mismas. Además, las entradas de las celdas I31:J32 y las celdas J24:K25 coinciden. Muestra que los mismos resultados se obtienen cuando se incluye la columna C en el modelo, pero se ha encontrado que son redundantes (salida en las celdas I24:K28) como cuando se eliminó la columna C antes de que se ejecutara LOGEST (salida en las celdas I31:J35). Esto satisface el principio fundamental de la colinealidad.

En las celdas A18:C21, Microsoft usa datos de Excel 2003 y versiones posteriores de Excel para ilustrar cómo GROWTH toma el resultado de LOGEST y calcula los valores previstos de y pertinentes. Al examinar las fórmulas de las celdas A20:A21 y las celdas C20:C21, puede ver cómo se combinan los coeficientes LOGEST con los datos de new_x en las celdas B7:C8 para cada uno de los dos modelos (usando las columnas B, C como predictores; usando solo la columna B como predictor).

Collinearity se identifica en LOGEST en Excel 2003 y en versiones posteriores de Excel porque LOGEST llama a LINEST. LINEST usa un enfoque diferente para resolver los coeficientes de regresión. Este enfoque es descomposición QR. El artículo LINEST contiene un tutorial del algoritmo de descomposición QR para un ejemplo pequeño.

Resumen de los resultados en versiones anteriores de Excel

Los resultados de GROWTH se ven afectados negativamente en las versiones de Excel anteriores a Excel 2003 debido a resultados inexactos en LOGEST que, a su vez, se derivan de resultados inexactos en LINEST.

LINEST se calculó mediante un enfoque que no prestaba atención a los problemas de colinealidad. La existencia de colinealidad provocó errores de redondeo, errores estándar inadecuados de coeficientes de regresión y grados de libertad inadecuados. A veces, los problemas de redondeo son tan graves como para que LINEST llenara su tabla de salida con #NUM!. Si, como en la mayoría de los casos en la práctica, puede estar seguro de que no existen columnas predictoras colineales (o casi colineales), LINEST generalmente proporcionaría resultados aceptables. Por lo tanto, los usuarios de GROWTH se pueden tranquilizar de forma similar si pueden ver la ausencia de columnas de predictores colineales (o casi colineales).

Resumen de los resultados en Excel 2003 y en versiones posteriores de Excel

Las mejoras en LINEST incluyen cambiar al método de descomposición QR para determinar los coeficientes de regresión. La descomposición QR tiene las siguientes ventajas:

• Mejor estabilidad numérica (por lo general, errores de redondeo más pequeños)
• Análisis de problemas de colinealidad

Todos los problemas con versiones de Excel anteriores a Excel 2003 que se ilustran en este artículo se han corregido para Excel 2003 y para versiones posteriores de Excel. Estas mejoras en LINEST se traducen en mejoras en LOGEST y GROWTH.

Conclusiones

Se ha mejorado el rendimiento de GROWTH porque LINEST se ha mejorado considerablemente para Excel 2003 y para versiones posteriores de Excel. Las mejoras en LINEST también afectan a LOGEST, porque GROWTH llama a LOGEST. Los usuarios de versiones anteriores de Excel deben comprobar que las columnas de predicción no son collineadas antes de usar GROWTH.

Gran parte del material presentado en este artículo y en el artículo LINEST puede parecer al principio alarmar a los usuarios de versiones de Excel anteriores a Excel 2003. Sin embargo, debe tenerse en cuenta que la colinearidad es un problema solo en un pequeño porcentaje de casos. Las versiones anteriores de Excel proporcionan resultados GROWTH aceptables cuando no hay colinealidad.

Afortunadamente, las mejoras en LINEST también afectan a la herramienta de regresión lineal de Analysis ToolPak (esta herramienta llama a LINEST) y otras dos funciones de Excel relacionadas: LOGEST y TREND.