Funciones estadísticas de Excel: GROWTH

Resumen

En este artículo se describe la función GROWTH en Microsoft Office Excel 2003 y en versiones posteriores de 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 los resultados de GROWTH en versiones anteriores de Excel. GROWTH se evalúa mediante una llamada a la función relacionada, LINEST. Se resumen amplios cambios en LINEST para Excel 2003 y para versiones posteriores de Excel, y se indican 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, constant) se usa para realizar un análisis de regresión en el que se ajusta una curva exponencial. Se usa un criterio de mínimos cuadrados y GROWTH intenta encontrar el mejor ajuste bajo ese criterio. Known_y representan datos en la "variable dependiente" y known_x representan datos en 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 haya variables de predictor p, GROWTH básicamente llama a LOGEST. LOGEST se ajusta a una ecuación del formulario:

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

Se determinan los valores de los coeficientes, b, m1, m2, ..., mp que proporcionan 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 estableciendo básicamente 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, suponga que los datos están organizados en columnas de modo que known_y es una columna de datos y y known_x es una o varias columnas de datos x. Por supuesto, las dimensiones (longitudes) de cada una de estas columnas deben ser iguales. New_x también se supone que 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 este único caso (que se usa con más frecuencia).

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

En este artículo se usan ejemplos para mostrar cómo se relaciona GROWTH 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 de forma eficaz a LOGEST, ejecuta LOGEST, usa coeficientes de regresión en la salida de LOGEST en su cálculo de valores y predichos que están asociados a cada fila de new_x y le presenta esta columna de valores y predichos. Por lo tanto, debe conocer los problemas en la ejecución de LOGEST. Cuando se llama a LOGEST, a su vez llama eficazmente a LINEST. Aunque el código de GROWTH y LOGEST no se ha reescrito para Excel 2003 y para versiones posteriores de Excel, se han realizado grandes cambios (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, haga clic en el número de artículo siguiente para verlo 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, tal como se ha revisado para Excel 2003.

En el artículo siguiente sobre LOGEST se explica cómo LOGEST interactúa con LINEST. Estos detalles se omiten aquí.

Para obtener más información, haga clic en el número de artículo siguiente para verlo en Microsoft Knowledge Base:

828528 funciones estadísticas de Excel: LOGEST

Dado que el foco de este artículo se centra en los problemas numéricos en las 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 rangos de celdas que tengan dimensiones relacionadas. Si known_y es una columna por filas m, known_x es c columnas por m filas donde c es mayor o igual que uno. C es el número de variables de predictor; m es el número de puntos de datos. New_x deben ser columnas c por filas r, donde son mayores o iguales que una. (Las relaciones similares de las dimensiones deben contenerse 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 0 o 1 que Excel interpreta como FALSE o TRUE, respectivamente). Los tres últimos argumentos de GROWTH son opcionales; vea el archivo de ayuda GROWTH para ver las opciones de omitir el segundo argumento, el tercer argumento o ambos; la omisión del 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 predictor, el segundo argumento de este ejemplo contiene varias columnas. En este ejemplo, hay 100 asuntos, un valor de variable dependiente (known_y) para cada sujeto y cinco valores de variable dependientes (known_x) para cada sujeto. Hay ocho temas hipotéticos adicionales en los que desea usar GROWTH para calcular los valores y predichos.

Ejemplo de uso

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

• Cómo interactúa GROWTH con LOGEST
• Problemas que se producen con GROWTH (o LOGEST y LINEST) debido a colinear known_x en versiones de Excel anteriores a Excel 2003

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, a continuación, pegue las entradas para que la tabla siguiente rellene las celdas A1:K35 en la hoja de cálculo.

Los datos de GROWTH se encuentran en las celdas A1:C8. (Las entradas en 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 ambas versiones anteriores de Excel y para versiones posteriores de Excel se presentan en las celdas E10:E16 e I10:116, respectivamente. Los resultados en las celdas A10:A16 se corresponden con la versión de Excel que está usando. Por ahora, céntrese en los resultados de Excel 2003 y en las versiones posteriores de Excel cuando investigue cómo GROWTH llama a LOGEST y cómo GROWTH usa los resultados de LOGEST.

GROWTH y LOGEST se pueden ver como interacción en los pasos siguientes:

1. Llama a GROWTH(known_y, known_x, new_x, constante)
2. GROWTH llama a LOGEST(known_y, known_x, constant, TRUE)
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 en esa fila.
5. El valor calculado del paso 4 se devuelve en la celda adecuada para la salida GROWTH que corresponde a la fila de esa new_x.

Si GROWTH devuelve 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 de predicción colinear. (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 analizan aquí).

Las columnas de predictor (known_x) son colinear si al menos una columna, c, se puede expresar como una suma de múltiplos de otras, c1, c2 y otras columnas. 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 colinearidad es que los resultados no deben verse afectados por si una columna redundante se incluye en los datos originales o se quita de los datos originales. Dado que LINEST en las versiones de Excel anteriores a Excel 2003 no buscaban la colinearidad, este principio se violó fácilmente. Las columnas de predictor son casi colinear si al menos una columna, c, se puede expresar como casi igual a una suma de múltiplos de otras, c1, c2 y otras columnas. En este caso, "casi igual" significa una pequeña suma de desviaciones cuadradas de entradas en c de las entradas correspondientes en la suma ponderada de c1, c2 y otras columnas. "Muy pequeño" podría ser menor que 10^(-12), por ejemplo.

El primer modelo, en las filas 10 a 12, usa las columnas B y C como predictores y solicita a Excel que modele la constante (último argumento establecido en TRUE). Excel inserta de forma eficaz una columna de predictor 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 colinearidad presente porque la columna C es una suma de múltiplos de los siguientes elementos:

• Columna B
• Columna adicional de Excel de 1s que se inserta porque se omitió el tercer argumento de LOGEST o TRUE (el caso "normal")

Esto provoca tales 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 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 colinearidad presente. Estos casos se controlan lo suficiente en todas las versiones de Excel. Debe ser tranquilizador saber que es probable que no se produzcan problemas numéricos en el caso práctico más común 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 colinearidad, quitan una columna que es una suma de múltiplos de otros del modelo y alertan al usuario con un mensaje como "la columna C depende linealmente de otras columnas de predictor 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 un mensaje de este tipo 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 para una columna que se ha quitado del modelo. Las tablas de salida de 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 predictor redundante eliminada. En este caso, LOGEST eligió quitar la columna C (los coeficientes de las celdas I24, J24, K24 corresponden a las columnas C, B y columna constante de Excel, respectivamente). Cuando hay colinearidad presente, se puede quitar cualquiera de las columnas implicadas y la elección es arbitraria.

En el segundo modelo de las filas 30 a 35, no hay ninguna colinearidad y no se quita ninguna columna. Puede ver que los valores y predichos son los mismos en ambos modelos. Este problema se produce porque la eliminación de 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 mínimo de cuadrados. Además, si examina la salida de LOGEST en las celdas I23:K35 en Excel 2003 y en versiones posteriores de Excel, observará que las tres últimas filas de las tablas de salida son las mismas. Además, las entradas de las celdas I31:J32 y las celdas J24:K25 coinciden. Esto muestra que se obtienen los mismos resultados cuando se incluye la columna C en el modelo, pero se detecta que es redundante (salida en las celdas I24:K28) que cuando se eliminó la columna C antes de ejecutar LOGEST (salida en las celdas I31:J35). Esto satisface el principio fundamental en la existencia de la colinearidad.

En las celdas A18:C21, Microsoft usa datos de Excel 2003 y de versiones posteriores de Excel para ilustrar cómo GROWTH toma la salida de LOGEST y calcula los valores y previstos pertinentes. Al examinar las fórmulas de las celdas A20:A21 y 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 (mediante 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 pequeño ejemplo.

Resumen de 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 colinearidad. La existencia de colinearidad 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 suficientemente graves que LINEST llenó su tabla de salida con #NUM!. Si, como en la gran mayoría de los casos en la práctica, puede estar seguro de que no había columnas de predictor colinear (o casi colinear), LINEST generalmente proporcionaría resultados aceptables. Por lo tanto, los usuarios de GROWTH pueden estar igualmente seguros si pueden ver la ausencia de columnas de predictor colinear (o casi colinear).

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

Las mejoras en LINEST incluyen el cambio 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 (generalmente, errores de redondeo más pequeños)
• Análisis de problemas de colinearidad

Todos los problemas con las versiones de Excel anteriores a Excel 2003 que se muestran 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

El rendimiento de GROWTH se ha mejorado porque LINEST se ha mejorado considerablemente para Excel 2003 y para versiones posteriores de Excel. Las mejoras en LINEST también afectan a LOGEST, ya que GROWTH llama a LOGEST. Los usuarios de versiones anteriores de Excel deben comprobar que las columnas del predictor no colisionan antes de usar GROWTH.

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

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