yan2021
Goto Top

Excel Wenn-Dann Berechnung mit falschem Ergebnis

Hallo nochmal,

in der gleichen Datei, wie aus meinem letzten Thread, gibt es noch ein Problem mit einer Wenn-Dann Berechnung.

Ich versuche es mal an einem Beispiel zu erklären:

Es geht um Werte, die (nicht gleichzeitig) manuell in 4 Zellen eingetragen werden und die in der 5. Zelle berechnet werden sollen.
Am Ende steht...

- in Zelle B5 der Wert 8
- in Zelle C5 der Wert 12
- in Zelle D5 der Wert 13
- in Zelle E5 der Wert 20
- in Zelle F5 das Ergebnis mit 11 --> das ergibt sich aus 20 - 8 abzüglich des Ergebnisses von 13 - 12

Die Berechnung soll wie folgt geschehen:
- wenn in B5 nichts steht, soll F5 leer bleiben
- wenn in B5 etwas steht, aber in C5 noch nichts, soll F5 auch noch leer bleiben
- wenn in B5 + C5 etwas steht, dann soll gerechnet werden C5-B5 (das Zwischenergebnis wäre in meinem Fall 4)
- wenn nun zusätzlich noch etwas in E5 steht, dann soll gerechnet werden...
      • E5-B5 (das wären 12)
      • abzuglich des Ergebnisses von D5-C5 (das wäre 1)
      • Ergebnis wären dann 11

Meine bisherige Formel dazu:

=WENN(B5="";"";WENN(UND(B5>0;C5="");"";WENN(UND(B5>0;C5>0);C5-B5;WENN(E5>0;E5-B5-(D5-C5)))))  

Dabei erscheint jedoch als Ergebnis 4 statt 11. Es bleibt also der Wert der dritten Berechnung stehen (C5-B5).
Und ich habe schon massenhaft Formel-Konstellationen ausprobiert... auch schon mit einer Hilfszelle. Aber auch wenn ich diese in die Formel integriere, erhalte ich nicht die 11 als Eregebnis.

Was mache ich falsch? face-wink

Grüße,
Yan

Content-Key: 1121012488

Url: https://administrator.de/contentid/1121012488

Ausgedruckt am: 28.03.2024 um 21:03 Uhr

Mitglied: 148656
148656 04.08.2021 um 09:35:45 Uhr
Goto Top
Moin Yan,

Da dich das Thema des Öfteren beschäftigt.
Die Hilfe, in den Microsoft Produkten, ist nicht mehr so Textlastig wie zu Disketten-Zeiten.

https://support.microsoft.com/de-de/excel
Das online Angebot, hat es auch in sich.
Howto's in Schrift und Videos.

Gruß
C.C.
Mitglied: 149062
149062 04.08.2021 aktualisiert um 12:03:22 Uhr
Goto Top
=WENN(ODER(B5="";C5="");"";WENN(E5>0;(E5-B5)-(D5-C5);C5-B5))
Mitglied: Yan2021
Yan2021 04.08.2021 aktualisiert um 11:56:36 Uhr
Goto Top
Danke Dir für den Hinweis Caveman.
Und danke für den Code evoplus - hat vom Ergebnis her super funktioniert.
Ich habe offensichtlich einfach zu kompliziert gedacht face-smile

Aber nach Eingabe NUR der Zahl 8 in B5 (alle anderen Felder bleiben frei), erscheint im Feld F5 das hier --> "###########"
Das Feld soll aber frei bleiben.

Grüße von
Yan
Mitglied: 149062
149062 04.08.2021 aktualisiert um 12:10:42 Uhr
Goto Top
Zitat von @Yan2021:
Aber nach Eingabe NUR der Zahl 8 in B5 (alle anderen Felder bleiben frei), erscheint im Feld F5 das hier --> "###########"
Hätte eigentlich gedacht das du den absichtlich von mir eingebauten Fehler nun selbst findest ... einfach das UND durch ODER ersetzen ... tja falsch gedacht, das kommt halt vom copy n paste syndrom ...

