Excel - Prozentuale (auch negative) Differenz errechnen

[Tortellino]

Du erklärst auch nicht warum.

Mit -2000 (2024) VS +10000 (2023) bin ich halt insgesamt 12000 Euro vom Vorjahr entfernt. Das möchte ich halt ausdrücken mit den -600% (6x 2000 = 12000). Ich hätte also mindestens, ausgehend von den -2000 Euro, noch 12000 Euro auftreiben müssen um überhaupt erstmal auf 0% zu kommen. -120% würde ja bedeuten das ich lediglich 2400€ hinten bin. Achtung: Das gilt zumindest in der aktuellen Darstellung in der Tabelle bzw. im Vergleich zu dem was sonst an dieser Stelle immer dargestellt wurde.

Jedenfalls wäre, der aktuellen Logik folgend, -600% an dieser Stelle richtig. Wie das ungefähr aussieht kannst Du ja im Post #32 im Screenshot sehen. An der Stelle macht -120% einfach keinen Sinn.

Absurd wird es zb. dann, wenn man für 2023 1 Euro und für 2024 -10000 Euro einträgt. Ergebnis -100,01%. Das würde man in der aktuellen Darstellung eben als 1,0001€ Unterschied interpretieren und das ist doch quatsch. Der eine Euro passt glaub ich etwas öfter als 1,01x in die 10000.

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.

Ja, absolut richtig. Ich bin dabei. Nur ist mir noch nicht ganz klar, wie ich unterscheiden soll, wann welcher Bezug genommen werden muss.

