У меня есть от двух до шести дат событий в прошлом, и на основе среднего разброса от каждой из них мне нужно предсказать, когда произойдет следующее событие.
На скриншоте я по сути хочу взять среднее значение ( C4-D4),( D4-E4),( E4-F4) и пропустить ( F4-G4), так как оно пустое. Затем я хочу добавить среднее количество дней к самому последнему значению ( C4), чтобы получить ( A4), предсказанное следующее событие.
Мне нужна одна формула, B4которая вычисляет среднее количество дней и пропускает расчет, если одна или обе ячейки пустые.
Я пытался Max-Min/CountIf:
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
Но каждый раз получается слишком низкое число, в случае row 5, 159когда должно быть 214, а row 6должно быть 337. Когда я попытался использовать его AVERAGEпо датам, я не получил дни, я получил среднюю дату.
решение1
В вашей формуле следует вычесть 1 из знаменателя, поскольку вы хотите подсчитать именно разницу, а не сами числа.
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Если вы хотите пропустить вспомогательный столбец:
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Вы также можете использовать FORCAST:
=FORECAST(0,C4:G4,ROW($1:$5))
Или даже ПЕРЕХВАТ:
=INTERCEPT(C4:G4,ROW($1:$5))
Эти два показателя используют тенденцию, а не среднее значение, поэтому они дадут разные значения, если различия будут значительными.
решение2
Ответ Скотта Крейнера охватывает задачу, заданную в вопросе, прогнозируя следующую дату на основе среднего интервала. Он также предлагает альтернативу использования тренда. Это может быть как лучшим, так и худшим подходом, в зависимости от того, что означают данные. Этот ответ будет сосредоточен на разнице, чтобы читатели могли применить подходящий вид решения.
Вопрос и ответ Скотта используются (Max - Min)/(interval count)для поиска среднего интервала. Это нормально, но чтобы проиллюстрировать эффект, я вычислю интервалы и буду работать с ними, потому что это облегчает просмотр на графике. Я буду использовать данные строки 6, потому что это первая строка с пятью значениями. Так что эти данные выглядят так.
Предполагаемый интервал между пятым и шестым событиями в столбце C даст дату события 6. Если вы нанесете интервалы на график, они будут выглядеть следующим образом:
Средний интервал выглядит так:
Среднее значение одинаково в любой момент, это просто значение, в данном случае 225.5. Если вы добавите это к последней дате, вы получите прогнозируемое следующее событие на 7/13/2019.
Вот в чем проблема. Вы записываете процесс, который следует шаблону, или что-то близкое к случайному? Случайные события не следуют предсказуемому шаблону подъема и спада с каждым последующим событием, как зубья пилы. Они включают в себя серии наблюдений в одном направлении. Существуют статистические тесты на то, насколько вероятна закономерность, если данные на самом деле случайны, но мозг людей настроен на то, чтобы видеть закономерности, поэтому закономерности в данных часто считаются осмысленными. Закономерности данных чем-то похожи на чернильные пятна Роршаха, люди проецируют на них смысл, которого на самом деле может и не быть.
Если вы исследуете закономерности, вы можете посмотреть на данные и решить, стоит ли тестировать то, что выглядит как закономерность. Но если вы ожидаете, что данные будут случайными, или хотите получить беспристрастную оценку следующего события, вам не нужно начинать с предположения о закономерности. Если вы слепо используете линию тренда, то это то, что вы делаете. Работа со средним значением в этой ситуации, как предлагается в вопросе, — это путь.
Возьмем этот пример. Глядя на данные, ваш мозг пытается убедить вас, что данные следуют кривой. Кажется, что они в целом увеличиваются, хотя кривая, кажется, выравнивается. Так что при отсутствии какой-либо другой информации, какой был бы лучший способ подстроиться под шаблон? Вот что произойдет, если вы спроецируете следующий интервал на основе последовательно более высоких порядков соответствия.
Соответствие первого порядка — это прямая линия, которую вы получаете при простом тренде:
Это воспринимает значения как в целом возрастающие, и оценивает, что следующий интервал будет 259.5. Подгонка второго порядка выглядит так:
Это рассматривает последний интервал как высокую точку и оценивает, что следующий интервал будет ниже, 232. Третий порядок соответствия — это наивысший, который вы можете получить с четырьмя интервалами, и выглядит следующим образом:
Линия третьего порядка будет идеальной подгонкой для четырех точек. Она находит несколько точек перегиба и в итоге идет выше после последней точки, оценивая 253следующий интервал.
Таким образом, в зависимости от того, какой тип линии, по вашему мнению, лучше всего отражает базовый процесс, генерирующий «шаблон», следующее событие может находиться в диапазоне от 7/13/2019до 8/16/2019.
Расширение любой из этих «тенденций» для прогнозирования седьмого события даст вам еще более сильно различающиеся результаты. Эти результаты получены с пятью точками данных. Даже если вы считаете, что данные следуют шаблону, это не так много данных для оценки. С еще меньшим количеством точек данных, как во многих строках данных, любая форма оценки рискованна. Если у вас есть основания полагать, что данные следуют шаблону, и ваши данные в целом соответствуют этому шаблону, использование линии тренда соответствующей формы (т. е. типа формулы) скорее всего даст вам «наилучшую» оценку, но в этом случае используйте доверительный интервал вместо точечной оценки или в дополнение к ней. Это, по крайней мере, даст вам представление о том, насколько вы можете быть далеки от истины.
Помните, что любая форма линии тренда предполагает, что есть базовая закономерность, и эта закономерность отражается в данных. Если закономерность действительно есть, нескольких точек данных обычно недостаточно, чтобы оценить ее. Но закономерности может и не быть вовсе, а быть просто случайной последовательностью наблюдений. В этом случае оценка на основе закономерности может увести вас в произвольном направлении, внеся существенную ошибку в ваш прогноз.
Но есть и другая возможность. Многое следует циклу. Наблюдения могут быть частью модели, но лишь небольшим фрагментом модели. В этом примере эти наблюдения могут быть частью цикла длиной в десятилетия, который выглядит как синусоида. Эти наблюдения могут точно отражать приближение к вершине цикла, поэтому последующая модель может быть направлена вниз, а не вверх (аналогично подгонке второго порядка выше). Поэтому даже если модель реальна, опасно экстраполировать за пределы диапазона данных, не зная ничего о базовом процессе, лежащем в основе модели.