Excel - Prozentuale (auch negative) Differenz errechnen

[Tortellino]

Hi. Ich hab ein Problem mit meiner Tabelle und es will mir einfach keine Lösung einfallen. Google und sogar KIs konnten das Problem auch nicht lösen. Vielleicht hat jemand von euch hier eine Idee?

Also folgende Situation: In Zelle "A1" und in Zelle "A2" steht eine Zahl. Diese Zahlen können Positiv wie auch Negativ sein. Ich möchte mir nun in "A3" einfach nur die Differenz in "%" angeben lassen und zwar von "A1" (Zahl für dieses Jahr) im Vergleich zu "A2" (Zahl aus dem letztem Jahr). Doch das funktioniert einfach nicht wie gewollt sobald eine der Zahlen Negativ ist.

Beispiel:
A1 (Aktueller Wert), A2 (Alter Wert), A3 (Ergebnis), A4 (Verwendete Formel), A5 (Anderer Versuch):

-100

200

-300%

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

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

Okay, also -300% klingen erstmal richtig. Doch wenn ich nun aus der -100 in "A1" eine -1000 mache dann sinken die % auf -120. Bei -10000 sind es dann nur noch -102%. Dabei sollte der abstand ja eigentlich weiter steigen? Wenn einer der Werte in "A1" oder "A2" Negativ ist dann funktionieren die Formeln nicht mehr richtig.

Ziel ist: Wenn "A1" größer ist als "A2" dann soll die Differenz in "A3" in "%" da stehen. Wenn er kleiner ist, dann in "-%".

Ich steh auf dem Schlauch. Jemand eine Idee?

 

[hanse59]

Das ist korrekt so: wenn sich die Differenz auf A1 bezieht dann wird die Differenz (in % von A1) immer kleiner je größer A1 im Verhältnis zur Differenz wird. PROZENT ist ein Verhältnis zweier Werte, kein absoluter Wert

 

[andi_sco]

Absolutwert

Haut irgendwie nicht ganz hin:

Verglichen wird bei mir der Benzinverbrauch laut Tankstelle und laut Bordcomputer, beide jetzt gelb markiert, ohne Prozentangabe stimmen die Angaben, wie man an den anderen beiden Werten sieht - 9,44 und 3,87. Für letztere ist der klassische Dreisatz genutzt worden.

 

[zazie]

Prozentrechnen mit (teilweise) negativen Werten funktioniert nicht (nicht nur in Excel). Es kommt dabei zu 'sinnlosen' Ergebnissen. Es gibt eine zu deinem Fall mehr oder weniger direkt passende Diskussion mit entsprechenden Hinweisen (eventuell musst du zuerst eine Art Einstiegsseite bewältigen).

Muss es aber trotzdem eine Prozentrechnung sein, kannst du folgendes Vorgehen wählen ("prozentuale Veränderung" statt wie üblich "prozentuale Zu-/Abnahme"):
Berechne die Differenz zwischen dem Startwert und dem Endwert, dividiere das Ergebnis durch den absoluten Wert des Startwerts und multipliziere das Ergebnis mit 100.

 

[jenhls]

Das gewünschte Verhalten ist mathematischer Unsinn. 200 sind -200% von -100, und nicht 300%. Prozentwerte sind Quotienten und keine Differenzen.

 

[duAffentier]

Was ist das Ziel? Was für Werte sind das?

=(A1 - A2) / ABS(A2)

Dabei steht A1 für den aktuellen Wert und A2 für den alten Wert.
Die Funktion ABS(A2) gibt den absoluten Wert von A2 zurück, wodurch sichergestellt wird, dass die Berechnung unabhängig vom Vorzeichen korrekt erfolgt. Es ergibt die relative Änderung!
Welcher Manager möchte denn diese "%" haben?

Ergänzung (7. Januar 2025)

Ziel ist: Wenn "A1" größer ist als "A2" dann soll die Differenz in "A3" in "%" da stehen. Wenn er kleiner ist, dann in "-%".

Dies hier testen!

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

 

[Tortellino]

Dies hier testen!

Hab ich gemacht. Funktioniert aber nicht. Siehe hier:

-500	-500
1000	10000
-1,50	-1,05

