Prognose.linear liefert seltsame Werte

[Monkey]

Hallo zusammen,

ich möchte meinen Gasverbrauch besser nachvollziehen und vergleichen können.

Dazu erfasse ich manuell und nicht regelmäßig meinen Zählerstand.
Per Funktion (prognose.linear) möchte ich dann die Werte für den Monatsanfang und Monatsende berechnen lassen. Das funktioniert leider überhaupt nicht.

Konkret sieht es so aus:
Ich habe einen Wert für den 29.08.2022 = 3456,000
Ich habe einen Wert für den 19.09.2022 = 3457,000
Ich möchte den Wert für den 01.09.2022 bekommen. Prognose.linear liefert mir 3649,084. Ist natürlich Blödsinn.

Dann habe ich mir das Problem genauer angeschaut und folgende Zahlen genommen:

Y-Werte: 6   / 7   / 9   / 15 / 21
X-Werte: 20 / 28 / 31 / 38 / 40

Ich möchte den Y-Wert für X=30 haben. Ohne nachzudenken, weiß man, dass dieser zwischen 7 und 9 liegen muss. Sieht Excel anders -> 10,6072531. Probiert es aus.


Jemand ne Idee was ich hier falsch mache, oder welche Funktion man dafür verwenden kann? Oder muss ich mir selber was basteln?


grz
Monkey

[rate mit]

Hallo,

also ich würde es so erklären... deine Werte sind nicht "monoton steigend" (siehe Spalte D)...

[Ego]

Hallo M...,

Excel rechnet schon richtig.  Du suchst nur einen interpolierten Wert und nicht einen Wert auf der Trendlinie.

Für den linearen Trend erstellt Excel aus allen Punkten eine möglichst angenäherte Trendlinie (nach der Methode der kleinsten Quadrate) und sucht auf ihr den Y-Wert.
siehe Diagramm.

Wenn du schon die Trendfunktion zur Interpolation missbrauchst, darfs du nur die benachbarten Punkte nutzen.

[Sulprobil]

Hallo,

Lineare Extrapolationen sind hier wenig aussagekräftig, fürchte ich.
Für den Anfang würde ich Ansätze mit dem Begriff "seasonal forecast" suchen.

Aber aussagekräftigere Modelierungen müssten wohl auch die Gaspreisentwicklung und sogar Deine privaten Präferenzen mit einbeziehen.

Da im englischsprachigen Raum etwa 10x mehr Ansätze und Lösungen ausgetauscht werden, würde ich nicht auf Deutsch  suchen.

Viele Grüße,
Bernd

[LCohen]

Schade, dass es zur Fragestellung des TE keine eingebaute Funktion gibt, die einfach zwischen zwei Nachbarn linear interpoliert, wenn die Reihe mehr als 2 Punkte aufweist. Also im Sinne eines Dreisatzes, statt einer Interpolation (oder Extrapolation). Mehr willernich, sulprobil, und mehr kannernich. Und brauchernich, denn oft kommt es nur auf einen Stichtag beim Verbrauch an, der nicht durch sonderlich hohe Abweichungen gekennzeichnet ist.

PROGNOSE.LINEAR entspricht im Folgenden TREND:

=TREND({2.5.3};{1.3.5};4)
=PROGNOSE.LINEAR(4;{2.5.3};{1.3.5})
=PROGNOSE.ETS(4;{2.5.3};{1.3.5})

=TREND({2.5};{1.5};4)
=PROGNOSE.LINEAR(4;{2.5};{1.5})
=PROGNOSE.ETS(4;{2.5};{1.5})

ergeben

3,583333333
3,583333333
3,09862625

4,25
4,25
4,25

So muss man mühsam mit Verweisen/Vergleichen arbeiten. Lässt sich natürlich auch leicht als LAMBDA bauen.

---

Hier für 

A1: X-Suchwert
B1:C5: X-Werte und Y-Werte (als nebeneinanderliegende Spalten; sonst halt MTRANS mit verwenden)

alternativ als (vielleicht kennt xl2021 noch kein LAMBDA, dann bitte Alternative 1)

=LET(xy;B1:C5;x;INDEX(xy;;1);y;INDEX(xy;;2);z;A1;c;VERGLEICH(z;x);d;c+1;(INDEX(y;d)-INDEX(y;c))*(z-INDEX(x;c))/(INDEX(x;d)-INDEX(x;c))+INDEX(y;c))

=LAMBDA(z;xy;LET(x;INDEX(xy;;1);y;INDEX(xy;;2);c;VERGLEICH(z;x);d;c+1;(INDEX(y;d)-INDEX(y;c))*(z-INDEX(x;c))/(INDEX(x;d)-INDEX(x;c))+INDEX(y;c)))(A1;B1:C5)

=DREISATZKETTE(A1;B1:C5) mit DREISATZKETTE benennend
=LAMBDA(z;xy;LET(x;INDEX(xy;;1);y;INDEX(xy;;2);c;VERGLEICH(z;x);d;c+1;(INDEX(y;d)-INDEX(y;c))*(z-INDEX(x;c))/(INDEX(x;d)-INDEX(x;c))+INDEX(y;c)))

Achtung: Die X-Werte müssen aufsteigen. Außerhalb der beiden äußersten Werte gibt es Fehler.

Ich bin kein Mathematiker. Statt DREISATZKETTE könnte man vermutlich ein wissenschaftlich korrektes Wort verwenden. LINFKTREIHUNG?

[Monkey]

Perfekt, genau das habe ich gesucht!

Danke LCohen!