Office: Formel über zwei Spalten mit Mittelwert, KGrößten, Max,...

Hallo zusammen,

ich habe folgendes Problem:

Mein Excel Sheet verfügt über zwei Spalten:
1. Spalte enthalten 10000 Werte von 1-1000.
2. Spalte enthalten eine Größeneinteilung: Bspw. Größenklasse 1, Größenklasse 2, Größenklasse 3

Ich würde gerne in einer Zelle den Mittelwert aus den 5 größten Werten jeder Größenklasse definieren. Leider habe ich das per Formel noch nicht hinbekommen. Die Formel sollte so gestaltet sein, dass man die Eintragungen nicht erst sortieren muss....meine Idee war sowas wie:

MITTELWERTWENN(a1:a100;"GK 1";Summe(KGRÖSSTE(b1:b1000; 5)))...passt jetzt nicht zum angehängten Sheet!!!!

Im Attachment wird vielliecht deutlicher, was genau ich meine.

Vielleicht kann mir jemand auf die Sprünge helfen.

Vielen Dank vorab!

 

Hallo!

=MITTELWERT(KGRÖSSTE(WENN(B2:B99="GK 1";A2:A99);ZEILE(1:5)))

Achtung Matrixformel: Die Eingabe der Formel mit der Tastenkombination Strg+Shift+Enter abschließen!

 

Hallo,

vielen Dank für die superschnelle Antwort. Die Formel haut bestens hin: Nur eine Frage hätte ich noch - was genau muss ich ändern, wenn ich bspw. den Mittelwert der 8größten oder 7 größten Werte haben möchte.

Was ich mir als Endlösung auch vorstellen könnte: Das Sheet wird permanent gepflegt und ich berechne immer die Anzahl der TOP 25% die in einer Zelle stehen. Jetzt hätte ich gerne den Mittelwert dieser TOP 25%. Könnte ich das durch einen Bezug in die Formel mit einbauen?

 

Hallo!

=MITTELWERT(KGRÖSSTE(WENN(B2:B99="GK 1";A2:A99);ZEILE(1:5)))
(den roten Wert anpassen)

Wie oft eine Klasse vorkommt, kannst du mit ZÄHLENWENN bestimmen.
1/4 davon (gerundet) sind 25%.
Auch diesen Wert kannst du in die Formel einbauen:

=MITTELWERT(KGRÖSSTE(WENN(B2:B99="GK 1";A2:A99);ZEILE(INDIREKT("1:"&x))))

 

Vielen vielen Dank!

Ist das geil - ich freu mich;))