NEVER TRUST FOREIGN CODE ! ALWAYS THINK BEFORE YOU USE IT.
Mitglied: Yan2021
Yan2021 04.08.2021 aktualisiert um 13:55:07 Uhr
Goto Top
Ooooh, sorry. Da wär ich jetzt nicht drauf gekommen, denn für mich war auch das UND logisch.
"Wenn in B5 nix und in C5 nix, dann in F5 auch nix" - hätte ja auch passen müssen.
Aber mit dem ODER verstehe ich es jetzt - auch wenn ich nicht weiß, wieso das Feld F5 nur leer bleibt mit ODER statt UND.

Und Dein Spruch unten ist klasse face-wink

Grüße von
Yan
Mitglied: Yan2021
Yan2021 04.08.2021 aktualisiert um 14:14:30 Uhr
Goto Top
Noch eine Frage zum Runden:

Nachdem ich jetzt die Zellen als Uhrzeiten mit "hh:mm" formatiert habe, sehe ich oben in der Zeile, wo man den Code lesen kann, dass die Angabe immer mit "hh:mm:ss" erfolgt. Dadurch stimmen die Ergebnisse nicht mehr (wenn man auf volle Minuten auf-/abrunden will), da beispielsweise das Ergebnis "08:00:56" dann in der Zelle als "08:01" angezeigt wird.

Ich möchte aber, dass ab "08:00:31" auf- und bei "08:00:29" abgerundet wird auf die nächste, bzw. gleiche Minute.

Dazu habe ich schon Versuche gemacht mit "Runden, Aufrunden, Abrunden oder VRunden". Aber ich bekomme das nicht hin.
Alternativ wäre eine Lösung, dass auch tatsächlich nur mit "Stunde + Minute" gerechnet wird und ohne Sekunden.

Grüße von
Yan
Mitglied: erikro
erikro 04.08.2021 um 21:09:51 Uhr
Goto Top
Moin,

na Excel macht genau das, was Du sagst:

> =WENN(B5="";"";WENN(UND(B5>0;C5="");"";WENN(UND(B5>0;C5>0);C5-B5;WENN(E5>0;E5-B5-(D5-C5)))))  
> 

B5 und C5 sind auch dann größer Null (wird nur mit positiven Zahlen gerechnet? Besser wäre ISTZAHL()), wenn in E5 etwas steht. Damit wird die vierte Bedingung nie erreicht.

=wenn(oder(b5="";c5="");"";wenn(E5="";c5-b5;e5-b5-d5-c5))  

hth

Erik
Mitglied: Yan2021
Yan2021 05.08.2021 aktualisiert um 09:00:27 Uhr
Goto Top
Hallo Erik,

danke für Deine Erklärung.
Aber sorry, ich habe es nicht wirklich verstanden.

Was meinst Du mit "ISTZAHL())"?
Und Dein Code weicht ja von dem ab, den "evoplus" mir geschrieben hatte (bei der Angabe für "E5").

Code evoplus:
=WENN(ODER(B5="";C5="");"";WENN(E5>0;(E5-B5)-(D5-C5);C5-B5))  

Code Erik:
=wenn(oder(b5="";c5="");"";wenn(E5="";c5-b5;e5-b5-d5-c5))  

Was soll / müsste ich also ändern?

Grüße von
Yan
Mitglied: erikro
erikro 05.08.2021 um 11:03:06 Uhr
Goto Top
Moin,

Zitat von @Yan2021:
danke für Deine Erklärung.

Gerne.

Aber sorry, ich habe es nicht wirklich verstanden.

Na dann. face-wink

Was meinst Du mit "ISTZAHL())"?

ISTZAHL() ist eine Excelfunktion, die prüft, ob in einer Zelle eine Zahl steht. ISTZAHL(A1) ist also wahr, wenn in A1 irgendeine Zahl steht. Wenn nicht, dann ist es falsch. Das wäre hier besser als die Prüfung >0, denn jede Zeichenkette, die nicht mit Minus beginnt und darauf eine Ziffernfolge, ist größer als Null.

