XL 2016 Moyenne cellule non adjacente mais réguliere et excepté vides

johh

XLDnaute Nouveau
Bonjour à tous,

Je cherche une formule pour faire des moyennes de cellules non adjacentes, mais placées de façon régulière et une condition que les cellules vides/=0 ne soit pas prises en compte.

Les cellules en questions sont : d44 d49 d54 ..... jusqu'à d194.

J'ai tenté
=SIERREUR(SOMMEPROD((d44:d194<>0)*(MOD(COLONNE(d44:d194);5)=0);d44:d194)/SOMMEPROD((d44:d194<>0)*(MOD(COLONNE(d44:d194);5)=0));0)

mais sans résultats.
 
Solution
Bonsoir @johh ,
  • Vos données sont en colonne, donc il faut prendre la fonction ligne... La colonne est constante, c'est la colonne D. C'est le numéro de ligne qui varie.
  • Les données commencent en ligne 44, le modulo de la ligne 44 par 5 est =4 et pas =0
  • la somme des lignes qui sont différentes de 0 est :
    SOMMEPROD((D44:D194<>0)*(MOD(LIGNE(D44:D194);5)=4)*((D44:D194)))
  • le nombre de valeurs différente de zéro est :
    SOMMEPROD( --((d44:d194<>0) * (MOD(LIGNE(d44:d194);5)=4)) )
nota : Pas pu vérifier car pas de fichier joint

mapomme

XLDnaute Barbatruc
Supporter XLD
Bonsoir @johh ,
  • Vos données sont en colonne, donc il faut prendre la fonction ligne... La colonne est constante, c'est la colonne D. C'est le numéro de ligne qui varie.
  • Les données commencent en ligne 44, le modulo de la ligne 44 par 5 est =4 et pas =0
  • la somme des lignes qui sont différentes de 0 est :
    SOMMEPROD((D44:D194<>0)*(MOD(LIGNE(D44:D194);5)=4)*((D44:D194)))
  • le nombre de valeurs différente de zéro est :
    SOMMEPROD( --((d44:d194<>0) * (MOD(LIGNE(d44:d194);5)=4)) )
nota : Pas pu vérifier car pas de fichier joint
 
Dernière édition:

johh

XLDnaute Nouveau
Merci beaucoup
J'ai ajusté à mon classeur au final ca donne :

=(SOMMEPROD((Feuil1!D44:D194<>0)*(MOD(LIGNE(Feuil1!D44:D194);5)=4)*((Feuil1!D44:D194))))/(SOMMEPROD( --((Feuil1!D44:D194<>0) * (MOD(LIGNE(Feuil1!D44:D194);5)=4)) ))

C'est fonctionnel et ne prend pas en compte les 0
 

johh

XLDnaute Nouveau
@mapomme petit probleme j'ai le meme chose à faire pour les cellules suivantes donc j'ai fait :

=(SOMMEPROD((Feuil1!D46:D196<>0)*(MOD(LIGNE(Feuil1!D46:D196);5)=4)*((Feuil1!D46:D196))))/(SOMMEPROD( --((Feuil1!D46:D196<>0) * (MOD(LIGNE(Feuil1!D46:D196);5)=4)) ))

Seulement ça met le résultat de la formule que vous m'avez donné, avez vous une idée?

Désolé pour les smileys c''est moins compréhensible
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Re,

D'après votre formule, vous partez de la cellule D46 et voulez faire les moyennes de toutes les 5 cellules en partant de D46 (D46, D51, D56, ...).

Je suppose qu'ensuite, vous voulez à la ligne suivante, partir de D47 et voulez faire les moyennes de toutes les 5 cellules en partant de D47 (D47, D52, D57, ...).

Est-ce cela ?

nota: je n'avais pas vu le message privé - je regarde
 

mapomme

XLDnaute Barbatruc
Supporter XLD
Pour les deux premieres lignes, on peut diviser par le nombre de cellule non vide? pour avoir du coup la moyenne, ou on doit garder le total?

Si on divise les deux premières lignes par le nombre de cellules non vides, ça modifie aussi les autres formules.

Je regarde et vous envoie le nouveau fichier.

Le voilà:
 

Pièces jointes

  • johh- Fevrier 2020- v2.xlsx
    37 KB · Affichages: 7

Discussions similaires

Statistiques des forums

Discussions
311 715
Messages
2 081 822
Membres
101 822
dernier inscrit
holale