Office: (Office 2010) Mittelwert erst ab bestimmter Uhrzeit

Hallo liebe Excel-Fans :-)

Wie der Titel schon verrät, suche ich eine Formel um einen Mittelwert aus verschiedenen Zeiten zu ermitteln. Soweit so gut...
Jedoch sollte bei der Berechnung nur Zeiten ab 6:30 berücksichtigt werden.
Genauer gesagt sollen frühere Zeiten aber NICHT einfach ignoriert werden (MITTELWERTWENN), sondern mit dem Wert 6:30 in den Mittelwert miteinfließen.

ZB.
A1 6:37
A2 6:45
A3 7:03
A4 6:23
A5 6:42
A6 6:51
A7 6:18

Nun brauche ich den Mittelwert aus allen Zeiten. Die Zeiten VOR 6:30 (Zelle A4 und A7) sollen jedoch als 6:30 in die Berechnung fließen.
Hoffe meine Frage war verständlich genug :-) Wenn ich Google bemühe, komme ich immer nur auf die MITTELWERTWENN Funktion, welche mir nicht wirklich hilft, da ich so die unerwünschten Werte komplett aus dem Mittelwert streiche, was ich jedoch nicht will...

Vielen Dank für Eure Hilfe.

LG
Kami

Gesendet von meinem SM-T805 mit Tapatalk

 

Hola,

mach dir eine Hilfsspalte:

Code:

=WENN(B1<6,5/24;6,5/24;B1)

Darauf wendest du dann Mittelwert() an.

Gruß,
steve1da

 

Hola :-)

Danke für die rasche Antwort!

An eine Hilfsspalte dachte ich auch schon... Jedoch ist mir diese Lösung irgendwie zu "unelegant" ;-)
Mitarbeiter tragen ihren Arbeitsbeginn ein und Excel soll die Arbeitszeit erfassen und eben den Durchschnittlichen Arbeitsbeginn, die durchschnittliche und tatsächliche Arbeitszeit, das Gleitzeitkonto sowie das durchschnittliche Arbeitsentgelt errechnen. Ebenso gewünscht wäre das durchschnittliche Arbeitsende bei erreichen der Sollarbeitszeit erwünscht (=durchschnittl. Arbeitsbeginn + 8:15).
Es War schon kompliziert genug die Gleitzeit zu errechnen, da Excel ja ohne dem 1904 Datumformat keine negativen Zeiten rechnet. Hier musste bereits eine Hilfsspalte für die Zeit in Form von Dezimalzahlen her...

Zeiterfassung beginnt ab 6:30, Mitarbeiter tragen jedoch die Zeit ein wann sie tatsächlich ins Büro kommen (also auch schon vor 6:30).
Kann ich mir hier nicht iw mit einer doppelten WENN Funktion helfen?
=WENN(A1="";"";WENN(A1<ZEIT[06;30;00];ZEIT[06;30;00];....??

Wenn alles nichts hilft werde ich wohl eine Hilfsspalte in einem eigenen Tabellenblatt nehmen und dieses verstecken (oder so ähnlich...).

Danke für die Hilfe jedenfalls!

LG

Gesendet von meinem SM-T805 mit Tapatalk

 

Hola,

eine Beispieldatei würde wohl Sinn machen da man sicherlich einiges vereinfachen könnte.

Dafür brauchts in der Regel keine Hilfsspalte.

Leider ist auch deine Formel abgeschnitten.

Gruß,
steve1da

 

Hallo,

z.B. so:

Arbeitsblatt mit dem Namen 'Tabelle2'
*	A	B
1	06:37	06:42
2	06:45	*
3	07:03	*
4	06:23	*
5	06:42	*
6	06:51	*
7	06:18	*

Zelle	Formel
B1	{=MITTELWERT(WENN(A1:A7<6,5/24;6,5/24;A1:A7))}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

Danke für die Antworten (erneut) :-)
Ich werde versuchen eine Beispieldatei hoch zu laden... Habe das File allerdings in d. Arbeit.
Meine Idee wäre ja sowas wie =MITTELWERT(WENN(A1:A7<ZEIT[06;30;00];ZEIT[06;30;00];A1:A7))
Das ist bestimmt kompletter Blödsinn :-D Aber so in der Art: Mittelwert wenn Wert von A1 bis A7 kleiner 6:30, dann nimm 6:30, sonst nimm den Wert von A1 bis A7)... Aber das funktioniert eben nicht,...

Omg... ich denke das verwirrt nur noch mehr :-) ich werde demnächst einfach eine kurze Beispieldatei Hochladen
Wie das BoskoBiati meinte versteh ich leider nicht ganz... ?!?

Thx
LG

 

Hallo,

was habe ich eigentlich anders gemacht, als Du beschrieben hast????

 