Und Dein Code weicht ja von dem ab, den "evoplus" mir geschrieben hatte (bei der Angabe für "E5").

Code evoplus:
> =WENN(ODER(B5="";C5="");"";WENN(E5>0;(E5-B5)-(D5-C5);C5-B5))  
> 

Code Erik:
> =wenn(oder(b5="";c5="");"";wenn(E5="";c5-b5;e5-b5-d5-c5))  
> 

Nein, die beiden Codes sind identisch. Jedenfalls logisch identisch. @149062 dreht in der zweite WENN-Funktion lediglich die Bedingung um. Ich teste, ob die Zelle leer ist, dann berechne ich das, was bei leerer Zelle gerechnet werden soll. @149062 testet, ob was drin steht und dann die bei mir zweite Berechnung. Das ist gehuppt wie gesprungen. Ich persönlich halte es aber für besseren Stil, die Prüfungen der WENNs möglichst einheitlich zu gestalten. Aber das ist eine reine Stilfrage und hat mit der Funktion nichts zu tun.

Liebe Grüße

Erik
Mitglied: Yan2021
Yan2021 05.08.2021 um 12:13:38 Uhr
Goto Top
OK, hast es für mich ein bisschen übersichtlicher gemacht mit Deiner Erläuterung.

Ich habe eben - eher durch Zufall - festgestellt, dass Excel offenbar automatisch rundet.
Vielleicht war das ja auch weiter oben gemeint.

Ich habe folgende Uhrzeit-Einträge in die jeweiligen Zellen gemacht:
B5 = 08:00:56
C5 = 12:00:10
D5 = 12:30:54
E5 = 16:00:20
Ergebnis in F5 = 07:28 (mit der obigen Formel)

Dabei sind die angegebenen kompletten Zeiten (also mit Sekunden-Angaben) jedoch nur oben in der Eingabeleiste sichtbar. In den eigentlichen Zellen sieht man nur 08:00 / 12:00 / 12:30 / 16:00

Da das Ergebnis aber 07:28 ist, muss Excel ja offensichtlich automatisch runden.
Das würde auch die Angabe erklären und im Grunde sollte es ja auch so sein.
Dann müßte ich im Ergebnisfeld einfach nur den Zusatz "gerundet" machen und schon wäre es auch verständlich face-smile

Richtig so? face-wink

Grüße und nochmal danke von
Yan
Mitglied: erikro
erikro 05.08.2021 um 12:40:13 Uhr
Goto Top
Moin,

Zitat von @Yan2021:
Da das Ergebnis aber 07:28 ist, muss Excel ja offensichtlich automatisch runden.

Nein, da wird nichts gerundet. So lange Du keine Rundungsfunktionen benutzt, rechnet Excel so exakt, wie es kann. Bei den Dimensionen hast Du keine Fehler. Es ist lediglich ein Darstellungsproblem:

Beim Format hh:mm werden Stunden und Minuten angezeigt. Die Sekunden fehlen und, sofern es mehr als 24h sind, fehlen die Tage auch. Also 0,31 wird als 07:28 angezeigt. 1,31 wird auch als 07:28 dargestellt. Usw.

Format hh:mm:ss zeigt die Sekunden mit an. Tage werden wieder weggelassen.

Beim Format [hh]:mm sorgen die eckigen Klammern dafür, dass alle Stunden angezeigt werden. 0,31 wird wieder zu 07:28. Aber 1,31 wird zu 31:28. Hängst Du noch :ss an das Format dran, sind auch wieder die Sekunden sichtbar.

Das kann man dann noch weiter treiben: [hh] würde z. B. nur die aufsummierten Stunden anzeigen. [mm] zeigt das Ganze in Minuten an und [ss] in Sekunden. Eckige Klammern sind nur einmal pro Format bei der größten Angabe erlaubt. Also hh:[mm] oder [hh]:[mm] führt zum Fehler.

hth

