Live-Forum - Die aktuellen Beiträge
Anzeige
Archiv - Navigation
1584to1588
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Formel gesucht

Formel gesucht
19.10.2017 17:59:21
Frank
Hallo allerseits,
ich habe folgenden Sachverhalt: In Zelle M38 wird die Gesamtstundenanzahl mittels =SUMME(M7:M37) berechnet.In Zelle N38 steht die Solstunden, in dem Fall 116:96. In Zelle O38 steht die Gesamtüberstundenanzahl mittels =SUMME(O6:O37).
Ich möchte diese Formel in Zelle O38 nun so abändern,das im Falle von Minusstunden die Differenz aus Zelle M38 zu N38 von O38 abgezogen wird.
Wie bewerkstellige ich das?
Lg Frank

33
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Feine derartige Zeit 116:96 gibt es nicht ...
19.10.2017 18:11:39
...
Hallo Frank,
... hast Du Dich nur verschrieben?
Gruß Werner
.. , - ...
AW: Feine derartige Zeit 116:96 gibt es nicht ...
19.10.2017 18:22:55
Frank
Hallo Werner,
nein,eigentlich nicht.Ich habe diese Zahl extra so geschrieben,weil die Zelle im Stundenformat formatiert ist und ich dachte,das dies dann mit dem weiter rechnen einfacher ist. Normal müsste es natürlich 116,96 heissen.
Lg Frank
AW: also ist es eine Dezimalzahl ...
19.10.2017 18:32:50
...
Hallo Frank,
... das wird "exceltechnisch" nämlich anders gesehen und behandelt.
Mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=129
Für Deine gesuchte Berechnung ist somit noch wichtig, wie Deine Zeitangaben in Spalte M und O vorhanden sind. Am besten Du stellst mal eine entsprechenden Ausschnitt als Exceldatei hier ein. Dann "reden" wir beide vom Gleichen.
Ich bin dann allerdings erst einmal offline.
Gruß Werner
.. , - ...
Anzeige
AW: dazu nun ...
19.10.2017 20:07:51
...
Hallo Frank,
... ich hab den VBA-Anteil Deiner Datei nicht mit übernommen (mach ich prinzipiell nicht). Evtl. sind ja darüber Deine Bereichsnamen wie Farbe etc. definiert, so dass diese in meiner VBA-freien Datei diese als #Name? ausgewiesen werden.
Aber erkennbar ist, dass Du mit dem Excel-Zeitformat arbeitest.
Deine angestrebte Formel wäre absolut solange kein Problem, wenn die Differenz nie einen Negativwert ergeben kann. Wenn das allerdings möglich wäre, dann müsstest Du entweder mit Indutriezeit arbeiten oder bei einer Formellösung die negative Zeit als Textwert ausgegeben.
Bezogen auf Deine ursprüngliche Angaben, wonach die Sollzeit in N38 stünde z.B. so: =WENN(N38-M38 .. , - ...
Anzeige
AW: dazu nun ...
19.10.2017 21:03:46
Frank
Hallo Werner,
irgendwie werde ich aus deiner Formel nicht schlau zumal sie in O38 nicht den gewünschten Erfolg bringt. Vielleicht habe ich mich aber auch nicht klar genug ausgedrückt,wobei mir jetzt ein neuer Gedanke gekommen ist.
Nehmen wir als Wert in Zelle O6 mal 150,in den Zellen O7-O37 die jeweiligen Überstunden. Nun habe ich mir folgendes gedacht:
Wenn der Wert aus Zelle M38 größer 116,96 ist,dann sollen Werte von O7-O37 zu O38 dazu gezählt werden,wenn M38 weniger als 116,96 aufweist,dann soll die Differenz von M38 abgezogen werden.Im leeren bzw neuen Blatt steht in O38 lediglich der Übertragswert aus O6.
Man könnte natürlich auch hergehen,indem man einfach die Differenz aus M38 zu O38 hinzufügt,geht aber im Moment noch nicht,da Zusatztage komplett in die Überstunden gehen.
Gruß Frank
Anzeige
AW: dann ...
19.10.2017 21:17:34
...
Hallo Frank,
... stell doch mal eine Datei ein, wo ich Deine Angaben einfacher nachvollziehen kann. Ich schau es mir dann Morgen dann noch mal an.
Gruß Werner
.. , - ...
AW: ich kennzeichne den thread als offen ...
20.10.2017 09:11:36
...
Hallo Frank,
... damit sich andere den evtl. annehmen.
Wie ich schon schrieb, deaktiviere ich prinzipiell den VBA-Teil einer xls-Datei. Dadurch erscheint bei mir in Spalte O lediglich #NAME? Aber selbst wenn da Werte erscheinen würden, ist mir momentan nicht eindeutig klar, was Du wann und wo ermittelt haben möchtest.
So wie ich Deine bisherigen Angaben interpretiere, liefe das von Dir gewünschte formeltechnisch gesehen auf einen Zirkelbezug hinaus und ist somit mit Formel nicht wirklich zu lösen.
Aber vielleicht täusche ich mich und ein Anderer hat einen klareren Blick und kann Dir helfen.
Gruß Werner
.. , - ...
Anzeige
AW: ich kennzeichne den thread als offen ...
20.10.2017 18:47:48
Frank
Hallo Werner,
ich habe jetzt mal nen Screen gemacht damit du siehst,was wo steht. Im Prinzip ist es ganz einfach was ich wo möchte,weiß nur nicht genau wie ich es erklären soll.
Solange in M38 der Wert von 116,97(116,96 ist Sollwert) nicht erreicht ist,sollen bzw dürfen die Werte aus O7-O37 nicht zu O38 dazu addiert werden,sondern erst bei Erreichen.Wenn der Sollwert in M38 nicht erreicht wird,dann soll die fehlende Differenz von O38 abgezogen und zu M38 hinzu addiert werden.
Sorry,aber noch besser kann ich es nicht erklären.
Gruß Frank
Userbild
Anzeige
AW: wir drehen uns im Kreis, ...
20.10.2017 19:48:28
...
Hallo Frank,
... in Deinem Beispiel sind in M38 161:05 ermittelt. Diesen Wert willst Du mit Deinem Sollwert 116,96 vergleichen. Auf meine gestrige erste Rückfrage schreibst Du dazu: "...habe diese Zahl extra so geschrieben,weil die Zelle im Stundenformat formatiert ist und ich dachte,das dies dann mit dem weiter rechnen einfacher ist" Diese Zahl jedoch in Stunden formatiert ergibt: 2807:16 und ist somit natürlich in jedem Monat größer als Dein Wert in M38.
Heute schreibst Du weiter:
"Solange in M38 der Wert von 116,97(116,96 ist Sollwert) nicht erreicht ist,sollen bzw dürfen die Werte aus O7-O37 nicht zu O38 dazu addiert werden,sondern erst bei Erreichen"
Das würde zunächst bedeuten, dass O38=O6 bleibt !?
Weiter hast Du dann geschrieben: "Wenn der Sollwert in M38 nicht erreicht wird,dann soll die fehlende Differenz von O38 abgezogen und zu M38 hinzu addiert werden"
Sorry, aber das ist für mich alles nicht eindeutig und teil sogar widersprüchlich.
Schreib doch mal ganz konkret (ohne jegliche Formel) für Dein aufgezeigtes Beispiel:
1.) welcher Stundenwert (im [h]:mm -Format) ist Dein Sollwert für M38
2.) welcher Stundenwert (im [h]:mm -Format) muss dafür in O38 nach Deinen ermittelt werden
3.) welcher Stundenwert (im [h]:mm -Format) soll danach in N38 stehen
4.) welchem Fall (Sollwert unterschritten oder überschritten) entspricht Deiner Meinung nach Dein Beispiel.
Ich schau dann Morgen noch einmal.
Gruß Werner
.. , - ...
Anzeige
AW: wir drehen uns im Kreis, ...
20.10.2017 21:57:59
Frank
Hallo Werner,
richtig, zu Beginn eines Monats steht in O38 nur der Wert aus O6.
Zu 1: Der Sollwert in M38 ist 116,96
Zu 2: Da gibts keine bestimmte Vorgabe,da ich nie weiß wieviel Stunden ich genau mache
Zu 3: In N38 muss nicht zwangsläufig etwas stehen,wenns hilft könnten da die Sollstunden von 116,96 stehen
Zu 4: Sollwert überschritten wäre für mich alles ab 116,97 und unterschritten alles bis 116,95
Ich hoffe,es kommt langsam Licht ins Dunkle
Gruß Frank
AW: (m)ein neuer Versuch ...
21.10.2017 09:00:29
...
Hallo Frank,
... Klärung herbeizuführen.
Lies bitte nochmal meine Aussagen von gestern Abend durch und schau Dir dazu unten stehende Tabelle an. Schau Dir dazu auch nochmal: http://www.online-excel.de/excel/singsel.php?f=129 an.
Danach antworte nochmal zu den Punkten 1.) bis 4.) und zwar unter Beachtung (!) deren Überschrift: "...für Dein aufgezeigtes Beispiel"
Auf Deine Antwort kann ich jedoch frühestens Morgen Nachmittag bzw. am Montag reagieren.
 ABCDE