Hey allerseits :) Vielen Dank für die Mühen...
Und Danke Edgar, für die neuerliche Erkläreung... ich habe es nun tatsächlich so versucht mit der Verschachtelten WENN Funktion...
da kommt aber nur #WERT! raus
Habe Euch einfach mal meine Excel als Anhang mitgeschickt...
das was ich eben nicht hinbekommen ist der Wert in Zelle C36, der "Durschnittliche Arbeitsbeginn".
Hier sollen die Werte nur berücksichtigt werden WENN die betroffenen Zellen überhaupt befüllt sind und erst ab einer Zeit von 6:30.
Wenn jemand früher ins Büro kommt - Pech... wird nicht gezählt... Zeiterfassung zählt nun mal erst ab 06:30...

Ich bin für jede Hilfe dankbar :-)

Bin jetzt dann auf Urlaub und komme übrigens erst am 10.10.2016 wieder retour...

LG :)

 

Hallo,

warum nimmst Du nicht die Formeln, die man Dir zeigt? Die funktionieren wenigstens!!!!!

Arbeitsblatt mit dem Namen '09-2016'
*	B	C	D	E	F	G
2	Arbeitszeiten September 2016
3	Datum	Arbeitsbeginn	Arbeitsende	Tatsächliche Arbeitszeit	Differenz Gleitzeit Dezimal	Differenz Gleitzeit hh:mm
4	01.09.2016	06:31	13:45	07:14	-0,0424	-01:01
5	02.09.2016	06:00	15:30	09:00	0,0313	00:45
6	03.09.2016	*	*	*	*	*
7	04.09.2016	*	*	*	*	*
8	05.09.2016	06:05	13:15	06:45	-0,0625	-01:30
9	06.09.2016	06:35	15:00	08:25	0,0069	00:10
34	*	*	*	*	*	*
35	*	Durchschnittl. Arbeitsbeginn	Durchschnittl. Arbeitsende	Durchschnittl. Arbeitszeit	Summe Gleitzeit Dezimal	Summe Gleitzeit
36	*	06:30	14:22	07:51	-0,06667	-01:36

Zelle	Formel
E4	=WENN((C4="")+(D4="");"";D4-MAX(C4;6,5/24))
F4	=WENN((C4="")+(D4="");"";E4-8,25/24)
G4	=WENN(E4="";"";WENN(E4<8,25/24;"-";"")&TEXT(ABS(8,25/24-E4);"hh:mm"))
C36	{=MITTELWERT(WENN(C4:C34<6,5/24;6,5/24;C4:C34))}

Achtung, Matrixformel enthalten!Die geschweiften Klammern{} werden nicht eingegeben.Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.

Diese Tabelle wurde mit Tab2Html (v2.4.1) erstellt. ©Gerd alias Bamberg

 

Mensch BoskoBiati / Edgar....
Vielen Vielen DANK !!! Ich war irgendwie zu blöd, die Matrixformel einzusetzen.
Und die Lösung von E4, F4 und G4 find ich viel eleganter als bei mir ^^ Ich sehe schon ich brauche noch etwas Übung darin :))
DANKE Dir für die Hilfe !! Tabelle funktioniert suuuper.
Kann ich Dich (weil Du scheinbar so schlau bist) noch fragen, ob es eine Möglichkeit gibt die Durchschnittswerte immer aktuell zu halten?
Also nur die Werte berechnen die auch eingetragen sind. Nicht immer komplett von C4 bis runter C34 ,... sondern nur jede Tage die bereits befüllt sind?

Okay... und jetzt duck ich mich ganz klein weg und verschwinde mal auf Urlaub.
Office-Hilfe.com ist Spitze - vielen Danke für die immer rasche Unterstützung !!!

LG :-D :-)

PS: So sieht es in der Arbeit aus - und so hätte ich es gerne in einer Excel Tabelle :)

Datum | Kommen | Gehen | Ist | Soll| GLAZ | Saldo
.
.
.
13 Di | 06:31 | 15:25 | 8.39 | 8.15 | 0.39 | 20.58
14 Mi | 06:39 | 16:04 | 9.10 | 8.15 | 1.10 | 22.08
15 Do | 06:38 | 15:35 | 8.42 | 8.15 | 0.42 | 22.50
---------------------------------------------------------------------------------
Summe aus Vormonat: 17.16
Summe aus lfd.Monat: 5.34
Übertrag Folgemonat: 22.50
Durchschn. Kommen: 6.43
Sollzeit erreicht: 14.43
Durchschn. Gehen: 15.22

 

Hallo,

ungetestet:

Code:

=MITTELWERT(WENN((C4:C34<6,5/24)*(C4:C34<>"");6,5/24;C4:C34))