Excel - Prozentuale (auch negative) Differenz errechnen

[jenhls]

Hmm, der Copilot hat sich übrigens verbessert... Auf die Frage:

Wieso ist die Prozentuale Veränderung von -2000 nach +8000 125%?
Antwortet er:
Entschuldigung, da ist ein Missverständnis aufgetreten! Die prozentuale Veränderung von -2000 Euro nach +8000 Euro ist tatsächlich nicht 125%, sondern sollte korrekt berechnet werden.

Kann mich erst jetzt wieder einklinken, Familie und so...

Die Prozentuale Veränderung von -2.000 nach 8.000 ist -500%. Die Prozentuale Veränderung von 8.000 nach -2.000 ist -125%. Wo liegt das Problem? Wurde doch alles richtig berechnet! Wenn Du natürlich erst die Änderung von 8.000 nach -2.000 berechnen lässt und dann die KI (eher KD, wie man hier sieht...) frägst, warum sie auf dieses Ergebnis kommt, ihr aber suggerierst, dass die Jahre zeitlich anders herum waren, dann kommt die Maschine halt ins Schleudern.

Du stehst glaube ich gerade einfach gewaltig auf dem Schlauch. Geht mir auch immer mal wieder so, manchmal braucht das einfach ein paar Tage. Ich habe mal ein Excel- Sheet gemacht, mit dem ich Viskositäts- Temperatur- Diagramme nachrechnen kann. Keine Ahnung, wie viele Iterationen über ETLICHE Jahre ich hatte, bis es schlussendlich gepasst hat. War am Ende ein ganz trivialer Fehler, den ich immer übersehen hatte, kombiniert mit permanenten Hirnverknotungen, wann immer ich an dem Teil gearbeitet hatte.

Darum, und sorry, gar nicht böse gemeint: Ich klinke mich hier aus. Lass das Thema mal bis Montag liegen und schau dann noch einmal drauf, vielleicht klickt es dann im Kopf 💡Viel Erfolg! 🫰

 

[Incanus]

Meine Formel hat ein Problem sobald ich aus den 2118€ des Jahres 2023 einfach mal 21180€ mache. Dann sinkt es nämlich von -262,51% auf -116,25%

Und wo ist da das Problem? Es stimmt doch. Machst Du den Gewinn größer, ist der gleichbleibende Verlust ein geringerer Anteil davon.

Von 2118€ sind 3422€ 162,51%, von 21180€ aber nur noch 16,25%.

 

[Tortellino]

@jenhls Ja Du hast recht, ist mir gar nicht aufgefallen mit dem Copiloten... Ich habe halt im Hintergrund immer wieder die Tabelle geändert und getestet. Da bin ich durcheinander gekommen weil meine Formel mir -500% sagte aber der Copilot 125... Da hab ich ihn gefragt wie er darauf kommt. Aber ist ja auch egal, das was er da gerechnet hat ist ja das was ich möchte. Ich will ja das Ergebnis von -500% sehen weil das in meinem Fall korrekt ist. Nur wie mach ich das in Excel? Tja.

@Incanus Es geht nur um den Vergleich zur Vorjahres Differenz. Mein Ziel: Zwei Zahlen vergleichen und in % ausdrücken wie weit diese beiden von einander entfernt liegen. Vielleicht drück ich mich einfach falsch aus.

Frage: Ich habe im Jahr 2023 "X" Plus. Im Jahr 2024 "Y" Plus. Die Differenz zwischen "X" und "Y" ist im Vergleich zum 2023er "X" wie viel %?

Die Zahlen mit denen gerechnet wird sind NICHT die Kontostände sondern nur die Differenz vom 01.01. bis zum 31.12. des jeweiligen Jahres. Diese Differenzen Versuch ich zu vergleichen und in % auszudrücken und zwar jeweils zur Vorjahres Differenz.

Beispiel mit einfachen Zahlen: Also 2023 hatte ich ein Gewinn von 1000€ ... 2024 aber ein Minus von 2000€. Also ist die "Performance" schlechter. Ich häng nämlich 3000 Euro hinten im Vergleich zu 2023. Also sind das -300% im Vergleich zu 2023.

2024 = -2000 Euro
2023 = 1000 Euro
Differenz = 3000 Euro
Performance = -300%

Wenn ich nun aus den 1000 Euro von 2023 aber 10000 Euro mache dann sieht das so aus:

2024 = -2000 Euro
2023 = 10000 Euro
Differenz = 12000 Euro
Performance = -600%