Mein Problem sind die im Test (Post #26) genutzten 50€ Unterschied. Denn 100 (2023) VS 50 (2024) ist ein Minus von 50% im Vergleich zum Vorjahres Ergebnis und nicht ein Minus von 100%.

Wenn ich die Formel aber ändere das der Bezug wieder passt, dann kommen bei -2000 VS 10000 wieder -120% raus und nicht mehr -600%. Bisher ist es mir nicht gelungen beides zu erreichen.

Diese drei Formeln hier errechnen -50% beim 50€ Differenz Beispiel und -120% beim -12000€ Differenz Beispiel:

=WENN(A1>A2; A1-A2; A2-A1) << in Zelle C1
=(C1) / MAX( ABS(A1); ABS(A2) )
=(C2) * WENN(A1>A2; -1; 1)

Diese drei wiederrum -100% bzw. -600%:

=WENN(A1>A2; A1-A2; A2-A1) << in Zelle C1
=(C1) / MIN( ABS(A1); ABS(A2) )
=(C2) * WENN(A1>A2; -1; 1)

Diese funktioniert auch fürs -600% Beispiel doch kommt sogar auf +100% beim 50er...

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

Gut möglich das es auch gar keine Lösung für das Problem gibt. Aber für den Fall gibt es ja Plan B (Euro Werte).

 

[jenhls]

Mit -2000 (2024) VS +10000 (2023) bin ich halt insgesamt 12000 Euro vom Vorjahr entfernt. Das möchte ich halt ausdrücken mit den -600% (6x 2000 = 12000). Ich hätte also mindestens, ausgehend von den -2000 Euro, noch 12000 Euro auftreiben müssen um überhaupt erstmal auf 0% zu kommen. -120% würde ja bedeuten das ich lediglich 2400€ hinten bin.

Nein. -120% bedeutet, dass Du 12.000 € hinter dem Vorjahr bist, wenn im Vorjahr +10.000 € waren. Bei -600% wärst Du im aktuellen Jahr bei -50.000€, wenn es im Vorjahr +10.000 € waren.

Ich glaube wirklich, dass Du einen ganz großen Knoten im Kopf hast, der erst einmal platzen muss.

 

[Incanus]

Deine Erläuterung hier zeigen mir, dass Du an prozenztualen Angaben eigentlich gar nicht interessiert bist. Absolute Summen bzw. Differenzen passen besser. Denn wenn Du vom Gefühl her meinst weit weg vom Ziel zu sein, drehst Du den Bezug um, damit der Prozentwert größer erscheint und besser zu Deinem Gefühl passt. Kann man machen, aber dann ist es halt Kacke .

Was Du dann aber eben nicht richtig umsetzt, ist die Fallunterscheidung, da musst Du in Dich gehen und Dir anschauen, was wann für Dich richtig aussieht. Ich habe Dir da die aus meiner Sicht möglichen vier Fälle genannt, Du hast aber nur zwei davon umgesetzt und dann geht es halt nicht so, wie Du es Dir ausdenkst.

 

[Tortellino]

Nein. -120% bedeutet, dass Du 12.000 € hinter dem Vorjahr bist

Du hättest das ACHTUNG noch lesen sollen Denn: Ja, ich verstehe was Du sagst und es ist ja auch richtig was Du schreibst.

Ich versuch es mal ganz einfach ausgedrückt: Von 500 (2023) auf 10000 (2024) ist eine Steigerung um 1900%, richtig? Wenn das so ist, dann sollten es auch von 10000 (2023) auf 500 (2024) -1900% sein (IN DER DARSTELLUNG) auch wenn es genau genommen -95% sind. Verstehst Du was ich meine? Denn das aktuelle Ergebnis ist doch dann 1900% vom Ergebnis von 10000 entfernt. Also je nach Betrachtungsweise natürlich.

Sonst steht in dem einem Jahr +1900% << Kommentar: WOW!! Krasse Steigerung! Und im nächsten Jahr steht dann -95% << Kommentar: Naja, hätte schlimmer kommen können, 500% zb. oder so... Dabei wird dann überhaupt nicht klar wie schlecht das Ergebnis wirklich ist denn von 100 (2023) auf 5 (2024) sind ebenfalls wieder -95%. Zudem ist das Problem das sich kaum was verändert an den Prozenten obwohl die Zahlen sich Extrem ändern. Beispiele:

100 VS 1 = -99%
1000 VS 1 = -99,90%
10000 VS 1 = -99,99%
100000 VS 1 = -99,999%

In dem Fall spielt es gar keine Rolle mehr ob ich 99 Euro weniger erwirtschaftet habe oder 99999 Euro weniger. Toller Vergleich.

Du hast doch gesehen was ShiftC geschrieben hat. Sagst Du das ist quatsch? Denn Ihre/Seine Formeln funktionieren und errechnen im obigen Beispiel +1900% oder eben -1900% je nachdem wie die Zahlen eingetragen werden. Also genau das was ich versuche zu erreichen.

Du hast aber nur zwei davon umgesetzt und dann geht es halt nicht so, wie Du es Dir ausdenkst.

Hmm, bist Du Dir da sicher? Vier Fälle. Ja, das sehe ich auch so. "+ & +", "- & -", "+ & -", "- & +" ... Checken wir mal:

Fall Eins (+ & +):

2023 = 2000
2024 = 10000
Differenz = 8000
Ergebnis +400%

Fall Zwei (- & -):

2023 = -2000
2024 = -10000
Differenz = 8000
Ergebnis -400%

Fall Drei (+ & -):

2023 = 2000
2024 = -10000
Differenz = 12000
Ergebnis -600%

Fall Vier (- & +):

2023 = -2000
2024 = 10000
Differenz = 12000
Ergebnis +600%

Sieht alles "korrekt" (oder eher wie gewünscht) aus. Daran liegts dann wohl nicht denn: die exakt selbe Formel macht auch das hier:

2023 = 100
2024 = 50
Differenz = 50
Ergebnis -100%

Um da die "gewünschten" -50% angezeigt zu bekommen, muss ich in der Formel "=(C1) / MIN( ABS(A1); ABS(A2))" das MIN durch ein MAX ersetzten. Mir fällt aber kein "WENN" ein wie ich das unterscheiden könnte.

Vielleicht sollte ich doch einfach Euro Werte nehmen. +8000€ oder -12000€ ist recht eindeutig und unmissverständlich

 

[Incanus]

Vielleicht sollte ich doch einfach Euro Werte nehmen. +8000€ oder -12000€ ist recht eindeutig und unmissverständlich

Ja, solltest Du, denn wie Du oben in Deinem Beitrag erklärst bist Du an prozentualen Unterschieden überhaupt nicht interessiert:

Zudem ist das Problem das sich kaum was verändert an den Prozenten obwohl die Zahlen sich Extrem ändern.

 

[Tortellino]

denn wie Du oben in Deinem Beitrag erklärst bist Du an prozentualen Unterschieden überhaupt nicht interessiert

Naja, der Wert in % würde mich schon interessieren. Nur wohl in einer unkonventionellen, betriebswirtschaftlich unlogischen und Mathematisch falschen Art und Weise.

Damit wäre Deine Aussage widerlegt. Es sei denn Du redest explizit von der Definition der "prozentualen Unterschiede". Das kann dann durch aus richtig sein. Fakt ist: Es gibt Formeln die das gewünschte errechnen können, unabhängig davon wie man diese Berechnung jetzt genau nennen mag. Diese Formeln zusammen zu bringen ist aber nicht ganz so einfach.

Ich werde die Sache vorerst auf Eis legen. Vergessen ist es aber nicht. Ich werde Euro Werte nehmen und mich später nochmal daran versuchen. Spricht ja dann nix dagegen am Ende beide Werte zu nutzen, Bonus Info halt. Wie @jenhls schon sagte: Ich habe vielleicht einfach nur einen ganz großen Knoten im Kopf der erst einmal platzen muss. Auch wenns anders gemeint war

 

[jenhls]

Auf jeden Fall viel Erfolg!

 

[bughero]

Probier mal so: =(A1-A2)/ABS(WENN(A1<A2;MAX(A1:A2);MIN(A1:A2)))

 

[Incanus]

Naja, der Wert in % würde mich schon interessieren.

Nein, wirklich nicht, wie Du mehrfach erklärt hast.

Fakt ist: Es gibt Formeln die das gewünschte errechnen können, unabhängig davon wie man diese Berechnung jetzt genau nennen mag.

Auch das ist kein Fakt, Du möchtest nämlich nur große Zahlen sehen und drehst dabei die Formeln solange hin und her, bis sie Dein Gefühl unterstützen, der tatsächliche Wert ist dabei egal.

Ein Beispiel mit anderen Größen: Du hast einen Arbeitsweg von 20km, legst Du davon 10 km zurück hast Du 50% geschafft und bist schon fast da. Wenn Du in den Urlaub fährst, 1000km Strecke, hast Du nach 500km ebenfalls schon 50% hinter Dir, bist aber noch stundenlang unterwegs bis zum Ziel. Folge ich Deinen Angaben oben, kommt es Dir falsch vor in beiden 50% geschafft zu haben, weil Du ja einmal nur noch Minuten, im anderen Fall aber noch Stunden fahren musst .

 

[ThinkingOrc]

Ich würde das, was du willst so lösen:
=WENN((A2-A1<0);-ABS(A2-A1)/ABS(AI1);ABS(A2-A1)/ABS(A1))



Ob das sinnvoll ist, ist ein anderes Thema.

 

[Tortellino]

Ein Beispiel mit anderen Größen [...]

Ja Du hast recht und ich verstehe was Du sagst. Erwischt Aber wenn ich da Prozente sehen möchte, woran bin ich dann interessiert? Hmm. Egal. Aber nur um das noch abzuklären und aus interesse: Du sagst also das, wenn Du die beiden Zahlen (500) und (10000) vergleichst, sie keinesfalls 1900% auseinander liegen können bzw. nur in eine Richtung?

Ich frag nur weil die Formeln von ShiftC genau das ausrechnen (+1900% und -1900%) und es mir ebenfalls "Logisch" erscheint. Immer dran denken: Für die Darstellung in der Tabelle ist gemeint!

Die Gründe dafür hab ich Dir ja genannt: Vergleichbarkeit. Sonst steht bei Negativen Bilanzen immer irgendwas zwischen 0 und 99,9999%, während bei Positiven durchaus dann mal 1000% und mehr stehen können. Das ist ja dann quatsch, bin ja kein Intel Folien Designer.

@bughero Wenn A1 = 2023 und A2 = 2024 ist dann rechnet Deine Formel bei den Werten A1 = 10000 und A2 = -2000 600%. Dreht man die Zahlen sind es -120% nicht -600%.

@ThinkingOrc Also Deine Formel funktioniert zwar einwandfrei. Allerdings nur solange bis man die Zahlen aus Deinem Screenshot dreht. Beispiel "Fall4" -2000 (2023) VS 10000 (2024) gibt zwar die 600% aus, doch 10000 (2023) VS -2000 (2024) ergibt wieder die korrekten -120% und nicht -600%.

Das hier sind die einzigen Formeln die funktionieren. Bis auf das 100 (2023) VS 50 (2024) Beispiel. Das sind keinesfalls 100% weniger als 2023 sondern -50%.

=WENN(A1>A2; A1-A2; A2-A1) << Zelle C1
=(C1) / MIN( ABS(A1); ABS(A2))
=(C2) * WENN(A1>A2; -1; 1)

Naja was solls. Ich werde es mir nach einigem Abstand nochmal ansehen. Vielleicht erkenne ich das Problem ja noch. Falls nicht dann eben nicht

 

[ThinkingOrc]

Sorry, ich dachte du willst immer die absolute Steigerung in Prozent der Steigerung vom Vorjahr.
Fall 5 und Fall 6 (dein letztes Bsp.) haben ja eine unterschiedliche (selbst innere) Logik.
Du kannst nicht einmal in % vom größeren und einmal vom kleineren wollen. Das ist selbst in deiner Logik widersprüchlich. Bzw. es folgt dann halt keiner berechenbarer Regel.

Wenn es dir nur um das Ungleichgewicht der Abweichungsskala bei % geht, Also eine andere Skala auch ok für dich wäre, probiere es doch mal mit dem Mean Squared Error (MSE) oder dem Root Mean Squared Error (RMSE) aus dem Forecast Accuracy Measurement. Die skalieren Abweichungen nach oben und unten gleich. Aber es ist halt ne andere Skala.

In Essenz geht es dir ja darum, dass die prozentuale Abweichung bei der gleichen absoluten Abweichung mal hoch und mal niedrig sein kann. D.h. du musst eigtl nur deinen Bezugwert fixen. Das kannst du bei der Zahlenreihe z.B. einfach am ersten Wert machen (Im Prinzip ein Index) oder am arithmetischen Mittel (-> MSE und RMSE).

 

[ShiftC]

Du hast in Summe 5 Fälle.

1:    von positiv nach negativ - Vorzeichen = (-)
2:    von positiv nach mehr positiv 
3:    von positiv nach weniger positiv - Vorzeichen = (-)
4:    von negativ nach positiv
5:    von negativ nach mehr negativ - Vorzeichen = (-)

Dein Bezugswert für die relative Differenz ist immer der Vorjahreswert (von), bis auf Fall 1. Da ist dein Bezugswert der Neujahreswert (nach). Diese Bedingung gilt für die vorzeichenlose Prozentangabe.
Bei dem Vorzeichen willst du wie o.g., dass es in Fall 1, 3 und 5 negativ, ansonsten positiv ist.

1. Beginnen wir erstmal mit der Gleichung für die absolute Differenz ( = Abstand).

Absolute Differenz
JW = Jahreswert
VJW = Vorjahreswert

=ABS(VJW - JW)

2. Jetzt die Gleichung für die Prozentangabe, welche die o.g. Bedingungen berücksichtigt.

Textcode
Wenn Fall 1, dann teile die absolute Differenz durch den Jahreswert.
Ansonsten teile die absolute Differenz durch den Vorjahreswert.

=(Ergebnis aus 1) / WENN( UND ( VJW>0; JW<0; ABS(VJW) > ABS(JW); ABS(JW); ABS(VJW) ))

3. Jetzt noch das Vorzeichen für die Prozentangabe.

= (Ergebnis aus 2) * WENN(JW < VJW; -1; 1)

Schritt 2 und 3 kannst du natürlich in dieselbe Zelle packen, ebenso Schritt 1 bei Bedarf.

So sieht es dann aus:

 

[Incanus]

Du sagst also das, wenn Du die beiden Zahlen (500) und (10000) vergleichst, sie keinesfalls 1900% auseinander liegen können bzw. nur in eine Richtung?

Richtig, Prozente geben ein Verhältnis an zu einem Bezugswert.
Klassisches Beispiel war die ‚Wir schenken Euch die MwSt‘-Aktion bei MediaMarkt und Saturn. Etliche Käufer haben nun erwartet alle Preise um 19% reduziert zu bekommen, stattdessen waren es aber nur 15,97%. Warum?

Die MwSt wird auf den Nettopreis aufgeschlagen, also kostet ein Artikel netto 100€, sind es brutto mit MwSt 119€, denn der Bezugswert sind die 100€. Kaufe ich jetzt dieses Produkt werden mir aber nicht 19% von 119€ abgezogen, denn das wären dann 96,39€, sondern eben 15,97%, so dass am Ende die ursprünglichen 100€ herauskommen.

Um auf Dein Beispiel zurückzukommen sind 10.000€ tatsächlich ein Zuwachs um 1900% von 500€, umgekehrt kommt man auf 500€ aber von 10.000€ durch eine Reduktion um 95%.

 

[Tortellino]

Sorry

Ach quatsch, da gibt es nichts zu entschuldigen Ist ja auch eher ungewöhnlich was ich hier versuche umzusetzen. Von daher eher: Danke für Deine Mühe!

Mean Squared Error (MSE) oder dem Root Mean Squared Error (RMSE) aus dem Forecast Accuracy Measurement

Alles klar.... *hust. Ich habe nicht mal eine Ahnung was Du da überhaupt gesagt hast

@ShiftC Also, ich habe gelesen was Du geschrieben hast und versucht das nachzuvollziehen. Deine Formeln hab ich jetzt einfach mal nachgebaut, allerdings bekomme ich ein #DIV/0! Fehler bei der gleichung für die Prozentangabe. Meine Formel sieht jetzt so aus:

VJW = A1
JW = A2
=(B1)/WENN(UND(A1>0;A2<0;ABS(A1)>ABS(A2);ABS(A2);ABS(A1)))

Könnte das an LibreOffice liegen? Ich schreib die ganze Zeit "Excel" dabei nutze ich eher LibreOffice. Ist halt wie "Zewa"

 

[ThinkingOrc]

Hab nochmal editiert, bin doch nicht sorry, musste nur nochmal überlegen:
So sind die verschiedenen Varianten:

​	Ergebnis​	% zum Vorjahr​	% des Vorjahrs​	Index​	Abweichung zum MW​	MSE​	RMSE​
​	E​	E_n+1 / E_n -1​	E_n_+1 / E_n​	E_n / E_n1​	E_n / ( 1 / E_n1…nx)​	E_n / ( 1 / E_n1…nx)^2​	Wurzel(E_n / ( 1 / E_n1…nx)^2)​
2017​	2000​	​	​	100 %​	79,01 %​	62 %​	79,01 %​
2018​	10000​	400 %​	500 %​	500 %​	395,06 %​	1561 %​	395,06 %​
2019​	-2000​	-120 %​	-20 %​	-100 %​	-79,01 %​	62 %​	79,01 %​
2020​	10000​	-600 %​	-500 %​	500 %​	395,06 %​	1561 %​	395,06 %​
2021​	100​	-99 %​	1 %​	5 %​	3,95 %​	0 %​	3,95 %​
2022​	50​	-50 %​	50 %​	3 %​	1,98 %​	0 %​	1,98 %​
2023​	100​	100 %​	200 %​	5 %​	3,95 %​	0 %​	3,95 %​
2024​	0​	-100 %​	0 %​	0 %​	0,00 %​	0 %​	0,00 %​

Das gleiche kannst du natürlich auch nicht mit dem Gewinn selbst machen, sondern mit der absoluten Differenz zum Gewinn in Periode 1 / Mittelwert (hier 2017). Der Clou ist, dass du deinen Bezugswert irgendwie fixierst.

 

[ShiftC]

@Tortellino B1 ist falsch. Da muss B2 hin, weil erst in B2 die erste absolute Differenz entstehen kann.

 

[Tortellino]

@ShiftC Sekunde mal... Also ich habe Dein Text jetzt nochmal gelesen. In Schritt 2 soll doch in die Klammer das Ergebnis aus Schritt 1. Mein Schritt 1 steht in Zelle B1. Demnach muss im zweiten Schritt doch "B1" in die Klammer oder nicht? Denn Schritt 2 steht bei mir in B2. So siehts bei mir aus:

Zelle B1: =ABS(A1 - A2)
Zelle B2: =(B1) / WENN( UND ( A1>0; A2<0; ABS(A1) > ABS(A2); ABS(A2); ABS(A1) ))
Zelle B3: =(B2) * WENN(A2 < A1; -1; 1)

=(Ergebnis aus 1) / WENN( UND ( VJW>0; JW<0; ABS(VJW) > ABS(JW); ABS(JW); ABS(VJW) ))

 

[ShiftC]

So sieht bei mir die Formel für die Differenz aus:

Und so die Formel für die Prozente inkl. Vorzeichenberechnung.

Irgendwas machst du falsch bei dir.

Edit: Da fehlt eine Klammer bei dir. Syntax immer beachten.

 

[Tortellino]

Irgendwas machst du falsch bei dir.

Ja alles klar, ich habe den Fehler gefunden. Danke.

=B1/WENN(UND(A1>0;A2<0;ABS(A1)>ABS(A2));ABS(A2);ABS(A1))

Also die Formeln funktionieren zwar aber beim Testen das gleiche Ergebnis: Im Falle von VJW=10000 und JW=500 ergibt die Rechnung korrekte -95%. Deine alten Formeln haben an der Stelle -1900% errechnet. Dafür "stimmt" nun VJW=100 und JW=50, Ergebnis -50%. Da kam bei den alten Formeln immer +100% raus.

Es gibt wohl einfach keine Möglichkeit immer auf alle Fälle vorbereitet zu sein. Mindestens ein Fall ist immer "falsch". Was vielleicht auch einfach daran liegt, das die ganze Rechnung falsch/idiotisch ist bzw. den Wunsch das so darzustellen.