Wenn man "A2" von 1000 auf 10000 erhöht dann wird das Ergebnis wieder kleiner obwohl der Abstand weiter steigt. Nach lesen der Beiträge hier und des, von @zazie Verlinkten Mathe Forums, ist auch klar das dies nicht so einfach funktionieren kann.

Ich werde es wohl einfach in Euro anzeigen lassen müssen, das funktioniert wenigstens wie gewünscht.

 

[duAffentier]

Aber du kannst keine % verrechnen. Was ist denn das Ziel, was du haben magst? Die Formel macht was es soll!

Du hast 2 Absoluterte. Wenn du diese verrechnest kommt eine Differenz heraus! Aber von was?

Du hast 1000€ und danach hattest du -500€ (schulden). Also 1500€ dif.
A2-A1 = 1500€ Diff.

Und nun? 1500€ in % zu was?

Mit % kann man nicht rechnen.

 

[jenhls]

Ich werde es wohl einfach in Euro anzeigen lassen müssen, das funktioniert wenigstens wie gewünscht.

Ja. Und da wir jetzt wissen, dass es um Geld geht: Letztes Jahr hat das Unternehmen 1.000 € Gewinn gemacht, dieses Jahr 500 € Verlust. Das heißt, der Gewinn ist um 100 % gesunken und der Verlust unendlich gestiegen, weil der im Vorjahr 0 war. Gewinn und Verlust sind zwei unterschiedliche Zustände, die sich nicht prozentual zueinander ins Verhältnis bringen lassen.

Differnzen gehen halt immer: Differenz year to year = dieses Jahr - letztes Jahr, bei meinem Beispiel also:
-500 € - 1.000 € = -1.500 €.

 

[Tortellino]

@duAffentier Hmm. Also angenommen 2023 gab es eine Bilanz von 10000 Euro + (also der Kontostand stieg um 10000) ... 2024 sank der Kontostand aber um 3000 Euro. Damit habe ich jetzt zwei Werte.

2024 (A1) mit -3000 und 2023 (A2) mit 10000. Diese Formel:

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

errechnet -433,33% (-4,33 dezimal)... aber wenn ich nun für 2023 nicht +10000 sondern -10000 Eintrage und für 2024 statt -3000 dann +3000, ergibt das -130% ... Ich muss also A1 und A2 in der Formel Tauschen um wieder auf 433,33% zu kommen. Doch wenn ich das tue dann funktioniert es wieder in die andere Richtung nicht... außerdem bleibt es bei -433,33% obwohl es ja eigentlich eine +% sein sollte.

Und nun? 1500€ in % zu was?

Ja, der Gedanke war: im Vergleich zum Vorjahr. Ist die Bilanz Positiv oder Negativ? Und um wie viel Prozent? So hatte ich es mir gedacht. Aber sobald Minus Zahlen ins Spiel kommen geht es nicht mehr so einfach.

@jenhls Zu Deinem Beispiel: 1000 Gewinn letztes Jahr und dieses Jahr -500. Das bedeutet für mein Vorhaben: Dieses Jahr ist das Ergebnis -300%. 500 Euro um auf 0 zu kommen und dann noch mal 2x 500 um an das Ergebnis vom letzten Jahr zu kommen. Ist das Falsch? Die oben genannte Formel errechnet zumindest -300% bzw. -3.00!

 

[duAffentier]

Sorry, ganz konfus von dir.

Du kannst die Bilian zu Bilianz nicht in % angeben.

Nenn doch mal mehr Daten, also Beispieldatei.

1000€ Gewinn zu was? Zu 0???
-500€ Verlust von 0???

10.000€ Ziel -> 9000€ gemacht = -1000€ = "10% Verlust" und nicht "-10% Verlust"
10.000€ Ziel - >11.000€ gemacht = 1000€ = "10% Gewinn"

Und nun Jahr zu Jahr --> -1000€ zu +1000€ sind 2000€ unterschied zw. 1 Jahr.

Hab also 2000€ mehr was ja falsch ist! Weil 10.000€ Ziel / 1000€ mehr gehabt...


Nenn uns mal die korekten wirtschaftlichen Themen die du berechnen magst!

 

[Tortellino]

@duAffentier Okay, möglicherweise habe ich das nicht einleuchtend genug erklärt.

Also ein Beispiel um es ganz einfach zu machen und um zu erklären worauf ich hinaus möchte. Dazu erstmal ein paar Zahlen:

