XL 2013 Calculer la moyenne en prennante compte des "Absent" comme 0 et s'ajuste automatiquement en ajoutant ou en supprimant les colonnes

[lestoiles1]

Bonjour,

J'aimerais avoir dans la colonne "Average" la moyenne en prenant compte des "Absent" comment 0 et s'ajuste automatiquement lorsque je supprime ou ajoute une colonne. Noter auss que que parfois la premiere date change (dans la celuule j3)

Merci d'avance

 

[ALS35]

Bonjour,
Une proposition à tester en I4 :

=MOYENNE(SIERREUR(--SUBSTITUE(DECALER(I4;0;1;1;COLONNES(Table2)-COLONNE(I4));"Absent";0);""))

Cordialement

 

[job75]

Bonjour lestoiles1, ALS35,

En I4 formule normale :

=MOYENNE(DECALER([@Average];;1;;COLONNES(Table2)))

Les cellules vides ou contenant du texte sont ignorées.

Il ne faut pas de nombres à droite du tableau.

Attention le fichier est en mode de calcul Manuel, mettez-le en Automatique à la fin de la Worksheet_SelectionChange :

Application.Calculation = xlCalculationAutomatic

A+

 

[job75]

Notez qu'il n'y a aucun "Absent" dans la plage J4:Y189.

Mais si vous voulez qu'ils soient traités comme des zéros remplacez-les par zéro dans les formules de cette plage.

 

[lestoiles1]

Notez qu'il n'y a aucun "Absent" dans la plage J4:Y189.

Mais si vous voulez qu'ils soient traités comme des zéros remplacez-les par zéro dans les formules de cette plage.

Merci pour votre réponse, mais je veux que c'est la formule de la moyenne qui detecte les "Absent" comme 0 sans changer les autres formules.

 

[lestoiles1]

Bonjour,
Une proposition à tester en I4 :

=MOYENNE(SIERREUR(--SUBSTITUE(DECALER(I4;0;1;1;COLONNES(Table2)-COLONNE(I4));"Absent";0);""))

Cordialement

ca ne marche pas malheureusement.

 

[ALS35]

ca ne marche pas malheureusement.

Bonjour,
C'est dommage parce que chez moi ça marche, alors poste ton fichier en indiquant le résultat attendu, sinon on n'avancera pas.
Cordialement

 

[job75]

Bonjour lestoiles1, le forum,

Merci pour votre réponse, mais je veux que c'est la formule de la moyenne qui detecte les "Absent" comme 0 sans changer les autres formules.

Pour que les "Absent" soient comptés comme des zéros vous pouvez utiliser en I4 :

=SOMME(DECALER([@Average];;1;;COLONNES(Table2)))/(NB(DECALER([@Average];;1;;COLONNES(Table2)))+NB.SI(DECALER([@Average];;1;;COLONNES(Table2));"Absent"))

Formule normale.

Arrangez-vous pour avoir des "Absent" dans le tableau afin qu'on puisse tester...

 

[lestoiles1]

Bonjour lestoiles1, le forum,

Pour que les "Absent" soient comptés comme des zéros vous pouvez utiliser en I4 :

=SOMME(DECALER([@Average];;1;;COLONNES(Table2)))/(NB(DECALER([@Average];;1;;COLONNES(Table2)))+NB.SI(DECALER([@Average];;1;;COLONNES(Table2));"Absent"))

Formule normale.

Arrangez-vous pour avoir des "Absent" dans le tableau afin qu'on puisse tester...

Désolé, j'ai pas pensé qu'il n'y avait pas "Absent" dans le fichier

 

[lestoiles1]

Bonjour lestoiles1, le forum,

Pour que les "Absent" soient comptés comme des zéros vous pouvez utiliser en I4 :

=SOMME(DECALER([@Average];;1;;COLONNES(Table2)))/(NB(DECALER([@Average];;1;;COLONNES(Table2)))+NB.SI(DECALER([@Average];;1;;COLONNES(Table2));"Absent"))

Formule normale.

Arrangez-vous pour avoir des "Absent" dans le tableau afin qu'on puisse tester...

Merci, ç'est ça

 

[ALS35]

Bonjour,
J'ai noté une différence de comportement du DECALER entre ta version et la mienne.
Au cas où, essaie :

=MOYENNE(SIERREUR(--SUBSTITUE(DECALER([@Average];0;1;1;COLONNES(Table2));"Absent";0);""))

Cordialement

 

[job75]

@ALS35 oui c'est bon mais il faut valider la formule matriciellement.

Merci, ç'est ça

Oui c'est ça, en I38 la moyenne était 40,22 avec la nouvelle formule elle passe à 34,47.

 

[job75]

Bonjour lestoiles1, le forum,

Pour simplifier la formule et éviter de traiter des cellules à droite du tableau utilisez le nom défini P :

=DECALER(Table2[@Average];;1;;COLONNES(Table2)) Table2

Je rappelle que l'espace est l'opérateur d'intersection de plages.

Formule en I4 :

=SOMME(P)/(NB(P)+NB.SI(P;"Absent"))

A+

 

[lestoiles1]

Bonjour lestoiles1, le forum,

Pour simplifier la formule et éviter de traiter des cellules à droite du tableau utilisez le nom défini P :

=DECALER(Table2[@Average];;1;;COLONNES(Table2)) Table2

Je rappelle que l'espace est l'opérateur d'intersection de plages.

Formule en I4 :

=SOMME(P)/(NB(P)+NB.SI(P;"Absent"))

A+

Ok merci