XL 2013 Problèmes avec la fonction DECALER et des valeurs #N/A

[ExlEnGalere]

Bonjour, voici mon problème :

j'ai une feuille de calcul dans laquelle une ligne s'incrémente tous les jours. Dans une colonne, je souhaite calculer la moyenne des 5 dernières valeurs. Pour cela j'utilise les fonctions "MOYENNE.SI(DECALER)". Jusque là tout fonctionne.
Seulement dans certains cas, certaines valeurs de la colonne ont pour valeur "#N/A". A ce moment là je ne sais pas ce qu'il se passe mais le résultat est différent avec les fonctions "MOYENNE.SI(DECALER)" et si je fais manuellement la moyenne des 5 dernières valeurs (différentes de #N/A) de ma colonne.

Je vous joints un fichier Excel dans lequel j'ai dupliquée la colonne B pour y incruster des #N/A et ainsi voir la différence.

Dans l'espoir de trouver de l'aide parmi vous

Merci d'avance !

 

[R@chid]

Bonjour,
avec une formule matricielle :

=MOYENNE(GRANDE.VALEUR(SI(ESTNUM(C1:C20);C1:C20);LIGNE(1:5)))

@ valider par Ctrl+Shift+Enter


Cordialement

 

[ExlEnGalere]

Bonjour,
avec une formule matricielle :

=MOYENNE(GRANDE.VALEUR(SI(ESTNUM(C1:C20);C1:C20);LIGNE(1:5)))

@ valider par Ctrl+Shift+Enter


Cordialement

Bonjour R@chid, merci pour ta réponse ! Peux-tu expliquer cette formule ?
Cela ne fonctionne pas si l'on rajoute des #N/A dans la colonne.

Cordialement

 

[jmfmarques]

Bonjour

Cela ne fonctionne pas si l'on rajoute des #N/A dans la colonne

Une précision (à tout hasard) : "on" les "rajoute" comment, exactement, ces "#NA" ?
Je veux croire que ce n'est pas une saisie que tu y fais manuellement et sous forme de texte.

 

[R@chid]

Bonsoir @ tous,
Salut jmfmarques,

Cela ne fonctionne pas si l'on rajoute des #N/A dans la colonne.

ça marche sans faute, voir PJ

J'ai rajouté une autre formule un plus développée.


Cordialement

 

[jmfmarques]

Bonjour R@chid

ça marche sans faute, voir PJ

Tu ne le sais probablement pas, mais je n'ouvre jamais un classeur tiers.
Cela ne m'empêche pas de faire des petits tests --->> en voilà un :
Amitiés

1​
#N/A		formule en C3 : =MOYENNE(A1:A5)
3​		#N/A
#N/A
5​

 

[jmfmarques]

Il convient et serait très sage de traiter la furonculose plutôt que les furoncles.
Ces #NA ne sauraient être là par hasard et sont probablement le résultat de formules à corriger ou compléter.

 

[R@chid]

Re,
oui tu as tout à fait raison, mais il se peut les données sont importées depuis un autre fichier avec ses erreurs au format texte.

=MOYENNE(C20;C18;C15:C16;C13)

=MOYENNE(GRANDE.VALEUR(SI(ESTNUM(C1:C20);C1:C20);LIGNE(1:5)))

=MOYENNE(SOUS.TOTAL(1;DECALER(C1;GRANDE.VALEUR(SI(ESTNUM(C1:C20);LIGNE(C1:C20)-1);LIGNE(1:5));)))

 

[ExlEnGalere]

Re,
oui tu as tout à fait raison, mais il se peut les données sont importées depuis un autre fichier avec ses erreurs au format texte.

=MOYENNE(C20;C18;C15:C16;C13)

=MOYENNE(GRANDE.VALEUR(SI(ESTNUM(C1:C20);C1:C20);LIGNE(1:5)))

=MOYENNE(SOUS.TOTAL(1;DECALER(C1;GRANDE.VALEUR(SI(ESTNUM(C1:C20);LIGNE(C1:C20)-1);LIGNE(1:5));)))

Regarde la pièce jointe 1058265

Regarde la pièce jointe 1058266

Merci à tous pour votre aide.

R@chid, cela fonctionne avec la formule MOYENNE(SOUS.TOTAL(1;DECALER(C1;GRANDE.VALEUR(SI(ESTNUM(C1:C20);LIGNE(C1:C20)-1);LIGNE(1:5)))) lors ce que je fais référence à des cellules de la feuille.

Seulement, lorsque la formule fait référence à des cellules d'une autre feuille le résultat est #N/A.

Cordialement

 

[R@chid]

Re
Tu n'as pas adapté la formule pour l'autre feuille

 

[ExlEnGalere]

Re
Tu n'as pas adapté la formule pour l'autre feuille

Voici la formule que j'ai appliqué :
=MOYENNE(SOUS.TOTAL(1;DECALER(K6;GRANDE.VALEUR(SI(ESTNUM(K6:K50);LIGNE(K6:K50)-1);LIGNE(6:10))))
Dans mon fichier, les valeurs débutent en K6 jusqu'à K30 et après ce sont des cellules vides (K50).

 

[ExlEnGalere]

Voici la formule que j'ai appliqué :
=MOYENNE(SOUS.TOTAL(1;DECALER(K6;GRANDE.VALEUR(SI(ESTNUM(K6:K50);LIGNE(K6:K50)-1);LIGNE(6:10))))
Dans mon fichier, les valeurs débutent en K6 jusqu'à K30 et après ce sont des cellules vides (K50).

Voilà le fichier !

 

[R@chid]

Re,

voir PJ


Cordialement

 

[ExlEnGalere]

Re,

voir PJ


Cordialement

Merci beaucoup !
Puis je avoir une explication détaillée de la formule ?

 

[R@chid]

Re,
tu peux évaluer la formule en allant dans l'onglet formules, évaluer la formule.

Cordialement