2021: 1000 Euro
2022: 500 Euro
2023: 800 Euro
2024: -2000 Euro

Diese Zahlen stehen für die jeweiligen Kontostands Veränderung des entsprechenden Jahres.

Also Ende 2020 hatte ich zb. 1000 Euro auf dem Konto. Ende 2021 hatte ich 2000 Euro auf dem Konto. Daher kommt der Wert für 2021: +1000 Euro Veränderung nach oben im Vergleich zu 2020. Ende 2022 waren es dann Plus 500 Euro weil der Kontostand dann 2500 Euro war usw... Immer nur im Vergleich zum Vorjahr.

Was ich jetzt immer gemacht habe ist ganz Simpel. Ich holte mir die Zahl aus 2021 (A2=1000) und die Zahl aus 2022 (A1=500). Dann in A3 berechnetet ich einfach "=A1/A2-1" und fertig, Ergebnis: -0,50 was in Prozent dann eben -50% ergibt.

Warum das Richtig ist? Weil ich 2022 nur 500 Euro Plus gemacht habe, 2021 aber 1000 Euro. Also habe ich 2022 nur die Hälfte erreicht, 50%. Ich rechne das mal eben für die obigen Zahlen aus:

2022 = 500
2021 = 1000
Ergebnis: -50%

2023 = 800
2022 = 500
Ergebnis: +60%

So weit verständlich? 2022 waren es halt 500 Euro Plus, 2023 waren es 800 Euro Plus. 800 sind 60% mehr als die 500 von 2022. Diese Rechnung hat 15 Jahre lang so funktioniert und war auch immer korrekt. Doch 2024 habe ich 2000 Euro MINUS gemacht. Und jetzt funktioniert die Formel nicht mehr.

Ich suche nun einen Weg das immer korrekt berechnen zu lassen, unabhängig davon ob eine Zahl negativ ist oder eben nicht. Alternativ schreib ich den Wert halt in Euro hin wenn ich es nicht hinbekommen sollte.

 

[duAffentier]

Du rechnest betriebswirtschaftlich falsch! Sorry!!!!!!! Das ist Unfug!

Hier Copilot Rechnung:

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

Dabei steht der senkrechte Strich | | für den absoluten Wert, um sicherzustellen, dass die Berechnung korrekt erfolgt, unabhängig davon, ob der alte Wert positiv oder negativ ist.

Berechnungen:

1. 2021 zu 2022:
   • Alter Wert (2021): 1.000 €
   • Neuer Wert (2022): 500 €
   • Prozentuale Veränderung:

-50%
(500 - 1000) / 1000 * 100=-50

1. 2022 zu 2023:
   • Alter Wert (2022): 500 €
   • Neuer Wert (2023): 800 €
   • Prozentuale Veränderung:

60%
(800 - 500) / 500 * 100=60

1. 2023 zu 2024:
   • Alter Wert (2023): 800 €
   • Neuer Wert (2024): -2.000 €
   • Prozentuale Veränderung:

-350%
(-2000 - 800) / 800 * 100=-350
Zusammenfassung:

• Von 2021 zu 2022 gab es einen Rückgang von 50 %.
• Von 2022 zu 2023 einen Anstieg von 60 %.
• Von 2023 zu 2024 einen Rückgang von 350 %.

 

[Tortellino]

Du rechnest betriebswirtschaftlich falsch!

Ehm... Unfug? Hast Du Dir die Berechnungen des Copiloten überhaupt mal angesehen? Er kommt doch auf exakt die gleichen Werte wie ich auch.

Von 2021 zu 2022 gab es einen Rückgang von 50%.
Von 2022 zu 2023 einen Anstieg von 60%.
Von 2023 zu 2024 einen Rückgang von 350%.

Genau das hab ich doch auch ausgerechnet... Nur wie schreibe ich die Formel so das er sowohl korrekt die oben genannten -350% ausrechnet als auch die +60%? Denn bisher ist es mir nur gelungen entweder in die eine oder die andere Richtung zu Rechnen. Da aber A1 und A2 sowohl Positive als auch Negative Zahlen enthalten können, komm ich nicht weiter mit meiner Formel.

 

[duAffentier]

(Neuer Wert – Alter Wert) / |Alter Wert| × 100

 

[Tortellino]

