Sie haben es bei den Olympischen Spielen und bei anderen Sportveranstaltungen gesehen: Die durchschnittliche Punktzahl eines Athleten wird ermittelt, indem die höchste und die niedrigste Punktzahl ausgegeben und der Rest gemittelt wird. Möglicherweise müssen Sie ähnliche Arten von Durchschnittswerten erstellen. Beispielsweise können Sie Lehrer sein und müssen die beiden niedrigsten Zuordnungswerte ausschließen, bevor Sie einen Durchschnitt berechnen können.

Um diese Art der Mittelwertbildung durchzuführen, müssen Sie sich lediglich daran erinnern, dass ein Durchschnitt berechnet wird, indem alle Werte in einem Bereich summiert und diese Summe durch die Anzahl der Elemente in diesem Bereich dividiert wird. Die SUMME-Funktion liefert einfach die Summe, und die COUNT-Funktion kann verwendet werden, um die Anzahl der Elemente im Bereich herauszufinden. Wie kann man die beiden niedrigsten Werte im Bereich ausschließen? Sie können die SMALL-Funktion verwenden.

Betrachten Sie die folgende Formel, bei der davon ausgegangen wird, dass Sie einen angepassten Durchschnitt des Bereichs A10 ermitteln möchten: A14:

=(SUM(A10:A14)-SMALL(A10:A14,1)-SMALL(A10:A14,2))/(COUNT(A10:A14)-2)

Mit der SMALL-Funktion werden die beiden niedrigsten Werte im Bereich ermittelt, die von der Gesamtsumme des Bereichs abgezogen werden. Der resultierende Wert wird dann durch die Anzahl der Werte im Bereich geteilt.

Beachten Sie auch, dass der COUNT-Wert um 2 verringert wird, um die Tatsache zu kompensieren, dass Sie die beiden niedrigsten Werte ignorieren.

Eine andere Möglichkeit, denselben Durchschnitt zu berechnen, ist die Verwendung einer Array-Formel.

Der folgende macht den Trick:

=AVERAGE(IF(A10:A14>SMALL(A10:A14,2),A10:A14))

Da dies eine Array-Formel ist, müssen Sie sie durch Drücken von Strg + Umschalt + Eingabetaste eingeben, anstatt nur die Eingabetaste zu drücken. Diese Formel basiert weiterhin auf der Verwendung der SMALL-Funktion, verwendet jedoch auch die eigentliche AVERAGE-Funktion, um ein Ergebnis zurückzugeben. Da es sich um eine Array-Formel handelt, werden alle Werte im Array (der Bereich) untersucht und nur dann für die Verwendung im Durchschnitt berücksichtigt, wenn sie größer als der zweitkleinste Wert im Array sind.

Während die Array-Formel kürzer als die längere reguläre Formel ist, ist eine Einschränkung zu beachten: Die Array-Formel führt zu einem unerwünschten Ergebnis, wenn der zweitniedrigste Wert im Bereich oder in beide Richtungen „gebunden“ ist ein Drei-Wege-Gleichstand im niedrigsten Wert. Wenn die gemittelten Werte beispielsweise 3, 2, 10, 3 und 7 sind, ergibt die Array-Formel einen Durchschnitt von 8,5. Warum? Weil nur die Werte 10 und 7 über dem zweitniedrigsten Wert liegen und der Durchschnitt dieser beiden Werte 8,5 beträgt.

Wenn Sie die längere Formel verwenden, die zuerst oben dargestellt wurde, beträgt der zurückgegebene Durchschnitt 6,6666667, was dem Durchschnitt von 10, 3 und 7 entspricht.

Wenn Sie diese Formeln ausprobieren und sie nicht funktionieren, sollten Sie überprüfen, ob Sie das Analysis ToolPak installiert haben. Die SMALL-Funktion ist Teil dieses ToolPak.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (2050) gilt für Microsoft Excel 97, 2000, 2002 und 2003. Eine Version dieses Tipps für die Multifunktionsleistenschnittstelle von Excel (Excel 2007 und höher) finden Sie hier:

Werte von der Mittelwertbildung ausschließen.