Tom muss eine Reihe nicht zusammenhängender Zellen mitteln, ausgenommen alle Zellen, die möglicherweise Nullwerte enthalten. Insbesondere muss er die Zellen A1, C1, E1, G1 und J1 mitteln, aber nur die Zellen zählen, die keine Null enthalten.

Bevor wir uns mit dem befassen, was funktioniert, schauen wir uns an, was nicht funktioniert.

Erstens funktioniert es nicht, einfach die Zellen hinzuzufügen und durch 5 zu teilen. das berücksichtigt keine Nullwerte. Zweitens funktioniert es nicht, COUNTIF im Nenner Ihrer Formel zu verwenden, wie hier gezeigt:

=(A1+C1+E1+G1+J1) / COUNTIF(A1:J1,"<>0")

Dies funktioniert nicht, da Zellen im gesamten Bereich von A1: J1 untersucht und gezählt werden, nicht nur die fünf Zellen, die im Durchschnitt berücksichtigt werden sollen. Sie könnten auch denken, Sie könnten Ihre fünf nicht zusammenhängenden Zellen auswählen, ihnen einen Namen geben und dann den Namen in Ihrer Formel verwenden. Während Sie in Excel den Namen erstellen können, gibt Folgendes Folgendes aus:

=SUM(MyCells) / COUNTIF(MyCells,"<>0")

Es scheint, dass COUNTIF nur mit einem einzigen zusammenhängenden Bereich funktioniert, sodass die nicht zusammenhängende Natur des MyCells-Bereichs die Funktion in einen Tailspin versetzt. Ein ähnliches Problem tritt auf, wenn Sie versuchen, einen nicht zusammenhängenden Bereich mit der AVERAGEIF-Funktion zu verwenden:

=AVERAGEIF(MyCells, "<>0")

Da Sie keine der Funktionen verwenden können, die Sie möglicherweise verwenden möchten, müssen Sie sich bei der Berechnung des Durchschnitts auf eine etwas längere Formel verlassen. Sie können den Durchschnitt dieser fünf Zellen berechnen, indem Sie auf Ihre Nenner ein wenig „Trick“ anwenden:

=(A1+C1+E1+G1+J1) / ((A1<>0)+(C1<>0)+(E1<>0)+(G1<>0)+(J1<>0))

Die Auswertung jeder Zelle im Nenner gibt entweder eine 1 (für True) oder eine 0 (für False) zurück, je nachdem, ob die Zelle einen Wert ungleich Null enthält oder nicht. Diese Reihe von Werten wird addiert, wodurch die erforderliche Anzahl von Zellen ungleich Null für den Nenner bereitgestellt wird.

Beachten Sie, dass es bei der Diskussion hier nur um den Nenner in der Formel ging, nicht um den Zähler. Der Grund ist einfach: Sie können alle fünf Werte in den Zähler einfügen. Nullwerte dort spielen keine Rolle.

Der einzige Ort, an dem sie eine Rolle spielen, ist der Nenner, was die Berechnung dieses Durchschnitts so schwierig macht.

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

Dieser Tipp (7845) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tipps für die ältere Menüoberfläche von Excel finden Sie hier:

Mittelwertbildung eines nicht zusammenhängenden Bereichs.