1Dezimalzahlim Format: [h]:mm Zeit im Format: [h]:mm= Dezimalzahl
20,256:00 2:000,08
30,9723:16 9:000,38
410,00240:00 20:000,83
5116,962807:02 161:056,71

Formeln der Tabelle
ZelleFormel
B2=A2
E2=D2
B3=A3
E3=D3
B4=A4
E4=D4
B5=A5
E5=D5


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Gruß Werner
.. , - ...
Anzeige
AW: (m)ein neuer Versuch ...
21.10.2017 09:52:44
Frank
Guten Morgen Werner,
ich glaub wir lassen das mal im Moment da a) du mich immer mehr verwirrst und b) ich einen Fehler bemerkt habe,der in der nun benötigten Berechnungsgrundlage so gar nicht geht. Bisher bin ich davon ausgegangen, 3 Tage die Woche mal 9 Stunden,alles darüber hinaus wären Überstunden. Da ich nun aber eine festgelegte Stundenanzahl von 116,96 habe,passt meine Berechnung nicht, denn die Differenz aus M38-116,96 muss ja identisch sein mit Summe O7:O37,was aber im Beispiel nicht der Fall ist und somit muss ich meinen Tabellenaufbau nochmals überdenken.
Gruß Frank
AW: wenn Du meine Hinweise beachtet hättest, ...
22.10.2017 19:03:19
...
Hallo Frank,
... hättest auch Du feststellen können/müssen, dass ich Dich nicht verwirre will. Ich stelle jedoch fest, dass Du Dich einfach nach wie vor "auf dem falschen Dampfer bewegst und offensichtlich auch nicht bereit oder in der Lage bist, meine angebotenen Hilfestellungen zumindest richtig zu lesen und daraus die nötigen Schlussfolgerungen zu treffen.
Gruß Werner
.. , - ...
Anzeige
AW: (M)ein neuer Versuch ...
21.10.2017 10:38:28
Frank
Hallo,
jetzt mal ein neuer Ansatz. Zellen O7-O37 lassen wir zunächst ausser acht.
Zellen M38,N38 und O38 sind im im Format [hh:mm] formatiert.
Schritt 1 meines Gedankenganges wäre: Formel in Zelle O38 : =O6;WENN(M38>"116:96";O6+(M38-N38)),in Zelle N38 steht der Wert 116:96. Alternativ könnte ich mir auch folgende Formel vorstellen: =O6;WENN(M38>(27*4,33);O6+(M38-N38)). Leider steckt da natürlich ein Fehler drin,da mir in O38 #WERT! ausgegeben wird.
Wie muss hier die korrekte Formel lauten?
Gruß Frank
AW: Siehe auch..
21.10.2017 18:50:05
Frank
Scheint ja wohl wichtiger zu sein,hier eine Verlinkung zu nem anderen Forum vorzunehmen,als sich ggf. meines Problems anzunehmen :-D
AW: Siehe auch..
21.10.2017 22:54:09
SF
Hola,
scheint wohl wichtiger zu sein, mehrere Foren mit deinem Problem zu befragen, ohne dass diese voneinander wissen.
Gruß,
steve1da
AW: Siehe auch..
21.10.2017 23:58:11
Frank
Hallo,
ich habe nun eine 99% Lösung gefunden. Wäre nett wenn mir jetzt noch jemand bei dem fehlenden 1 Prozent behilflich sein könnte.
In M38 steht als Beispiel der Wert 146:48(=Summe(M7:37)) und hat das Format [hh:mm].In N38 steht die Formel =ABRUNDEN(1041/8,9;2) ,it dem Ergebnis 119,96 und hat das Format Zahl mit 2 Kommastellen.In O6 steht 20:00 und hat das Format [hh:mm]. In O38 steht jetzt folgende Formel: =WENN(M38>N38/24;O6+M38-(N38/24)).
Soweit ja schon ganz gut,nur stimmt das Ergebnis nicht ganz.Die Differenz zwischen M38 und N38 beträgt 29:52 Stunden. In O38 werden aber nur 26:50 zu O6 hinzu addiert.
Ich habe alles mögliche versucht,es aber nicht hinbekommen,das Excel mir in O38 das korrekte Ergebnis liefert.
Gruß Frank
AW: wenn Du meinst ...
22.10.2017 19:21:02
...
Hallo Frank,
... Du hättest eine "99% Lösung" gefunden, dann kann ich dem nicht folgen.
Aus Deiner letzten (neuen Angaben: "...in N38 ...=ABRUNDEN(1041/8,9;2)..." und " ... WENN(M38 größer N38/24;..."), kann man jetzt wenigstens entnehmen, dass Du mit 116,96 eine sogenannte Industriezeit meinst. Mehr dazu sieh mal hier: http://www.online-excel.de/excel/singsel.php?f=130.
Wenn in Excel Zeitdaten miteinander verglichen werden sollen, dann müssen diese zwingend auch das gleiche Zeitformat haben. Insofern wird jetzt richtig Deine Industriezeit mit N38/24 intern in eine Dezimalzahl gewandelt, die im Excel-Uhrzeitformat [h]:mm 116:57 entsprechen würde.
Deine weiteren Angaben stimmen nun aber nicht mehr mit dem ursprünglich von Dir eingestellten Beispiel über ein, denn 161:05 - 116:57 ergibt weder 29:52 noch 26:50.
Hinzu kommt, dass Deiner WENN()-Formel noch der SONST()-Formelteil fehlt.
Gruß Werner
.. , - ...
AW: wenn Du meinst ...
22.10.2017 20:48:59
Frank
Hallo Werner,
stimmt,die 26:50 waren ein Schreibfehler meinerseits.Auf das obige Beispiel bezogen käme da 44:07 raus,obwohl es 44:09 sein müssten.Mir ist auch klar,das es nicht die perfekte bzw regelkonforme Lösung ist. Ich wollte einfach überhaupt mal einen Lösungsansatz finden,denn ehrlich gesagt hätte ich nicht gedacht,das dies doch so eine schwierige Aufgabe zu sein scheint. Bisher gab es immer irgendeine Lösung bzw einen Lösungsansatz.
Ich glaube,ich rechne das einfach per Hand aus.
Gruß Frank
AW: nochmal: es gibt keine Zeit 116:96 owT
22.10.2017 19:03:58
...
Gruß Werner
.. , - ...
AW: nochmal: es gibt keine Zeit 116:96 owT
22.10.2017 20:59:30
Frank
Nachtrag: nachdem ich mir mal den Link angeschaut habe ist mir selbst was aufgefallen und glaube, ich weiß jetzt was du meinst. Meine 116,96 ist in der Tat keine zeit,sondern eine sog. Industriezeit. Und daher versuche ich,Äpfel mit Birnen zu multiplizieren bzw zu subtrahieren. Das ist mir bisher gar nicht aufgefallen.
Das bedeutet,ich muss diese Industriezeit zunächst einmal in eine normale Uhrzeit umwandeln,so fern dies überhaupt geht.
Liege ich mit meiner Vermutung soweit richtig?
Gruß Frank
AW: Industriezeit in Uhrzeit umwandeln
22.10.2017 21:59:16
Josef
Hallo Frank
Die Umwandlung von Industriezeit zu Uhrzeit machst du ja bereits in deiner Formel von gestern
=WENN(M38>N38/24;O6+M38-(N38/24))
Du rechnest da immer N38/24 damit findet die Umwandlung statt.
Du kannst in N38 aber auch direkt die Uhrzeit berechnen mit
=ABRUNDEN(1041/8,9;2)/24
Dann solltest du die Zelle N38 aber auch als Uhrzeit formatieren.
Der Fehler muss also anderswo liegen.
Um das herauszufinden, zeige uns doch bitte nochmals eine aktuelle Tabelle mit deiner Formel, und vermerke gleich daneben, welches Ergebnis für dich richtig ist, und wie du das per Hand errechnest.
Gruss Sepp
AW: Neuer Ansatz
22.10.2017 22:24:10
Frank
Hallo Sepp,
nachdem mir endlich der Fehler aufgefallen ist,mach ich das anders.
Als Sollzeit nehme ich nun einfach 117:00 und fertig.So habe ich jetzt folgende Formel in O38 stehen:
=WENN(M38>N38;O6+(M38-N38)) was auch soweit funktioniert.Nun muss nur noch Teil 2 in die Formel eingebunden werden. Wenn M38 kleiner ist als N38,dann soll die Differenz von O38 abgezogen werden und zu M38 hinzu addiert werden.
Gruß Frank
Teil 2 in die Formel einbinden
22.10.2017 23:45:55
Josef
Hallo Frank
Deine Beschreibung ist wieder etwas verwirrend. Die Formel steht in O38, und du willst irgendeine Differenz von O38 abziehen.
Sobald wir genau wissen, wie du diesen 2.Teil per Hand berechnest, können wir dir auch eine Formellösung erstellen. Also in deinem Interesse, zeige uns ein solches Rechenbeispiel mit allen Einzelschritten.
Gruss Sepp
AW: Teil 2 in die Formel einbinden
23.10.2017 07:35:29
Frank
Guten Morgen Sepp,
mir ist gestern Abend selbst aufgefallen,das die Formel so nicht funktioniert,da ein Zirkelbezug entsteht.
habe dann ein wenig experemntiert und mir ist folgende Teillösung eingefallen. Dazu hab ich nen Screen angehangen und die Datei mal hochgeladen
https://www.herber.de/bbs/user/117160.xls
Was jetzt noch fehlt,ist die Differenz,die bei Minusstunden von O38 abgezogen wird,zu M38 hinzu zu addieren
Gruß Frank
Userbild
AW: Teil 2 in die Formel einbinden
23.10.2017 11:56:35
Josef
Hallo Frank
Solange das Zeitkonto im Plusbereich bleibt, muss bei Minusstunden in M38 ja auch 117:00 stehen.
Die Formeln dazu siehst du weiter unten. Aber was ist, wenn das Zeitkonto negativ wird?
Und ein Zweiter Punkt.
Irgendwo solltest du trotzdem noch die elektiv geleistete Arbeitszeit darstellen also: SUMME(M7:M37)
März 2017

 MNO
