Tengo entre dos y seis fechas para eventos del pasado y, en función de la distribución promedio de cada uno a otro, necesito predecir cuándo ocurrirá el próximo evento.
De la captura de pantalla, básicamente quiero tomar el promedio de ( C4-D4), ( D4-E4), ( E4-F4) y omitir ( F4-G4), ya que está en blanco. Luego quiero agregar el número promedio de días al valor más reciente ( C4) para derivar ( A4), la próxima aparición prevista.
Quiero tener una fórmula B4que genere el promedio de días y omita el cálculo si una o ambas celdas están en blanco.
Lo intenté Max-Min/CountIf:
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
Pero cada vez aparece un número demasiado bajo, en el caso de fila 5, 159cuando debería ser 214, y fila 6debería ser 337. Cuando intenté utilizar AVERAGEtodas las fechas, no obtuve días, obtuve la fecha promedio.
Respuesta1
Tu fórmula debe restar 1 del denominador, porque lo que quieres contar son las diferencias, no los números reales.
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Si desea omitir la columna de ayuda:
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
También puedes utilizar el FORCAST:
=FORECAST(0,C4:G4,ROW($1:$5))
O incluso INTERCEPTAR:
=INTERCEPT(C4:G4,ROW($1:$5))
Estos dos utilizan la tendencia y no el promedio, por lo que obtendrán un valor diferente si las diferencias varían mucho.
Respuesta2
La respuesta de Scott Craner cubre la tarea planteada en la pregunta, pronosticando la próxima fecha en función del intervalo promedio. También sugiere una alternativa de utilizar una tendencia. Ese podría ser un enfoque mejor o peor, dependiendo de lo que signifiquen los datos. Esta respuesta se centrará en la diferencia para que los lectores puedan aplicar el tipo de solución adecuado.
La pregunta y la respuesta de Scott se utilizan (Max - Min)/(interval count)para encontrar el intervalo promedio. Está bien, pero para ilustrar el efecto, calcularé los intervalos y trabajaré con ellos, porque eso hace que sea fácil de ver en un gráfico. Usaré los datos de la fila 6 porque esa es la primera fila con cinco valores. Entonces esos datos se ven así.
El intervalo estimado entre el quinto y sexto evento, en la columna C, dará la fecha del evento 6. Si traza los intervalos, se verá así:
El intervalo promedio se ve así:
El promedio es el mismo en cualquier punto, en este caso es solo un valor 225.5. Si agrega eso a la última fecha, obtendrá una próxima aparición proyectada para el 7/13/2019.
Aquí está el problema. ¿Está grabando un proceso que sigue un patrón o algo que es casi aleatorio? Los eventos aleatorios no siguen un patrón predecible de subir y bajar con cada evento sucesivo, como los dientes de sierra. Incluyen series de observaciones en la misma dirección. Existen pruebas estadísticas sobre la probabilidad de que se produzca un patrón si los datos son realmente aleatorios, pero el cerebro de las personas está programado para ver patrones, por lo que a menudo se supone que los patrones en los datos son significativos. Los patrones de datos son algo así como las manchas de tinta de Rorschach: las personas proyectan en ellos significados que en realidad pueden no existir.
Si está investigando patrones, puede observar los datos y decidir si probar lo que parece un patrón. Pero si espera que los datos sean aleatorios o desea una estimación imparcial del próximo evento, no querrá comenzar con la suposición de un patrón. Si utilizas ciegamente una línea de tendencia, eso es lo que estás haciendo. Trabajar con el promedio en esta situación, como se propone en la pregunta, es el camino a seguir.
Tomemos este ejemplo. Al observar los datos, su cerebro intenta convencerlo de que los datos siguen una curva. En general, parece estar aumentando, aunque la curva parece estabilizarse. Entonces, en ausencia de cualquier otra información, ¿cuál sería la mejor manera de ajustarse al patrón? Esto es lo que sucede si proyecta el siguiente intervalo basándose en ajustes de orden superior sucesivamente.
Un ajuste de primer orden es una línea recta, lo que se obtiene con una tendencia simple:
Éste percibe que los valores aumentan en general y estima que el siguiente intervalo será 259.5. Un ajuste de segundo orden se ve así:
Eso ve el último intervalo como un punto alto y estima que el siguiente intervalo será más bajo 232. Un ajuste de tercer orden es el más alto que se puede alcanzar con cuatro intervalos y se ve así:
Una línea de tercer orden encajará perfectamente con cuatro puntos. Encuentra varios puntos de inflexión y termina subiendo después del último punto, estimando 253el siguiente intervalo.
Entonces, dependiendo de qué tipo de línea crea que representa mejor el proceso subyacente que genera el "patrón", el siguiente evento podría variar de 7/13/2019a 8/16/2019.
Extender cualquiera de esas "tendencias" para pronosticar el séptimo evento daría resultados aún más variables. Estos resultados son con cinco puntos de datos. Incluso si cree que los datos siguen un patrón, no son muchos datos para realizar estimaciones. Con incluso menos puntos de datos, como tienen muchas de las filas de datos, cualquier forma de estimación es arriesgada. Si tiene motivos para creer que los datos siguen un patrón, y sus datos generalmente se ajustan a ese patrón, utilizar una línea de tendencia con la forma adecuada (es decir, un tipo de fórmula) probablemente le dará la "mejor" estimación, pero en ese sentido En este caso, utilice un intervalo de confianza en lugar de una estimación puntual, o además de ella. Eso al menos te dará una idea de lo lejos que puedes estar.
Tenga en cuenta que cualquier forma de línea de tendencia supone que existe un patrón subyacente y ese patrón se refleja en los datos. Si realmente existe un patrón, unos pocos datos generalmente no son suficientes para estimarlo. Pero puede que no haya ningún patrón en absoluto, sino sólo una secuencia casual de observaciones. En ese caso, la estimación basada en el patrón puede desviarlo en una dirección arbitraria, introduciendo un error sustancial en su proyección.
Pero también existe otra posibilidad. Muchas cosas siguen un ciclo. Las observaciones pueden en realidad ser parte de un patrón, pero sólo un pequeño fragmento de un patrón. En este ejemplo, esas observaciones podrían ser parte de un ciclo de décadas que parece una onda sinusoidal. Esas observaciones podrían reflejar con precisión el acercamiento a la cima del ciclo, por lo que el patrón posterior podría ir hacia abajo en lugar de hacia arriba (similar al ajuste de segundo orden, arriba). Entonces, incluso si el patrón es real, es peligroso extrapolar fuera del rango de los datos sin saber algo sobre el proceso subyacente detrás del patrón.