Excel Mittelwert Zeiten vor 24 Uhr und nach 24 Uhr
Hallo Mein Problem lässt sich relativ schneller erklären. Ich habe in Excel eine Spalte mit lauter Uhrzeiten die teilweise nach 24 Uhr und teilweise vor 24 Uhr liegen. z.B. 22:30 ; 23:10 ; 00:10 ; 1:50 Jetzt will ich von diesen Werten den Mittelwert bilden. Jedoch ist das Problem, dasExcel das zwar macht ein Mittelwert bilden, aber nicht so wie ich ihn gerne haben will. Excel gibt mit der Formel Mittelwert eine Zeit von 11:55 aus.Wenn man annimmt es wären Arbeitszeiten wo ich zum Beispiel angenfangen habe zu Arbeiten, also den einen Tag um 22:30 und den anderen Tag um 1:50, dann möchte ich, das Excel mir einen Mittelwert von um die 0 Uhr irgendwo ausgibt. Hoffe ihr versteht was ich meine .
2 Antworten
Beziehe in deiner Formel bzw. in deinen Zeiten das Datum mit ein. Sonst nimmt er den selbigen Tag, was ja auch logisch wäre. Gerade erst in einer meiner vorhergehenden Beiträge / Antworten wurde so was ähnliches wie man Datum und Zeit verknüpft, behandelt.
http://www.gutefrage.net/frage/datumsbezogene-formatierung#answer118426492
Oh auch noch über mehrere Tage hinweg. Aber das geht auch.
Nachfolgend hab ich mal ein Excelbeispiel zum Download. In A Datum und B die Zeit. Zur Berechnung wird eine Hilfsspalte E benötigt. In der Datum A und Zeit B durch eine Formel verknüpft sind. Falls diese Hilfsspalte stört in der Tabelle kann man diese natürlich auf ein anderes Tabellenblatt unterbringen. Dann fällt das nicht so auf. In C10 wird mit dann mit Hilfe dieser Hilfsspalte der Mittelwert berechnet.
Man kann das natürlich auch ganz anders machen. Man kann von Hand in einer Zelle Datum und Zeit schreiben. Zwischen Datum und Zeit wird ein Leerzeichen eingefügt. Und die Zelle formatiert mit
TT.MM.JJJJ hh:mm
Dies ist in Spalte G zu sehen. In G10 dann eine ganz einfache Mittelwert-Formel für die Zellen G1 bis G4. Bei dieser Methode wird dann keine Hilfsspalte gebraucht.
Download Excelbeispiel:
http://www.dateiupload.net/download.php?file=71133ff8e5a466b1cfec16c2bf4bf594
Du kannst A1+B1 auch in komplexeren Formeln ohne Hilfsspalte verknüpfen.
Und im 1904-Datumsformat müsste auch die Mittelwertbildung (incl. negative Zeiten=frühere Ankunft) problemlos durchführen lassen, sofern man sich nur auf die auswertende Spalte (Verspätung/"Verfrühung") bezieht.
Kann mich da aber heute nicht mehr reinvertiefen!
Gilt auch für OO/LO
Im xl-1900er Format müsste man wohl erst in Text und dann wieder zu Wert umwandeln.
Demo:
=Wert(Text(A1+B1;"00000,00000000")
Die Zahl der Stellen reicht aus, um auch Sekunden noch genau wiederzugeben. Und die 5 Vorkomma-Stellen für die Tage (Datum) ist ebenfalls genau angemessen.
Im Zweifelsfall noch drei Nachkomma-Nullen mehr, da sollte dann die Genauigkeit auch Tausendstel Sekunden noch ausreichend genau wiedergeeben
Durch die doppelte Umwandlung "vergisst" xl, dass es sich ursprünglich um eine Zeit handelte und erlaubt auch negative Werte. Die lassen sich allerdings wiederum nur als Zeit-Text wiedergeben.
@ Iamiam und alle Interessierten:
Ich fand heute früh eine "schliessende Klammer", diese hier: )
Da ich z.Zt. keine weitere Verwendung dafür habe, spende ich sie als krönenden Abschluss der Formel von Iamiam.
Gruß aus Berlin
Dann musst du erstmal festlegen, wo die Tagesgrenze verlaufen soll, wenn sie nicht bei 0 Uhr liegt. Nehmen wir mal an, Excel soll alle Zeiten, die nach 6:00 Uhr liegen, zum heutigen Tag zählen und alle, die vor 6:00 Uhr liegen, zum gestrigen.
Mach dir eine Hilfsspalte mit folgender Formel:
=WENN(A1<0,25;A1+1;A1)
wobei in Spalte A die Uhrzeiten stehen. Diese Formel kopierst du nach unten. Dann kannst du den Mittelwert der Hilfsspalte ausrechnen.
Die Festlegung 6:00 Uhr bis 6:00 Uhr ist auf jeden Fall wichtig.
Ich hätte dann die Formeln anders (aber nicht besser) erstellt, nämlich:
Für die Hilfsspalte:
=REST(A1-0,25;1)
und nach unten kopieren.
Und dann für den Mittelwert:
=MITTELWERT(B1:B100)+0,25
OHNE Hilfsspalte wäre das dann:
{=(MITTELWERT(WENN(A1:A100="";"";REST((A1:A100)-0,25;1)))+0,25)}
ACHTUNG!
Das ist eine Matrixformel.
Das bedeutet: Die geschweiften Klammern {} NICHT mit eingeben, sondern die Eingabe der Formel NICHT mit ENTER abschließen, sondern mit:
STRG & SHIFT & ENTER (alle drei gleichzeitig).
DAS erzeugt die { } und macht die Formel zu einer Matrixformel.
Ich verstehe was du meinst, aber ich glaube das ist schwer umzusetzen für mein Tabelle. Dann habe ich über alles eine einzige Zelle C10 wo ich die Mittlere Zeit ausgegeben haben möchte. Wie müsste ich vorgehen?
A1: 11.05.13
A2: 12.05.13
A3: 13.05.13
A4: 14.05.13
B1: 22:30
B2: 23:10
B3: 0:10
B4: 1:50