Ich verfüge über zwei bis sechs Termine für Ereignisse in der Vergangenheit und muss auf Grundlage der durchschnittlichen Abweichung zwischen den einzelnen Terminen vorhersagen, wann das nächste Ereignis stattfinden wird.
C4-D4Aus dem Screenshot möchte ich im Wesentlichen den Durchschnitt von ( ),( D4-E4),( ) nehmen E4-F4und ( ) überspringen, F4-G4da es leer ist. Dann möchte ich die durchschnittliche Anzahl von Tagen zum aktuellsten Wert ( ) hinzufügen, um ( ), das vorhergesagte nächste Vorkommen, C4abzuleiten .A4
Ich möchte eine Formel haben, B4die die durchschnittliche Anzahl an Tagen ermittelt und die Berechnung überspringt, wenn eine oder beide Zellen leer sind.
Ich habe es versucht Max-Min/CountIf:
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/COUNTA(C4:G4),"")
Aber es wird jedes Mal eine zu niedrige Zahl zurückgegeben, und zwar im Fall von Zeile 5, 159obwohl es sein sollte 214und Zeile 6sein sollte 337. Als ich versuchte, AVERAGEdie Daten übergreifend zu verwenden, erhielt ich nicht Tage, sondern das Durchschnittsdatum.
Antwort1
Ihre Formel sollte 1 vom Nenner abziehen, da Sie die Differenzen zählen möchten, nicht die tatsächlichen Zahlen.
=IFERROR((MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Wenn Sie die Hilfsspalte überspringen möchten:
=IFERROR(MAX(C4:G4) + (MAX(C4:G4)-MIN(C4:G4))/(COUNTA(C4:G4)-1),"")
Sie können auch die Prognose nutzen:
=FORECAST(0,C4:G4,ROW($1:$5))
Oder sogar ABFANGEN:
=INTERCEPT(C4:G4,ROW($1:$5))
Diese beiden verwenden den Trend und nicht den Durchschnitt, sodass sie bei großen Abweichungen zu einem anderen Wert gelangen.
Antwort2
Die Antwort von Scott Craner deckt die in der Frage gestellte Aufgabe ab, das nächste Datum auf Grundlage des Durchschnittsintervalls vorherzusagen. Sie schlägt auch die Alternative vor, einen Trend zu verwenden. Dies könnte je nach Bedeutung der Daten ein besserer oder schlechterer Ansatz sein. Diese Antwort konzentriert sich auf den Unterschied, damit die Leser die entsprechende Art von Lösung anwenden können.
Die Frage und Scotts Antwort dienen (Max - Min)/(interval count)dazu, das Durchschnittsintervall zu ermitteln. Das ist in Ordnung, aber um den Effekt zu veranschaulichen, werde ich die Intervalle berechnen und mit diesen arbeiten, da dies die Anzeige in einem Diagramm erleichtert. Ich werde die Daten aus Zeile 6 verwenden, da dies die erste Zeile mit fünf Werten ist. Diese Daten sehen also so aus.
Der geschätzte Abstand zwischen dem fünften und sechsten Ereignis in Spalte C ergibt das Datum von Ereignis 6. Wenn Sie die Abstände aufzeichnen, sehen sie folgendermaßen aus:
Das Durchschnittsintervall sieht folgendermaßen aus:
Der Durchschnitt ist zu jedem Zeitpunkt derselbe, es ist nur ein Wert, in diesem Fall 225.5. Wenn Sie diesen zum letzten Datum addieren, erhalten Sie ein prognostiziertes nächstes Vorkommen am 7/13/2019.
Hier liegt das Problem. Zeichnen Sie einen Prozess auf, der einem Muster folgt, oder etwas, das nahezu zufällig ist? Zufällige Ereignisse folgen keinem vorhersehbaren Muster, bei dem es mit jedem aufeinanderfolgenden Ereignis auf und ab geht, wie Sägezähne. Sie umfassen Beobachtungsreihen in die gleiche Richtung. Es gibt statistische Tests, um zu bestimmen, wie wahrscheinlich ein Muster ist, wenn die Daten tatsächlich zufällig sind, aber das menschliche Gehirn ist darauf programmiert, Muster zu erkennen, daher wird oft angenommen, dass Muster in Daten bedeutsam sind. Datenmuster sind ein bisschen wie Rorschach-Tintenkleckse, Menschen projizieren Bedeutungen darauf, die möglicherweise gar nicht existieren.
Wenn Sie Muster untersuchen, können Sie sich Daten ansehen und entscheiden, ob Sie etwas testen möchten, das wie ein Muster aussieht. Wenn Sie jedoch davon ausgehen, dass die Daten zufällig sind, oder eine unvoreingenommene Schätzung des nächsten Ereignisses wünschen, sollten Sie nicht mit der Annahme eines Musters beginnen. Wenn Sie blind einer Trendlinie folgen, ist das genau das, was Sie tun. In dieser Situation mit dem Durchschnitt zu arbeiten, wie in der Frage vorgeschlagen, ist der richtige Weg.
Nehmen wir dieses Beispiel. Wenn Sie sich die Daten ansehen, versucht Ihr Gehirn Sie davon zu überzeugen, dass die Daten einer Kurve folgen. Sie scheinen im Allgemeinen zuzunehmen, obwohl die Kurve abzuflachen scheint. Was wäre also in Ermangelung anderer Informationen die beste Möglichkeit, das Muster anzupassen? Folgendes passiert, wenn Sie das nächste Intervall basierend auf sukzessiven Anpassungen höherer Ordnung projizieren.
Eine Anpassung erster Ordnung ist eine Gerade, die Sie bei einem einfachen Trend erhalten:
Dadurch werden die Werte im Allgemeinen als ansteigend wahrgenommen und das nächste Intervall wird als geschätzt 259.5. Eine Anpassung zweiter Ordnung sieht folgendermaßen aus:
Dabei wird das letzte Intervall als höchster Punkt angesehen und das nächste Intervall wird niedriger geschätzt 232. Eine Anpassung dritter Ordnung ist das höchste, was Sie mit vier Intervallen erreichen können und sieht folgendermaßen aus:
Eine Linie dritter Ordnung passt perfekt zu vier Punkten. Sie findet mehrere Wendepunkte und steigt nach dem letzten Punkt an, was eine Schätzung 253für das nächste Intervall darstellt.
Je nachdem, welche Art von Linie Ihrer Meinung nach den zugrunde liegenden Prozess, der das „Muster“ erzeugt, am besten darstellt, könnte das nächste Ereignis zwischen und 7/13/2019liegen 8/16/2019.
Wenn Sie einen dieser „Trends“ zur Vorhersage des siebten Ereignisses ausweiten würden, würden Sie noch stärker abweichende Ergebnisse erhalten. Diese Ergebnisse basieren auf fünf Datenpunkten. Selbst wenn Sie glauben, dass die Daten einem Muster folgen, sind das nicht viele Daten, auf deren Grundlage Sie schätzen können. Bei noch weniger Datenpunkten, wie sie viele der Datenzeilen aufweisen, ist jede Form der Schätzung riskant. Wenn Sie Grund zu der Annahme haben, dass die Daten einem Muster folgen, und Ihre Daten im Allgemeinen diesem Muster entsprechen, erhalten Sie wahrscheinlich die „beste“ Schätzung, wenn Sie eine Trendlinie der entsprechenden Form (d. h. Art der Formel) verwenden. Verwenden Sie in diesem Fall jedoch ein Konfidenzintervall anstelle einer Punktschätzung oder zusätzlich dazu. Dadurch erhalten Sie zumindest eine Vorstellung davon, wie weit Sie möglicherweise danebenliegen.
Bedenken Sie, dass jede Form von Trendlinie davon ausgeht, dass ein zugrunde liegendes Muster vorliegt und dass sich dieses Muster in den Daten widerspiegelt. Wenn tatsächlich ein Muster vorliegt, reichen einige Datenpunkte im Allgemeinen nicht aus, um es abzuschätzen. Es kann aber auch sein, dass überhaupt kein Muster vorliegt, sondern nur eine zufällige Abfolge von Beobachtungen. In diesem Fall kann eine Schätzung auf Grundlage des Musters dazu führen, dass Sie in eine willkürliche Richtung abdriften, was zu erheblichen Fehlern in Ihrer Prognose führt.
Aber es gibt auch eine andere Möglichkeit. Viele Dinge folgen einem Zyklus. Die Beobachtungen können tatsächlich Teil eines Musters sein, aber nur ein kleiner Ausschnitt eines Musters. In diesem Beispiel könnten diese Beobachtungen Teil eines Jahrzehnte dauernden Zyklus sein, der wie eine Sinuswelle aussieht. Diese Beobachtungen könnten genau die Annäherung an den Höhepunkt des Zyklus widerspiegeln, sodass das nachfolgende Muster nach unten statt nach oben verlaufen könnte (ähnlich der Anpassung zweiter Ordnung oben). Selbst wenn das Muster real ist, ist es also gefährlich, außerhalb des Datenbereichs zu extrapolieren, ohne etwas über den zugrunde liegenden Prozess hinter dem Muster zu wissen.