Erik
Mitglied: Yan2021
Yan2021 05.08.2021 aktualisiert um 13:50:00 Uhr
Goto Top
OK, vielleicht habe ich es ja oben blöde erklärt...
Versuche nochmal zu beschreiben, was ich meinte.

Also...
Wenn man so auf das Tabellenblatt drauf schaut, sieht man die Zeiteinträge (kommen, Pause, gehen) so:

08:00 / 12:00 / 12:30 / 16:00

Das müßte dann eine Arbeitszeit von 07:30 ergeben... also rein optisch face-wink

Oben in der Eingabezeile stehen jedoch folgende Werte:

08:00:56 / 12:00:10 / 12:30:54 / 16:00:20

Da Excel in der Ergebniszeile aber nicht 07:30 einsetzt (was rein optisch ja auch richtig wäre), sondern 07:28, nehme ich halt an, dass Excel hier automatisch rundet, bzw. mit den tatsächlichen Werten rechnet (inkl. der Sekunden). Denn aus dem 1. Feld wird offenbar 08:01 und aus dem 3. Feld wird 12:31. Das sind genau die beiden Minuten, die am Schluss dann auch beim Ergebnis abgezogen werden.

Ich hoffe, ich konnte es jetzt besser rüber bringen face-smile

Danke Euch für´s lesen.

Grüße von
Yan
Mitglied: erikro
erikro 05.08.2021 um 14:42:16 Uhr
Goto Top
Moin,

es wird mit den tatsächlichen Werten gerechnet. Es stehen in Deinen Zellen tatsächlich:

0,333981481481481
0,500115740740741
0,521458333333333
0,666898148148148

Wird die Berechnung ausgeführt, dann ist das Ergebnis tatsächlich

0,311574074074074

Und das ist das, was Excel ausgibt. Deshalb sollte man auch bei Arbeitszeitberechnungen auf die nächste volle Minute aufrunden. Dann stimmt alles wieder und es gibt keine verwirrende Anzeige. face-wink

Liebe Grüße

Erik
Mitglied: WilfKe
WilfKe 05.08.2021 aktualisiert um 15:05:18 Uhr
Goto Top
Hallo Yan2021,

versuche einmal folgende Hinweise zu beachten:

  • alle beteiligten Zellen Format prüfen auf Standard / Währung ... kein Text !
  • Ausrichtung auf Standard, damit sind Problem-Zellen sichtbar
  • in der Berechnungs-Formel niemals mit " " / leer arbeiten, nur mit 0 !
  • falls die 0 in der Anzeige stört, dann Datei / Optionen / Erweitert / Block: Optionen für dieses Arbeitsblatt "In Zellen mit Nullwerten eine Null anzeigen" abwählen

Damit sind erst einmal Rechenfehler ausgeschlossen.
Mitglied: Yan2021
Yan2021 05.08.2021 aktualisiert um 15:48:56 Uhr
Goto Top
OK und danke für die super Erläuterungen.
Danke auch an Dich "WilfKe" für die Tipps. Werde mich damit nochmal eingehender befassen.

Jetzt hab ich auch verstanden, dass Excel eigentlich im Hintergrund gar nicht mit den Uhrzeiten rechnet, sondern mit Zahlen und massenhaft Kommastellen. Und dann wird das Ergebnis wieder in eine Uhrzeit umgewandelt.

Du "Erik" hast im Grunde genau das geschrieben, was ich ja eigentlich machen wollte.
Du schreibst "auf die nächste volle Minute aufrunden".
Ich hatte das über die "Datenüberprüfung" versucht. Aber habe das nicht hinbekommen.

Dabei hatte ich 3 Versuche gemacht, wobei ich den Code bei "Datenüberprüfung / Einstellungen / Benutzerdefiniert" für das Feld F5 eingetragen hatte, in dem die Ergebnisse stehen.
=OBERGRENZE(F5;"0:01")  
und:
=OBERGRENZE(F5;"00:01:00")  
und:
=RUNDEN(F5*24*60;0)/(24*60)

