3

У меня есть от двух до шести дат для событий в прошлом, и на основании среднего спреда от каждого к другому, мне нужно предсказать, когда произойдет следующее событие.

Образец скриншота

На снимке экрана я хочу взять среднее значение (C4-D4), (D4-E4), (E4-F4) и пропустить (F4-G4), поскольку оно пустое. Затем я хочу добавить среднее число дней к самому последнему значению (C4), чтобы получить (A4), предсказанное следующее вхождение.

Я хочу иметь одну формулу в B4 которая соответствует средним дням и пропускает вычисления, если одна или обе ячейки не заполнены.

Я попробовал Max-Min/CountIf:

=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")

Но каждый раз получается слишком малое число, в случае строки 5 - 159 когда оно должно быть 214 , а в строке 6 - 337 . Когда я пытался использовать AVERAGE разных дат, у меня не было дней, я получил среднюю дату.

2 ответа2

4

Ваша формула должна вычесть 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),"")

Вы также можете использовать ПРОГНОЗ:

=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 .

Расширение любой из этих "тенденций" для прогнозирования седьмого события даст вам еще более дико меняющиеся результаты. Эти результаты с пятью точками данных. Даже если вы считаете, что данные соответствуют шаблону, это не так много данных для оценки. С еще меньшим количеством точек данных, как и у многих строк данных, любая форма оценки рискованна. Если у вас есть основания полагать, что данные соответствуют шаблону, и ваши данные обычно соответствуют этому шаблону, используя линию тренда соответствующей формы (т. Е. Типа формулы), скорее всего, вы получите "наилучшую" оценку, но в этом В этом случае используйте доверительный интервал вместо точечной оценки или в дополнение к ней. Это, по крайней мере, даст вам представление о том, как далеко вы можете быть.

Помните, что любая форма линии тренда предполагает наличие базовой модели, и эта модель отражается в данных. Если на самом деле есть шаблон, несколько точек данных, как правило, недостаточно для его оценки. Но здесь может не быть никакой картины, просто случайная последовательность наблюдений. В этом случае оценка, основанная на шаблоне, может привести вас в произвольном направлении, что приведет к существенной ошибке в вашей проекции.

Но есть и другая возможность. Многие вещи следуют за циклом. Наблюдения могут на самом деле быть частью шаблона, но просто небольшим фрагментом шаблона. В этом примере эти наблюдения могут быть частью десятилетнего цикла, который выглядит как синусоида. Эти наблюдения могут точно отражать приближение к вершине цикла, поэтому последующий паттерн может быть направлен вниз, а не вверх (аналогично подгонке второго порядка выше). Поэтому, даже если шаблон является реальным, опасно экстраполировать за пределы диапазона данных, не зная ничего о базовом процессе, стоящем за шаблоном.

Всё ещё ищете ответ? Посмотрите другие вопросы с метками .