Mittelwert der letzten 4 Werte einer Zeile

[cosmo kramer]

Hallo Ihr,

ich bin fortgeschrittener Anfänger hinsichtlich Excel (Formeln und auch VBA)...über eine Problematik bin ich gestolpert, welche ich innerhalb meiner Tabelle nicht gelöst bekomme:

Innerhalb einer Tabelle tragen wir wöchentlich neue Werte ein, ua. möchten wir uns den Mittelwert der letzten 4 eingetragenen Werte ausgeben lassen.
Werte werden horizontal fortlaufend in Zeile geschrieben - jede Kalenderwoche je Zeile neuer Wert.
Zur Mittelwert-Ermittlung nutze ich folgende Formel - hier mal beispielhaft:

Code:

=MITTELWERT(INDEX(B2:AB2;ANZAHL2(B2:AB2)-3):AB2)

Allerdings bin ich jetzt über einen Fehler gestolpert : Sobald irgendwo leere Felder (="kein Wert in dieser KW") reinkommen, wird es falsch.
"0" einzutragen ist aber für die Mittelwert-Bildung ebenso falsch.

Wie kann ich das gelöst bekommen?
Aufgabe wäre also :
- schau in definierte Zeile (Feld 1-52)
- ignoriere leere Felder/"0" - Werte
- bilde Mittelwert aus den letzten 4 gefundenen Werten
Der eine Gedanke war, in die leeren Felder "0" einzutragen und diese per "Formel" für den Mittelwert zu ignorieren, aber schon das habe ich nicht hinbekommen...

Ich habe eine Beispiel-Tabelle angehangen.

Ich danke Euch für Eure Hilfe im Voraus.

Beste Grüße.

[neopa]

Hallo,

in AD2 folgende Formel:

PHP-Code:

=WENNFEHLER(MITTELWERT(INDEX(2:2;VERGLEICH(KALENDERWOCHE(HEUTE();21)-4;$1:$1;0)):
INDEX(2:2;VERGLEICH(KALENDERWOCHE(HEUTE();21)-1;$1:$1;0)));"") 


und nach unten kopieren.

Wobei es einfacher/kürzer wird, wenn Du Dir die aktuelle KW in einer Hilfszelle bzw. gleich deren Spaltenwert ermittelst und diese in die Formel einsetzt.

[cosmo kramer]

Hallo Werner,

danke Dir für die schnelle Bearbeitung meines Problems - allerdings passt es noch nicht :

KW // 21   //  22  //  23  //  24  //  25  //  26  //  27  //  28
     349,6           361,4           366,8    360,1   347,2   421,9
     211,8           260,5           270,1    209,5

Deine Formel ermittelt für die 2. Zeile hier 239,8 (nimmt nur die Werte aus KW 25 und 26),
meine Formel 234,2 (nimmt hier noch den Wert aus KW 20 mit???), 
aber korrekt wären 238,0...also wirklich die letzten 4 Werte der Zeile - unabhängig von der Kalenderwoche...

Hast Du noch einen weiteren Ansatz?

Liebe Grüße

[snb]

Ein UDF

PHP-Code:

Function F_snb(sn)
    For j = sn.Count To 1 Step -1
       If sn.Cells(j) > 0 Then
         y = y + sn.Cells(j)
         jj = jj + 1
        End If
        If jj = 4 Then Exit For
    Next
    F_snb = y / 4
End Function 


In AD2

PHP-Code:

=F_snb(A2:AB2) 


[cosmo kramer]

Hallo snb,

so funktioniert es...
zum Verständnis :
  y = y + sn.Cells(j)     ==> Hier werden die Werte addiert?
  If jj = 4 Then Exit For      ==> Nach 4 gefundenen Werten ist Schluss?
  F_snb = y / 4         ==> Hier die Summe "geviertelt" (= Mittelwert von 4 Werten)?


Vielen Dank - Klasse

Ich habe aber weiter auch Interesse an einer Lösung per Formel, wenn noch jemand "Bock" hat...


Danke an alle...

[snb]

Arrayformel:

PHP-Code:

=AVERAGE(INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);1));INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);2));INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);3));INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);4))) 


[Josef B]

Hallo

In AD2:

=MITTELWERT(INDEX(2:2;AGGREGAT(14;6;SPALTE(B2:AB2)/(B2:AB2<>"");4)):AB2)

Gruss Sepp

[snb]

@Joseph B

Chapeau :23: 


Ich habe meine Arrayformel überarbeitet:

PHP-Code:

=AVERAGE(INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);4)):AB2) 


[cosmo kramer]

Hallo Sepp,

=MITTELWERT(INDEX(2:2;AGGREGAT(14;6;SPALTE(B2:AB2)/(B2:AB2<>"");4)):AB2)

läuft bei mir...

Hallo snb,

=AVERAGE(INDEX(B2:AB2;1;LARGE((B2:AB2>0)*COLUMN(A:AA);4)):AB2) 

läuft bei mir nicht - gibt immer "0" aus - und ich find den Grund nicht..

Danke Euch für Eure Hilfe mal wieder - cool...