我有過去兩到六個事件的日期,並且根據每個日期之間的平均分佈,我需要預測下一個事件何時發生。
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
,而 row6
應該是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),"")
您也可以使用預測:
=FORECAST(0,C4:G4,ROW($1:$5))
甚至攔截:
=INTERCEPT(C4:G4,ROW($1:$5))
這兩者使用趨勢而不是平均值,因此如果差異很大,他們會得出不同的值。
答案2
Scott Craner 的答案涵蓋了問題中提出的任務,根據平均間隔預測下一個日期。它還提出了使用趨勢的替代方案。這可能是更好或更差的方法,具體取決於數據的含義。這個答案將重點放在差異,以便讀者可以應用適當的解決方案。
這個問題和斯科特的答案用於(Max - Min)/(interval count)
找到平均間隔。很好,但為了說明效果,我將計算間隔並使用這些間隔,因為這樣可以輕鬆地在圖表中看到。我將使用第 6 行數據,因為這是具有五個值的第一行。所以數據看起來像這樣。
C 列中第五個和第六個事件之間的估計間隔將給出事件 6 的日期。
平均間隔如下所示:
平均值在任何點都是相同的,在本例中它只是一個值225.5
。如果將其添加到最後一個日期,您將獲得預計的下一次出現時間7/13/2019
。
問題就在這裡。您記錄的是遵循某種模式的過程,還是接近隨機的過程?隨機事件不會遵循每個連續事件的可預測的上下波動模式,就像鋸齒一樣。它們包括同一方向的一系列觀察。如果數據實際上是隨機的,則可以透過統計測試來檢驗模式的可能性有多大,但人們的大腦天生就能看到模式,因此數據中的模式通常被認為是有意義的。資料模式有點像羅夏墨跡,人們將可能實際上不存在的含義投射到它們上。
如果您正在研究模式,您可以查看數據並決定是否測試看起來像模式的內容。但是,如果您期望資料是隨機的,或者想要對下一個事件進行無偏估計,則您不想從模式假設開始。如果你盲目地使用趨勢線,那就是你正在做的事情。正如問題中所提議的,在這種情況下使用平均值是可行的方法。
以這個例子為例。查看數據時,您的大腦會試圖讓您相信數據遵循曲線。儘管曲線似乎趨於平穩,但它似乎總體上在增加。那麼,在沒有任何其他資訊的情況下,調整該模式的最佳方法是什麼?如果您根據連續的高階擬合來預測下一個區間,則會發生以下情況。
一階擬合是一條直線,透過簡單的趨勢可以得到:
它認為值普遍增加,並估計下一個間隔將為259.5
。二階擬合如下圖所示:
它將最後一個間隔視為高點,並估計下一個間隔將更低,232
。三階擬合是四個間隔中可以達到的最高值,如下所示:
第三階線將完美適合四個點。它找到了幾個拐點,並最終在最後一個點之後走高,估計253
下一個區間。
因此,根據您認為最能代表產生「模式」的底層過程的線類型,下一個事件的範圍可能是從7/13/2019
到8/16/2019
。
擴展任何這些「趨勢」來預測第七個事件都會為你帶來更加不同的結果。這些結果包含五個數據點。即使您相信數據確實遵循某種模式,但可供估計的數據並不多。由於數據點更少,正如許多數據行所具有的那樣,任何形式的估計都是有風險的。如果您有理由相信數據確實遵循某種模式,並且您的數據通常符合該模式,則使用適當形狀(即公式類型)的趨勢線,可能會為您提供“最佳”估計,但在這種情況下在這種情況下,請使用信賴區間而不是點估計,或除了點估計之外還使用信賴區間。這至少會讓你知道你可能離目標有多遠。
請記住,任何形式的趨勢線都假設存在潛在模式,並且該模式正在數據中反映出來。如果確實存在某種模式,那麼幾個數據點通常不足以估計它。但可能根本沒有任何模式,只是觀察的偶然序列。在這種情況下,基於模式的估計可能會讓您偏離任意方向,從而在您的預測中引入重大錯誤。
但還有另一種可能性。很多事情都有一個循環。觀察結果實際上可能是模式的一部分,但只是模式的一小部分。在這個例子中,這些觀察結果可能是長達數十年的周期的一部分,看起來像正弦波。這些觀察結果可能準確地反映了接近週期頂峰的情況,因此後續模式可能會向下而不是向上(類似於上面的二階擬合)。因此,即使該模式是真實的,在不了解該模式背後的基本過程的情況下,在數據範圍之外進行推斷也是危險的。