Das sind halt -600% und nicht, wie von meiner Formel aktuell berechnet, -120%. Ich bin ja jetzt auch viel weiter weg von dem Ergebnis von 2023.

Klar, dafür muss ich nun durch die 2024er Differenz teilen und nicht mehr durch die von 2023, da die Differenz sonst ja nur noch 2000 Euro beträgt. Ich kann die Formel ändern damit ich wieder das korrekt Ergebnis bekomme.

=(A1 - A2) / ABS(A1) * 100

Das errechnet mir die -600%

=(A1 - A2) / ABS(A2) * 100

Das hier die -120%

Alles was ich nun versuche, ist die Formel so zu formulieren, das sie immer korrekt arbeitet. Egal ob Negative Zahlen drin sind oder nicht und vor allem: egal wo sie stehen, im Vorjahr oder im Aktuellem.

Der Copilot hatte hier und da ein paar Formeln für Excel ausgespuckt die angeblich genau das bewerkstelligen sollten. Diese Arbeiten auch "korrekt" aber nur bis ich halt zum Beispiel aus einer 1000 eine 10000 mache. Obwohl die Differenz nun größer ist, sinken die Prozente wieder.

Ich fürchte es gibt zu viele "WENNs" als das man es in nur einer Formel abdecken könnte. Entweder fehlt das Negative Vorzeichen oder er nimmt die Falsche Zahl zum Teilen oder beides. Deshalb frage ich hier doch nach Hilfe. Möglicherweise hat jemand eine Ahnung wie diese Formel aussehen muss oder wie ich das sonst bewerkstelligen könnte.

 

[duAffentier]

Prozentuale Veränderung (%) = (Neuer Wert – Alter Wert) / |Alter Wert| × 100

Dann diese Formel.

 

[ShiftC]

Prozentuale Angaben sind relativ. Relativ bedeutet, dass es einen Bezug gibt.

Beispiel mit einfachen Zahlen: Also 2023 hatte ich ein Gewinn von 1000€ ... 2024 aber ein Minus von 2000€. Also ist die "Performance" schlechter.

Hier ist beispielsweise dein Bezugspunkt bzw. die "Performancereferenz" die 1.000€ von 2023.
100% weniger ergäbe 0€. 300% weniger ergäbe -2.000€.

Wenn der Referenzwert sich jetzt aber ändert, weil du nicht mehr mit 1000€ startest, sondern mit 10.000€, dann ergäbe eine 100%ige Performanceverringerung hier ebenfalls 0€. 300% weniger ergäbe -20.000€!

Deshalb ist folgende Aussage von dir nicht richtig:

Das sind halt -600% und nicht, wie von meiner Formel aktuell berechnet, -120%. Ich bin ja jetzt auch viel weiter weg von dem Ergebnis von 2023.

Die -120% sind absolut korrekt, weil das eben auf den 2023er Wert bezogen ist.

Was du suchst ist eine Formel, die zunächst erkennt, ob der Neuwert zum Vorjahreswert ( = Referenzwert) zu- oder abgenommen hat, das Vorzeichen berücksichtigt und das Verhältnis zum Abstand der beiden Zahlen bildet.

1. Bilde die betragsmäßige Differenz der beiden Zahlen und setze dabei stets die größere Zahl als Subtrahenden

Z1: Vorjahreswert
Z2: Jahreswert

=WENN(Z1>Z2; Z1-Z2; Z2-Z1)

2. Dividiere das Ergebnis von (1.) mit dem kleinsten Absolutwert der beiden Zahlen. Formatiere das Ergebnis als %.

=(Ergebnis aus 1) / MIN( ABS(Z1); ABS(Z2) )

3. Ermittle den Veränderungsfaktor des Jahreswertes relativ zum Vorjahreswert und multipliziere es mit dem Ergebnis aus (2.), um das Vorzeichen des Prozentwertes zu bestimmen.

=(Ergebnis aus 2) * WENN(Z2<Z1; -1; 1)

 

[Tortellino]

@ShiftC Zunächst mal: Danke für den Input. Ich habe Deine Formeln probiert und ja, es funktioniert soweit tatsächlich. Also Danke schon mal für Deine Mühe.

Doch ist mir im Vergleich zu meinen Formeln (eher den Ergebnissen) etwas aufgefallen. Folgendes:

2023 = 50 Euro
2024 = 100 Euro
Differenz = 50 Euro
Ergebnis Deiner Formel: +100%
Ergebnis Meiner Formel: +100%

Doch wenn ich die Zahlen umdrehe dann kommt folgendes bei raus:

2023 = 100 Euro
2024 = 50 Euro
Differenz = 50 Euro
Ergebnis Deiner Formel: -100%
Ergebnis Meiner Formel: -50%

Also wenn mich mein Verständnis nicht täuscht, dann hab ich in dem Beispiel doch nur die Hälfte des Vorjahres erreicht. Also 50% weniger. Wieso wäre in dem Fall -100% richtig?

Mir ist schon klar wie die Formel auf -100% kommt aber: Wäre -50% nicht das richtige Ergebnis in dem Fall? 100 Euro vorher, jetzt nur 50 Euro. Das sind 50% weniger als vorher oder nicht?

 

[duAffentier]

Excel macht das, was es soll. Somit kann man dies beenden hier.
Wie man was verrechnet etc., dies aber nach keinem Standard erfolgt, kann Excel auch nichts für!

Punkt und Strichrechnung beachten sowie die Differenzen und und und.
man dreht sich im Kreise, da alles hier funktionier!

 

[Incanus]

Diese Arbeiten auch "korrekt" aber nur bis ich halt zum Beispiel aus einer 1000 eine 10000 mache. Obwohl die Differenz nun größer ist, sinken die Prozente wieder.

Was logisch ist, da wir eben von Prozenten also Verhältnissen reden, nicht von Differenzen. 2.000 sind 200% von 1.000, aber 20% von 10.000.

Warum Du Deine Berechnung bei positiven Werten auf die Zahl von 2023 beziehst, bei negativen aber auf 2024 ist unverständlich.

 

[jenhls]

Also, einen Vorschlag habe ich noch. Wenn in A1 die zahl von letztem Jahr steht und in A2 die Zahl von diesem Jahr (bitte entsprechend anpassen!), vielleicht führt dann diese Formel zum gewünschten Ergebnis:

=(A2/A1-1)*100

oder wahlweise die Zelle als Prozentwert formatieren und die Klammern und die *100 weglassen.

 

[Tortellino]

@duAffentier Also ich weis ja nicht was Deine, irgendwie leicht aggressiv rüberkommende, Art die ganze Zeit soll aber nun gut: Es wäre durchaus hilfreich wenn Du die Texte auch mal lesen würdest.

Excel macht das, was es soll.

Richtig, tut es. Und die von mir gewünschten Ergebnisse lassen sich auch erzielen, WENN, ich die Formel manuell ändere. Steht in fast jedem Post von mir hier. Die Frage ist jedoch: Wie schreib ich eine Formel die immer gültig ist bzw. immer das von mir gewünschte errechnet? Na, Ideen dazu...?

Das mir übrigens klar geworden ist, das dies nicht so einfach zu bewerkstelligen ist, habe ich ebenfalls geschrieben. Doch dank @ShiftC bin ich ein gutes Stück weiter gekommen. Ihre/Seine Formeln funktionieren nämlich soweit, bis auf das oben erwähnte "Problem". Dieses zu Lösen, versuche ich gerade. Du siehst ein Kreis, ich sehe ein Weg.

Wie man was verrechnet etc., dies aber nach keinem Standard erfolgt, kann Excel auch nichts für!

Folg Du dem Standard, andere schreiben derweil Tetris in Excel oder machen andere "verrückte", nicht vorgesehene, Sachen. Meine Berechnungen sind "unlogisch"? Okay, schön. Aber so lange am Ende in der Zelle das steht was ich sehen möchte, ist mir das eigentlich egal. Und Excel, wie gesagt, kann mir das gewünschte ja anzeigen. Das in eine (immer funktionierende) Formel zu gießen ist die Herausforderung.

Und zu guter letzt: Ich habe schon geschrieben, das: Wenn ich die Formel/n nicht hinbekommen sollte, ich stattdessen auch simpel die Euro Beträge hinschreiben könnte. Doch geb ich mich nicht so schnell geschlagen bzw. gebe nicht voreilig auf. Daher werde ich es zumindest mal versuchen.

Um die wahre Lebenskunst gebührend zu berücksichtigen: Ich finde es Wunderbar das Du Dir die Zeit genommen hast auf dieses Thema zu antworten. Danke.

 

[Incanus]

Natürlich kann man unterschiedliche Formeln, z.B. über eine WENN-Abfrage in eine Zelle einbauen, z.B.

=WENN(A1>0;(A2-A1)/A2;(A2-A1)/A1)

aber warum willst Du denn falsche statt der richtigen Ergebnisse haben? Ich würde gerne wissen, was Du bezweckst.

 

[Tortellino]

