Wie kann ich den Durchschnitt der Top 5 Werte einer Liste berechnen?

Vor Kurzem hatten wir folgendes Problem: Ein Kunde wollte wissen wie man den Durchschnitt aus Werten einer langen Liste in Excel berechnet. Zuerst dachten wir, die Lösung wäre einfach: =MITTELWERT(A2:A100). Leider wollte der Kunde aber den Durchschnitt der Top 5 Werte, nicht den Durchschnitt der ganzen Liste.

MITTELWERT und KGRÖSSTE Funktionen

Um dies zu errechnen müssen wir etwas kreativ werden und eine zweite Funktion einbinden. Wir brauchen die KGRÖSSTE Funktion um die Top 5 Werte zu bestimmen.

durchschnitt in excel

=MITTELWERT(KGRÖSSTE(A2:A13;{1;2;3;4;5}))

In unserem Beispiel haben wir verschiedene Werte in den Zellen A2 bis A13. Wenn sie nur die Funktion MITTELWERT alleine verwenden, wird der Durchschnitt von allen Werten berechnet. Da wir aber nur den Durchschnitt der 5 grössten Zahlen berechnen wollen, müssen wir die KGRÖSSTE Funktion einsetzen.

Durchschnitt der 5 kleinsten Werte

Ähnlich wie im Beispiel oben, können wir auch den Mittelwert der 5 niedrigsten Werte berechnen. Einfach KGRÖSSTE durch KKLEINSTE ersetzen:

=MITTELWERT(KKLEINSTE(A2:A13;{1;2;3;4;5})) 

Durchschnitt der 3 kleinsten Werte

Hier müssen wir einfach die letzten zwei Zahlen (4 und 5) am Ende entfernen:

=MITTELWERT(KKLEINSTE(A2:A13;{1;2;3})) 

Profi-Tipp: Dynamische Listen

Unsere Formelkombination hilft uns bis jetzt nur bei Listen mit 5 oder mehr Werten. Wenn es sich aber um eine Liste die sich ändert handelt, z.B. wenn nur noch 3 Werte vorhanden sind, dann gibt es ein Problem. Unsere Funktion resultiert in einer #ZAHL! Fehlermeldung.

Abhilfe schafft da eine simple WENN/DANN Abfrage:

=WENN(ANZAHL(A2:A13)<6;MITTELWERT(A2:A13);MITTELWERT(KGRÖSSTE(A2:A13;{1;2;3;4;5})))

„Wenn die Liste A2 bis A13 weniger als 6 Werte enthält, wird der Mittelwert der gesamten Liste berechnet, ansonsten wird die Funktion von oben angewendet und es wird der Mittelwert der Top 5 Werte berechnet.“

1 Comment

  • Hallo,
    mich würde interessieren wie man bei einer Liste die ‚0‘ Werte herausfiltern kann bevor der Mittelwert gebildet wird.
    Ich hab z.B. Monatsergebnisse von Jan-Dez. Allerdings steht im Sep-Dez immer noch ‚0‘ drin.
    Wenn ich also ‚Mittelwert‘ über das ganze Jahr mache, dann wird das durch die Nullen falsch.

    Gibt es hier auch eine einfache Lösung ?

    mfg Peter

Frage oder Kommentar zu diesem Artikel?