Leider blieb in meinem Beispiel in Zelle F5 dennoch die 07:28 stehen. Und übrigens auch, wenn ich statt "" dann 0 eingebe und die Einstellungen in den Optionen entsprechend ändere.

Vielleicht liegt es ja auch daran, dass ja in Zelle F5 schon ein Code steht (Ergebnisberechnung). Vielleicht kann die Datenüberprüfung dann dieses Feld nicht entsprechend anweisen zu runden.

Wie Ihr seht, bin ich nah dran... aber noch nicht am Ziel face-wink

Grüße von
Yan
Mitglied: it-frosch
it-frosch 05.08.2021 um 17:40:54 Uhr
Goto Top
Hi Yan,
ich möchte dir mal die Excel Kurse auf Coursera empfehlen.
Es gibt 4 Kurse mit Prüfung (Zertifikat) und wenn du die durch hast, dann brauchst du solche Fragen nicht mehr zu stellen. face-wink

Die Prüfungen sind nicht easy und du musst wirklich wissen was du tust.

Ich fand sie super und habe viele Dinge gelernt, obwohl ich seit über 20 Jahren mit Excel intensiv arbeite.

grüße vom it-frosch
Mitglied: Yan2021
Yan2021 06.08.2021 aktualisiert um 07:54:37 Uhr
Goto Top
Hi it-frosch,
danke für die Info.
Aber das hilft mir ja im Moment nicht weiter und ich habe ja nicht ständig solche Fragen.
Denke auch nicht, dass mein Chef für mich einen solchen Kurs zahlen will face-wink

Da Du den Kurs ja schon besucht hast, kannst mir sicher auch mit meinem Problem helfen face-big-smile

Grüße von
Yan
Mitglied: Yan2021
Yan2021 06.08.2021 um 10:32:38 Uhr
Goto Top
Hallo nochmal.

Habe noch ein bisschen herum probiert und jetzt eine Verschachtelung von WENN-Funktion und OBERGRENZE-Funktion gefunden.
Der folgende Code bringt tatsächlich das Ergebnis 07:30 in Zelle F5

=WENN(ODER(B5="";C5="");"";OBERGRENZE(WENN(E5>0;(E5-B5)-(D5-C5);C5-B5);"0:02"))  

Hier hatte ich ursprünglich mit "0:01" am Ende getestet, aber da blieb die 07:28 stehen.
Mit "0:02" werden dann wohl 2 Minuten ausgeglichen.

Interessant ist jedoch, dass mit diesem Code die Einträge "08:01 / 12:00 / 12:31 / 16:00" tatsächlich und korrekt auch auf 07:28 stehen bleiben, trotz des Codes mit "0:02".
Ebenso die Einträge "08:00 / 12:00 / 12:30 / 16:00". Diese werden auch korrekt mit 07:30 als Ergebnis angezeigt, ebenfalls trotz des Codes mit "0:02".

Ich verstehe zwar nicht wieso das funktioniert, aber vielleicht ist es ja die Lösung face-smile face-wink

Was denkt Ihr?

Grüße von
Yan
Mitglied: Yan2021
Yan2021 06.08.2021 um 11:38:56 Uhr
Goto Top
Sorry...... nochmal ich face-smile

Habe einen guten Tipp gelesen.
Und zwar kann ich das Problem einfach direkt über eine Änderung in meinen ja schon vorhandenen VBA-Code lösen.

Dort habe ich folgendes geändert:

statt
Target = Time

jetzt
Target = CInt(Time * 24 * 60) / 24 / 60

Dadurch steht jetzt oben in der Eingabezeile jeder Eintrag zwar noch mit der Sekundenangabe (z.B. 08:00:00), aber die Sekunden bleiben immer bei Null stehen!!

Und mit "CInt" statt "Int" funktioniert auch das Runden korrekt und es wird nicht einfach nur abgeschnitten.

Hab´s eben ausprobiert und funktioniert face-wink

Hoffe das war´s dann und sorry wegen der vielen Nachfragen

Viiiieeeelen Dank und Grüße von
Yan face-smile