Ja, das Problem ist:

(-2000 - 800) / 800 * 100=-350

stimmt zwar aber mach mal aus der 800 eine 8000 und siehe da, es sind nur noch -125% mit genau dieser Formel. Das kann nicht richtig sein.

 

[duAffentier]

stimmt zwar aber mach mal aus der 800 eine 8000 und siehe da, es sind nur noch -125% mit genau dieser Formel. Das kann nicht richtig sein.

Alles richtig!

Berechnungen:

1. 2021 zu 2022:
   • Alter Wert (2021): 1.000 €
   • Neuer Wert (2022): 500 €
   • Prozentuale Veränderung:

-50%
(500 - 1000) / 1000 * 100=-50

1. 2022 zu 2023:
   • Alter Wert (2022): 500 €
   • Neuer Wert (2023): 8.000 €
   • Prozentuale Veränderung:

1.500%
(8000 - 500) / 500 * 100=1.500

1. 2023 zu 2024:
   • Alter Wert (2023): 8.000 €
   • Neuer Wert (2024): -2.000 €
   • Prozentuale Veränderung:

-125%
(-2000 - 8000) / 8000 * 100=-125
Zusammenfassung:

• Von 2021 zu 2022 gab es einen Rückgang von 50 %.
• Von 2022 zu 2023 einen Anstieg von 1.500 %.
• Von 2023 zu 2024 einen Rückgang von 125 %.

 

[Tortellino]

Ja die Mathematik dahinter ist mir doch schon klar.

Doch wenn ich von -2000 Euro komme und nun auf 8000 Euro springe (was 10000 Euro unterschied entspricht) rechnet meine Formel daraus +500%. Was ja auch Sinn ergibt. 5x 2000 sind die 10000 Euro Veränderung.

Ob das betriebswirtschaftlich richtig ist oder nicht spielt erstmal keine Rolle. Es ist nur eine neben Info. Und die Formel funktioniert ja auch. Nur eben nicht für alle Fälle. Und ich dachte es gibt eine Möglichkeit diese so in Excel zu schreiben das sie immer passt, egal ob irgendwo in der Rechnung eine Negative Zahl vorkommt oder nicht.

Wenn es dafür keine Lösung gibt dann bleiben mir nur zwei Optionen: Die Formel per Hand immer ändern bei Bedarf oder ich schreib die Veränderungen einfach als Euro Wert hin.

 

[duAffentier]

SInd immer andere Fälle!

Gib mal eine Liste mit mind 10 Werten und den Fällen, die vorkommen!

Rechne das dir mal kurz vor und dann muss die Formel eben aufgeteilt werden und wird komplexer. Dazu brauch man aber mehr wie 3 Werte.

 

[Tortellino]

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.

Dann seine Rechnung die bei mir im Browser aber nicht korrekt dargestellt wird und dann:

Die prozentuale Veränderung von -2000 Euro im Jahr 2022 zu 8000 Euro im Jahr 2023 beträgt also -500%. Das bedeutet, dass der Wert um 500% zugenommen hat, was in diesem Fall eine erhebliche Verbesserung darstellt.

Danke für den Hinweis, und entschuldige mich für das Missverständnis! Wenn du noch weitere Fragen hast oder etwas anderes wissen möchtest, stehe ich gerne zur Verfügung.

Und wegen Deiner Liste mit den 10 Werten: Das Problem ist, das es immer nur Positive Werte waren all die Jahre. Jetzt 2024 kam das erstmal ein Negativer Wert. Das heißt: Du könntest Dir die Werte einfach frei aussuchen, solange mindestens ein Wert davon Negativ ist.

Aber ich schreib die Daten trotzdem einfach mal hin inklusive dem Ergebnis meiner Formel in Klammern:
2014: 746€
2015: 974€ (+30,56%)
2016: 384€ (-60,57%)
2017: 1742€ (+353,65%)
2018: 3411€ (+95,81%)
2019: 1859€ (-45,50%)
2020: 417€ (-77,57%)
2021: 1731€ (+315,11%)
2022: 1993€ (+15,14%)
2023: 2118€ (+6,27%)
2024: -3422€ (-262,51%)

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% ... Leider kann ich nicht entziffern was der Copilot da gerechnet hat. Denn er kennt offenbar die Lösung. Werd mal einen anderen Browser probieren...