38117:00117:0012:22
39
40

Formeln der Tabelle
ZelleFormel
M38=MAX(SUMME(M7:M37); N38)
O38=O6+SUMME(M7:M37)-N38


Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4.8
Gruss Sepp
AW: Teil 2 in die Formel einbinden
23.10.2017 13:26:58
Frank
Hallo Sepp,
zunächst einmal recht herzlichen Dank für deine Hilfe. Funzt alles soweit super. Nun zu deinen beiden Anmerkungen:
1. Also im Moment habe ich ganz locker über 250 Überstunden,deswegen habe ich mir über ein negatives Zeitkonto noch gar keine Gedanken gemacht. Ich denke mal, ich würde eine Zusatzschicht einlegen um das wieder auszugleichen.Dieses Tabellenblatt ist in erster Linie ja dazu gedacht,das ich einen Überblick über meine ganzen Überstunden habe.
2. Solange M38>117:00 ist werden mir die effektiven Stunden ja angezeigt. N38 ist ja nicht zwingend notwendig,deswegen habe ich deine Formel mal ein bissl abgeändert,wo ich dann meines Erachtens N38 für deine Anregung frei hätte.
Formel in M38: =MAX(SUMME(M7:M37);"117:00")
Formel in O38: =O6+SUMME(M7:M37)-"117:00"
Formel in N38: =SUMME(M7:M37)
Habs mal so getestet und funktioniert.
Gruß Frank
AW: Teil 2 in die Formel einbinden
23.10.2017 13:58:11
Josef
Hallo Frank
Es freut mich, dass ich dir helfen konnte.
Wenn du N38 die Summe bildest, kannst du die Formel in O38 vereinfachen.
Statt: =O6+SUMME(M7:M37)-"117:00"
nur noch: =O6+N38-"117:00"
Gruss Sepp
AW: Teil 2 in die Formel einbinden
23.10.2017 14:30:57
Frank
Stimmt,so gehts auch.Nochmals vielen vielen Dank :-)
AW: Feine derartige Zeit 116:96 gibt es nicht ...
19.10.2017 18:31:50
Frank
Alternativ könnte ich mir das auch so vorstellen: In Zelle O38 steht zu Beginn eines Monats nur der Übertrag aus Zelle O6. In den Zellen O7-O37 werden weiterhin automatisch die Überstunden berechnet.Und nur wenn die Sollstunden in Zelle M38 von 116,96 erreicht ist,werden alle weiteren Überstunden zu O38 dazu addiert. Im Falle von Minusstunden müssen die Sollstunden aufgestockt werden,indem die entsprechend von O38 abgezogen werden.
Lg Frank

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige