Thema: Mittelwertwenn - ich kapiere es nicht :-(

Liebe Excelianer,

ich bekomme die Mittelwertwenn-Funktion nicht hin.

Ich habe auf einem Tabellenblatt eine Zeile mit Datumswerten. Das sind jeweils die Monatsersten über mehrere Jahre.
Darunter stehen Werte. Jetzt möchte ich mittels der Funktion "Mittelwertwenn" die Mittelwerte für die jeweiligen Jahre ermitteln.
Der Knackpunkt ist für mich, dass in der Zeile mit dem Datum ein Tag steht und ich aber nach der Jahreszahl auswerten muss.
Irgendwie komme ich da ohne Hilfszeile nicht hin. Eine andere Lösung ohne Hilfszeile habe ich bereits, möchte aber die Mittelwertwenn-Funktion verstehen lernen.

Ich würde mich freuen, wenn mir da jemand von euch auf die Sprünge helfen könnte.

https://www.herber.de/bbs/user/169082.xlsx

Viele Grüße

Kulo

Hola,
Ich habe mir die Datei nicht angesehen, aber Mittelwertwenn() kann aus einem Datum das Jahr nicht direkt auslesen.
Entweder du holst dir das Jahr mit Jahr() in Hilfszelle, oder mit Mittelwertwenns(). Hier muss du dann >=dem ersten und <= dem letzten Tag des Jahres definieren.
Gruß,
steve1da

Hallo steve1da,

vielen Dank für deinen Beitrag.

habe jetzt die Formel:

=MITTELWERTWENNS($A$7:$EN$7;$A$6:$EN$6;">="&DATUM(JAHR(A6);1;1);$A$6:$EN$6;"<="&DATUM(JAHR(A6);12;1))

Es läuft also. :-) Vielen Dank.

Gibt es noch andere Möglichkeiten?

https://www.herber.de/bbs/user/169085.xlsx

Es ginge noch mit Summenprodukt(), das kann direkt das Jahr auslesen. Die Formel wird aber länger und arbeitet nicht so schnell wie Mittelwertwenns(). Einfacher wird es, wenn du das Jahr in einer Hilfszeile ermittelst.

https://www.herber.de/bbs/user/169084.xlsx

Hallo

Oder als Alternative mit PQ.

let

in

   Table.TransformColumnTypes(Table.Group(Table.Transpose(Excel.CurrentWorkbook(){[Name="tblM1"]}[Content]),{"Column1"}, 

   {{"Mittelwert", each List.Average([Column3]), type number}}),{{"Mittelwert", Currency.Type}})

Oder so

let

in

    Table.PromoteHeaders(Table.Transpose(Table.TransformColumnTypes(Table.Group(Table.Transpose(Excel.CurrentWorkbook(){[Name="tblM1"]}[Content]),{"Column1"}, 

   {{"Mittelwert", each List.Average([Column3]), type number}}),{{"Mittelwert", Currency.Type}})),[PromoteAllScalars=true])

Hallo Eifeljoi,

vielen Dank für Deinen Beitrag.

PQ ist für mich ein Buch mit sieben Siegeln. Da muss ich mich mal ransetzen.
Ich hab da schon die tollsten Sachen gesehen. Aber irgendwie übersteigt das meinen Horizont.

Trotzdem vielen Dank für deine Mühe.

Viele Grüße

Kulo

Hallo Onur,

vielen Dank für die Formeln.
Darin sind aber wieder Verweise auf die Hilfszeile vorhanden.

Ich habe jetzt

=MITTELWERTWENNS($A$7:$EN$7;$A$6:$EN$6;">="&DATUM(JAHR(A6);1;1);$A$6:$EN$6;"<="&DATUM(JAHR(A6);12;1))

genommen. Da komme ich ohne die Hilszeile aus. Aber irgendwie -hm- sieht das so komisch aus.
Gibt's da noch was anderes?

Habe ich dir eben gepostet.

Hallo Onur, hallo Steve,

mit der letzten Formel komme ich ganz gut zurecht.
A$15 ist ja wieder ein Wert aus einer extra berechneten Zeile.

Wenn ich aber die Formel aus a15 in die neue Formel einsetze, kann ich die Hilszeile vermeiden.

Aus =MITTELWERT(WENN(JAHR($A$6:$EN$6)=A$15;$A$7:$EN$7;""))
wird =MITTELWERT(WENN(JAHR($A$6:$EN$6)=JAHR(A6)+SPALTE()-1;$A$7:$EN$7;""))

Hat dies irgendwelche Nachteile oder ist das mehr oder weniger nur kosmetisch? Rechnet Excel an einer verschachtelten Formel länger/angestrengter als unter Verwendung von Hilfsspalten?

Bei der Mittelwertfunktion habe ich auf alle Fälle jetzt gelernt, wie man den Bedingungswert variabel macht und mehrere Bedingungen verknüpft. Ich hoffe nur, dass das beim nächsten mal immer noch im Hirn ist. :-)

Vielen Dank nochmal an euch und

ein schönes Wochenende.

Kulo

Aber wozu? So eine Datenreihe ohne Kopfzeile ist doch nichtssagend. Eine Kopfzeile (Titelzeile) wäre schon hilfreich.

DAS war doch dein Ziel:
https://www.herber.de/bbs/user/169087.xlsx

Hallo Onur,

ja, du hast recht. Natürlich ist die Kopfzeile da, und dann kann ich sie ja auch nutzen.
Ich glaube, ich denke da viel zu kompliziert.

Ich habe Excel 365. Ich denke immer, ich sollte zuerst versuchen, meine Formeln für eine ältere Version zu erstellen, damit man auch mal was weitergeben kann.
Leider kann ich die Formeln nicht auf Kompatibilität prüfen, da ich keine ältere Excelversion zur Verfügung habe. Ich mach mich da echt verrückt.
Aber eigentlich ist es egal, mich noch nie jemand nach Excelkenntnissen gefragt... Also sollte ich mir nicht so viel Gedanken machen.

Für meine privaten Bereiche komme ich ganz gut zurecht. Wenn ich dann doch mal einen Knoten im Hirn habe, wie heute, dann weiß ich, dass es Menschen wie euch gibt, die einem den richtigen Denkanstoß geben.

Mein Anliegen mit dem Mittelwertwenn ist gelöst. Ich habe wieder etwas dabei gelernt.

Vielen Dank für deine Zeit und deine Unterstützung, auch an all die anderen hier im Forum.

Viele Grüße

Kulo