@Incanus Die Formel die Du da geschrieben hast, ist doch quasi meine aus Post #1. Ich müsste nur erstmal einen Weg finden immer das richtige Ergebnis zu bekommen. Dann kann ich mich ran machen alles in eine Formel zu quetschen. Wobei das eigentlich nebensächlich ist ob ich es in nur einer Formel habe oder drei oder zehn.

Was ich bezwecke? Hier ein Screenshot meiner Test Tabelle, vielleicht trägt das zu Aufklärung bei:

Das Problem ist halt: Wenn ich die Formel ändere damit sie -2100% zeigt, dann stimmen die Ergebnisse darüber nicht mehr. Ich kann also immer nur eine Richtung "richtig" haben. Das heißt: Ich muss die Formel jedesmal anpassen, je nachdem ob die aktuelle Bilanz negativ/positiv ist.

Mein Ziel also: Eine Formel (oder mehrere) die immer alles "richtig" anzeigt, ohne das ich sie jedesmal anpassen muss.

 

[duAffentier]

Es liegt ja auch am Aufbau der Tabelle Send doch mal deine Beispiel-Datei!

 

[jenhls]

 

[Tortellino]

@jenhls Hi und Danke für Deine Arbeit. Ich habe Deine Tabelle mal übernommen um Dir zu zeigen das es so nicht funktioniert.

Wenn man "A" und "B" in der Formel tauscht, dann kommt das richtige oder sagen wir lieber "Das Gewünschte" Ergebnis.

Genau das ist ja mein Problem. Vorher gab es nur Positive Bilanzen. Deswegen stimmte die Anzeige der Prozente immer. Doch jetzt mit der Negativen Zahl in 2024 ist die Formel Fehlerhaft bzw. nicht mehr geeignet mir, wie gewohnt, die Prozente (wie gewünscht) zu errechnen.

Ich tüftle noch an der "Dreier Formel" von ShiftC oben. Die rechnen soweit alles richtig, bis auf das in Post #26 erwähnte Problem. Wenn ich vorher 100 Euro hatte und jetzt nur noch 50, dann hab ich nur noch 50% von 100. Die Formel errechnet aber immer -100%. Naja was solls. Wenn es keine Lösung gibt die für alle Fälle gewappnet ist, dann schreib ich einfach -50€ hin und fertig.

