Moyenne sur les 50 dernières lignes non vide

[candido57]

Bonjour,

J’ai une série ( environ 500) de chiffres dans une colonne et je voudrais faire la moyenne des 50 dernières cellules non vide.
est-ce possible ?

Merci

 

[hbenalia]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour à tous,

En supposant E la colonne contenant ces valeurs, la formule est:

=MOYENNE(DECALER($E$1;EQUIV(-1;$E:$E;-1)-1;0;-50))

Cordialement

 

[candido57]

Re : Moyenne sur les 50 dernières lignes non vide

ça fonctionne pas


Il faudrait prendre la colonne exemple W >> rechercher de W5 à W300

1,2
1,25

2,36

2,36
5,25
ect.. il y a des cellules vide mais il ne faut pas les prendre en compte, prendre uniquement les 50 cellules pleine

 

[Staple1600]

Re : Moyenne sur les 50 dernières lignes non vide

Re, Bonsoir hbenalia

candido57
Si la formule de hbenalia fonctionne parfaitement, il suffit d'adapter comme il se doit ... (test OK sur mon PC)

=MOYENNE(DECALER($W$5;EQUIV(-1;$W5:$W300;-1)-1;0;-50))

EDITION: J'ai oublié de précisé que j'avais testé avec aucune cellule vide dans la plage.
D'ou le fait que je disais test ok sur mon PC

 

[ROGER2327]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour à tous.

Chez moi, test pas très convaincant...​

ROGER2327
#6439

Dimanche 15 Gueules 140 (Alice au Pays des Merveilles - fête Suprême Tierce)
21 Pluviôse An CCXXI, 1,5122h - thlaspi
2013-W06-6T03:37:45Z

 

[mapomme]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour candido57, à tous,

Un essai avec un nom dynamique (MaListe) et une formule matricielle à valider par Ctrl+Maj+Entrée.

Et un autre essai avec une fonction VBA: MoyN(Liste , N ) où Liste est la zone de valeurs et N le nombre de valeurs non vides à moyenner à partir du bas de la zone. La fonction retourne #N/A s'il y a moins de N valeurs dans la liste.

En excel 2003, la formule matricielle ne fonctionne pas car le nombre de niveaux d'imbrication est trop élévé (je suis sans doute parti sur un truc bien trop tarabiscoté!)

Formule matricielle Excel 2007+ :

=MOYENNE(DECALER(MaListe;LIGNES(MaListe)-EQUIV(50;SOUS.TOTAL(2;DECALER(DECALER(MaListe;LIGNES(MaListe)-LIGNE(INDIRECT("1:" & LIGNES(MaListe)));0;1;1);0;0;LIGNE(INDIRECT("1:" & LIGNES(MaListe)));1));0);0;EQUIV(50;SOUS.TOTAL(2;DECALER(DECALER(MaListe;LIGNES(MaListe)-LIGNE(INDIRECT("1:" & LIGNES(MaListe)));0;1;1);0;0;LIGNE(INDIRECT("1:" & LIGNES(MaListe)));1));0);1))

le code de la fonction (Excel 2003+):

Public Function MoyN(Liste As Range, N As Long)
Dim T, i As Long, k As Long, s
MoyN = CVErr(xlErrNA)
T = Liste.Columns(1).Value
For i = UBound(T) To 1 Step -1
  If T(i, 1) <> "" Then
    If IsNumeric(T(i, 1)) Then
      k = k + 1
      s = s + T(i, 1)
      If k = N Then
        MoyN = s / N
        Exit Function
      End If
    End If
  End If
Next i
End Function

nb: les formules proposées précédemment dans les autres réponses semblent ne pas fonctionner si des valeurs négatives sont présentes dans la liste.

 

[hbenalia]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour à tous,

La formule proposée plus haut ne tient pas compte des cellules vides... Autres solutions sont proposées dans le fichier attaché...

Cordialement

 

[job75]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour à tous,

Annulé car erroné, désolé.

A+

 

[job75]

Re : Moyenne sur les 50 dernières lignes non vide

Re,

Bon maintenant c'est bon, voir fichier joint.

Formule en E8 :

=MOYENNE(DECALER(plage;GRANDE.VALEUR(lig;50)-1;))

A+

 

[job75]

Re : Moyenne sur les 50 dernières lignes non vide

Re,

Si le tableau est très grand, la formule précédente peut créer une plage qui sort des limites de la feuille.

Il faut alors préciser la hauteur de la plage :

=MOYENNE(DECALER(plage;GRANDE.VALEUR(lig;50)-1;;LIGNES(plage)-GRANDE.VALEUR(lig;50)+1))

Fichier (2).

Nota : il a été dit qu'il y a 500 valeurs, donc ici c'est superflu.

A+

 

[hbenalia]

Re : Moyenne sur les 50 dernières lignes non vide

Bonjour à tous,

Une erreur dans mes formules dans le post #7, notifiée par mapomme que je remercie encore, les formules présentent des erreurs lors de la saisie de valeurs négatives parmi les 50 dernières valeurs, pour corriger ces erreurs, on remplace dans ces formules l'expression qui determine la ligne de la dernière cellule non vide de la colonne c-à-d l'expression EQUIV(-1;$C:$C;-1) par l'expression EQUIV(9^9;$C:$C;1) ou EQUIV(9^9;$C:$C).
Mille excuses pour ces erreurs....

Cordialement