☰

Lösung 1

tag-icon • tag-icon microsoft-excel worksheet-function
Lösung 1

Ich habe eine Reihe von Werten und möchte den Durchschnitt der ersten drei Werte ermitteln, leere Zellen nicht mitzählen. Beispiel:

  ABCDEFG
 12 leer 8 leer 7 9 4

Der Durchschnitt der ersten 3 nicht leeren Zellen sollte 9 sein, aber ich kann keine Formel finden, die das für mich berechnet. Kann mir jemand helfen?

Antwort1

InA2eingeben:

=IF(A1="",0,1)

und inB2eingeben:

=IF(OR(B1="",SUM($A$2:A2)=3),0,1)

und kopieren Sie es. Geben Sie dann in eine andere Zelle ein:

=SUMPRODUCT((1:1)*(2:2))/3

Bildbeschreibung hier eingeben

Antwort2

Sie suchen denDURCHSCHNITTFunktion. Nach der FormelSyntax und Verwendungfür die AVERAGE-Funktion von Excel:

Wenn ein Bereichs- oder Zellbezugsargument Text, logische Werte,oder leere Zellen, werden diese Werte ignoriert; Zellen mit dem Wert Null werden jedoch einbezogen.

Die Formel =AVERAGE(A1:E1)gibt zurück 9:

Bildbeschreibung hier eingeben

Antwort3

Lösung 1

Brute Force: Der gewünschte Durchschnitt kann wie folgt berechnet werden:

=WENN(ANZAHL($A1:$C1)=3, DURCHSCHNITT($A1:$C1),
 WENN(ANZAHL($A1:$D1)=3, DURCHSCHNITT($A1:$D1),
 WENN(ANZAHL($A1:$E1)=3, DURCHSCHNITT($A1:$E1),
 WENN(ANZAHL($A1:$F1)=3, DURCHSCHNITT($A1:$F1),
 WENN(ANZAHL($A1:$G1)=3, DURCHSCHNITT($A1:$G1), "oops")))))

(Einzeilige Version:)
 =WENN(ANZAHL($A1:$C1)=3, DURCHSCHNITT($A1:$C1), WENN(ANZAHL($A1:$D1)=3, DURCHSCHNITT($A1:$D1), WENN(ANZAHL($A1:$E1)=3, DURCHSCHNITT($A1:$E1), WENN(ANZAHL($A1:$F1)=3, DURCHSCHNITT($A1:$F1), WENN(ANZAHL($A1:$G1)=3, DURCHSCHNITT($A1:$G1),"oops")))))

Dabei wird nach der kürzesten anfänglichen Teilmenge der Zeile gesucht, die drei Zahlen enthält, und daraus der Durchschnitt berechnet. Dies ist bei einer großen Anzahl von Spalten nicht nur umständlich, sondern kann tatsächlich ziemlich schnell fehlschlagen, da (glaube ich) IF()eine ziemlich niedrige Verschachtelungsgrenze vorliegt.

Lösung 1.9

Finesse.

Ich nenne dies „1.9“, weil es noch nicht fertig ist. Der Ausdruck

SMALL(IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),""), {1,2,3})

(verwendet in einer Matrixformel) findet die Spaltennummern der ersten drei Zahlen in Zeile 1. Die

IF(ISNUMBER($A1:$G1),COLUMN($A1:$G1),"")

Teil wird ausgewertet als

IF( {TRUE,FALSE,TRUE,FALSE,TRUE,TRUE,TRUE}, {1,2,3,4,5,6,7}, "" )

was sich reduziert auf

{ 1, "", 3, "", 5, 6, 7 }

und SMALL(…, {1,2,3})von dem oben genannten ergibt { 1, 3, 5 }. Es sollte einfach sein zu sagen

=AVERAGE(INDEX($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

oder

=AVERAGE(OFFSET($A7:$G7, 1, SMALL(IF(ISNUMBER($A7:$G7),COLUMN($A7:$G7),""),{1,2,3})))

aber sie geben entweder den Wert A1(12) oder einen Fehler ( #DIV/0!oder #REF!) zurück. Ich kann nicht herausfinden, wie ich das beenden soll.

verwandte Informationen