@duAffentier Die "Test Tabelle" ist das was Du oben (in Post #32) als Screenshot siehst. Zwei Zahlen und alle möglichen Formeln zum Testen. Die Formel aus dem Screenshot ist aber diese hier:

=WENN(A1>0;((A1 - A2)*100 / A2) /100;((A2 - A1)*100 / A1) /100)

 

[Incanus]

Wenn man "A" und "B" in der Formel tauscht, dann kommt das richtige oder sagen wir lieber "Das Gewünschte" Ergebnis.

Aber das ist doch weiterhin falsch. Warum willst Du es so haben? Du drehst plötzlich den Bezug.

 

[jenhls]

@jenhls Hi und Danke für Deine Arbeit. Ich habe Deine Tabelle mal übernommen um Dir zu zeigen das es so nicht funktioniert.

Anhang anzeigen 1567410

Was ist an Zeile 5 falsch? Letztes Jahr 10.000, dieses Jahr -2.000, das sind -120%. Genauso wie in Zeile 2, wo Du es als richtig anerkennst.

 

[duAffentier]

Man macht die Welt, wie Sie einem gefällt!

 

[Tortellino]

Aber das ist doch weiterhin falsch. Warum willst Du es so haben? Du drehst plötzlich den Bezug.

Du hast mein "Das Gewünschte Ergebnis" sogar zitiert und fragst dennoch warum ich das so haben möchte?

Was ist an Zeile 5 falsch? Letztes Jahr 10.000, dieses Jahr -2.000, das sind -120%. Genauso wie in Zeile 2, wo Du es als richtig anerkennst.

Ganz einfach: In Zeile 2 habe ich ein um 50 Euro BESSERES Ergebnis als im Vorjahr. In Zeile 5 aber ein um 12000 Euro SCHLECHTERES Ergebnis. Zeile 2 ist demnach ein Positives Ergebnis, (+100% im konkreten Beispiel) und in Zeile 5 ein Negatives Ergebnis (-600% im Vergleich zum Vorjahr). Ja ich weis, -120% ist auch Negativ. Wenn Du weiter ließt dann versteht Du was ich meine. Hoffe ich.

Und nebenbei: ja, "FALSCH" ist nicht das richtige Wort. Du hast recht, bzw. alle hier haben recht. Die Formel rechnet das korrekt. Mit "Falsch" war eher gemeint: Das ist nicht das was ich sehen will. Ich dachte das hätte ich ausreichend erklärt und mit "Gewünschtes Ergebnis" nochmal unterstrichen.

2023 Kontostand Start: 10000 Euro
2023 Kontostand Ende: 20000 Euro
Differenz: 10000 Euro << Das ist mein "B"

2024 Kontostand Start: 20000 Euro
2024 Kontostand Ende: 18000 Euro
Differenz: -2000 Euro << Das ist mein "A"

Die Differenz zwischen "A" und "B" ist also 12000 Euro.

Um mal ShiftC zu zitieren:

Deshalb ist folgende Aussage von dir nicht richtig:

Das sind halt -600% und nicht, wie von meiner Formel aktuell berechnet, -120%. Ich bin ja jetzt auch viel weiter weg von dem Ergebnis von 2023.

Und jetzt Achtung:

Die -120% sind absolut korrekt, weil das eben auf den 2023er Wert bezogen ist.

Was du suchst ist eine Formel, die zunächst erkennt, ob der Neuwert zum Vorjahreswert ( = Referenzwert) zu- oder abgenommen hat, das Vorzeichen berücksichtigt und das Verhältnis zum Abstand der beiden Zahlen bildet.

Genau das ist es. Und wenn man die Formeln umsetzt, wie in Post #25 beschrieben, dann rechnet Excel halt -600%. Ganz ohne Fantasie (weil ich mir die Welt mache wie sie mir gefällt ).

@Incanus Das ist mein "Das Gewünschte Ergebnis". Ob das Sinn ergibt ist doch erstmal egal (tut es übrigens). Ob das Betriebswirtschaftlich so gemacht wird ebenfalls. Meine Alte Tabelle hat mir das immer korrekt in % angegeben. Jetzt, dank Negativer Zahl, funktioniert die Formel halt nicht mehr weil ich das vor 15+ Jahren wohl einfach nicht getestet hatte...

Ich wollte nur im zweiten Schritt (der erste war selbst probieren) hier nach Hilfe fragen um das alte Verhalten wieder herzustellen. Hab aber läääängst meine Bereitschaft signalisiert die Tabelle einfach zu ändern (direkte Euro Werte statt Prozent). Also was genau ist denn das Problem? Kann ich das alte Verhalten wiederhaben mittels angepasster Formel oder nicht? Denn die Formel jedesmal ändern ist eher keine Option, da kann ich auch gleich Extern mit nem Taschenrechner Rechnen und die (aus dem obigen Beispiel) -600% einfach hintippen...

Und falls die Antwort nun lautet: "Dann nimmt doch die Formeln aus Post #25 wenn die funktionieren." Sollte vielleicht mal meine Antwort darauf lesen (also Post #26). Ich versuche gerade die Formeln meinen Bedürfnissen anzupassen aber es sieht nicht gut aus. Ein Ergebnis ist immer "unerwünscht" (nicht falsch!).

Ich werde es heute nochmal probieren. Hab ich kein Erfolg werde ich wohl auf Euro Werte umschwenken müssen. Je mehr ich darüber nachdenken desto mehr gefällt mir der Gedanke übrigens. Das lässt eine viel bessere Vergleichbarkeit zu. Von 10000 auf 20000 Euro sind +100% von 5 auf 10 Euro aber auch, in Euro eben +10000€ und +5€. Aber egal, erstmal versuch ich es nochmal.

 

[Incanus]

Du hast mein "Das Gewünschte Ergebnis" sogar zitiert und fragst dennoch warum ich das so haben möchte?

Ja, weil es nicht erklärt, warum Du es einmal so und ein andermal anders haben willst. Es geht doch immer um die Veränderung zum Vorjahr, warum du das dann plötzlich herumdrehen und 600% statt 120% sehen willst, kann man nicht nachvollziehen und Du erklärst auch nicht warum.

Kann ich das alte Verhalten wiederhaben mittels angepasster Formel oder nicht?

Das wurde ja eigentlich schon ein Dutzendmal beantwortet, mit einer Fallunterscheidung kannst Du eben unterschiedliche Formeln für unterschiedliche Gegebenheiten nutzen, Du hast ja auch schon damit angefangen, aber es wohl nicht konsequent zu Ende gebracht.

Prinzipiell können ja vier Fälle eintreten, beide positiv, beide negativ, Vorjahr positiv und aktuell negativ oder umgekehrt. Auf diese Fälle musst Du abfragen und die Formel für das von Dir gewünschte Ergebnis anpassen.