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.
=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.“
