Office: (Office 365) Median mit mehreren Bedingungen.

Hallo zusammen,

ich hoffe mir kann jemand weiterhelfen.
In einer Excel-Datei habe ich für ein gesamtes Jahr die Arbeitszeiten pro Tag aufgeführt. Jetzt möchte ich den Median der Monate April bis Oktober jeweils für Montag, Dienstag, Mittwoch, etc. errechnen.
In Spalte B stehen die Zahlen der Monate; 1=Januar, etc
In Spalte C stehen die Wochentage; Mo, Di, Mi, etc.
In Spalte E stehen die Arbeitszeiten pro Tag

R3 = Mo für Montag

Dazu habe ich folgende Formel erstellt:
=MEDIAN(WENN(UND(C4:C368=R3;B4:B368>=4;B4:B368<=10);E4:E368))

Es funktioniert aber leider nicht. Ich habe auch schon ">="&4 (ebenso bei der 10) ausprobiert und ich habe auch schon STRG+Alt+Enter ausprobiert.
Habt ihr eine Idee, was ich falsch mache und wie es richtig geht?

Vielen lieben Dank und viele Grüße!

 

Hola,
das schreibt man nicht mit UND:
=MEDIAN(WENN((C4:C368=R3)*(B4:B368>=4)*(B4:B368<=10);E4:E368))
Gruß,
steve1da

 

Super, vielen lieben Dank, es funktioniert :-)

 

Jetzt habe ich das nächste Problem:
Wenn ich nun das gleiche für Januar bis März und Oktober bis Dezember machen möchte, habe ich folgende Formel eingegeben:
=MEDIAN(WENN((C4:C368=R3)*(B4:B368<=3)*(B4:B368>=10);E4:E368))
das funktioniert nicht. Dann habe ich das hier eingegeben:
=MEDIAN(WENN((C4:C368=R3)*(B4:B368>=1)*(B4:B368<=3)*(B4:B368>=10)*(B4:B368<=12);E4:E368))
weil ich dachte, dass das Problem vielleicht mit der 0 zusammenhängt, aber auch das funktioniert nicht.

Habt ihr noch Ideen?

Vielen lieben Dank und viele Grüße.

 

Das kann auch nicht klappen. Das sind alles UND Abfragen und aktuell suchst du eine Monatszahl die gleichzeitig kleiner als 3, größer als 10 und in der zweiten Formel größer als 1, kleiner als 3, größer als 10 und kleiner als 12 ist.
Welche Zahl soll denn das sein?

 

Dass * wie UND funktioniert war mir nicht bewusst. Mit einer ODER-Funktion hat es jetzt geklappt.
Vielen Dank!

 

Hat jemand auch noch eine Idee, wie ich von den gleichen Zahlen von März bis September einen Mittelwert bilden kann. Hier scheint das hier nicht zu funktionieren:
=MITTELWERTWENNS(E4:E368;C4:C368;R3;B4:B368;ODER((>=1)*(<=3);(>=10)*(<=12)))

Vielen Dank im Voraus.

 

Hi,

weder ODER() noch UND() funktionieren in solchen Formeln. Da muss man dann schon den Trick mit * bzw. + anwenden.
Und rechnen kann man in den ganzen xxxWENN(s)-Formeln auch nicht.
Da muss man dann schon =MITTELWERT(WENN(xxx)) (bei altem Excel als Matrix-Formel mit Strg-Shift-Enter abschließen) oder SUMMENPRODUKT(xxx) verwenden:

{=MITTELWERT(WENN((C4:C368=R3)*((B4:B368>=1)*(B4:B368<=3)+(B4:B368>=10)*(B4:B368<=12));E4:E368))}

=SUMMENPRODUKT((C4:C368=R3)*((B4:B368>=1)*(B4:B368<=3)+(B4:B368>=10)*(B4:B368<=12))*E4:E368)/SUMMENPRODUKT((C4:C368=R3)*((B4:B368>=1)*(B4:B368<=3)+(B4:B368>=10)*(B4:B368<=12)))

=LET(b;B4:B368;MITTELWERT(WENN((C4:C368*((b>=1)*(b<=3)+(b>=10)*(b<=12));E4:E368)))

=LET(b;B4:B368;x;(C4:C368=R3)*((b>=1)*(b<=3)+(b>=10)*(b<=12));SUMMENPRODUKT(x*E4:E368)/SUMMENPRODUKT(x))

Alles ungetestet....

Übrigens: theoretisch müsste (b<=3)*(b>=10) auch schon ausreichen. Oder hast du einen Monat 0 oder 13 in deiner Tabelle stehen?

 

Hallo,

zumindest für letzteres (Mittelwertermittlung) würde ich eine Pivotauswertung bevorzugen. Evtl. hat man in XL365 damit auch die Möglichkeit den Median zu ermitteln. In meiner älteren XL-Version (XL 2016) ist das leider noch nicht der Fall. Aber mit Power Query ist da auch eine Median-Auswertung möglich.

 

(b-3<8) oder (b-3>7) (für das Komplement) reduziert die Sache um einen Term
_______________________________________

Mit der in [LAMBDA] schnelles Summen-Pivot genannten Alleskönner-Highspeed-Superformel kannst Du in Deinem 365 eine einzige Formel anwenden:

=PVT(HSTAPELN(TEXT(B4:B368;"00")&"|"&C4:C368;E4:E368);{1.4.10})

Die 1 zeigt Dir den Bezeichner an: 01|Mo z.B.
Die 4 zeigt Dir dabei die Anzahl 4 oder 5 Wochentage pro Monat an - als Kontrolle für den Median, der bei 4 gemittelt und bei 5 treffgenau ist.
Die 10 gibt den Median des Bezeichners zurück.

Mit FILTER drumrum blendest Du dann noch ungewünschte Monate aus.

 

Vielen lieben Dank für die vielen guten Antworten!
Es hat tatsächlich geklappt mit:
=MITTELWERT(WENN((C4:C368=R3)*((B4:B368>=1)*(B4:B368<=3)+(B4:B368>=10)*(B4:B368<=12));E4:E368))

Die anderen werde ich auch noch ausprobieren. Gerade die "Alleskönner-Highspeed-Superformel" klingt interessant :-)

 

ungetestet kürzbar auf
=MITTELWERT(WENN((C4:C368=R3)*(REST(B4:B368+2;12)<6);E4:E368))

 

=MITTELWERT(WENN((C4:C368=R3)*(REST(B4:B368+2;12)<6);E4:E368))
getestet und funktioniert gut! :-) Vielen Dank! :-)