adapter une formule qui fonctionne pour des cellules adjacentes à des cellules non adjacentes

[didierrp]

Bonjour,
J'ai grâce à ce forum une formule qui répond parfaitement a mes besoins mais elle a été conçue pour des cellules adjacentes, dans la formule d'exemple ci-dessous pour les cellules de A1 à A10 (A1:A10).
J'aimerai la meme formule pour des cellules non adjacentes par exemple B1 et B5 et B10. (B1;B5;B10)

J'ai essayé de l'adapter mais ne connaissant pas bien la syntaxe j'arrive a des erreurs.

Voici la formule:
=SIERREUR(MOYENNE.SI(A1:A10;"<>0");0)

Merci

 

[djidji59430]

Bonjour,

=MOYENNE(B1;B5;B10)

 

[didierrp]

Merci mais ce serait trop simple....!!

Cela ne correspond pas à la formule que j'ai laissée en exemple et qui vérifie qu'au cas ou une (ou des) cellule est a 0 on ne compte pas cette cellule car c'est une mesure qui n'a pas été faite et elle ne rentre donc pas dans la moyenne.

Il faut qu'elle corresponde exactement à l'identique de la formule =SIERREUR(MOYENNE.SI(A1:A10;"<>0");0) mais remaniée pour des cellules non adjacentes..

Merci

 

[JHA]

Bonjour à tous,

Il serait bien d'avoir un fichier exemple pour répondre à tes besoins, cela éviterait de tâtonner à chaque fois.
En supposant que tu dois prendre la valeur toutes les 5 lignes (10;15;20 etc..)
A essayer cette formule matricielle

=MOYENNE(SI((MOD(LIGNE(B10:B20);5)=0)*(B10:B20<>0);B10:B20))

Si tu n'as pas le même nombre de lignes entre chaque valeur (B1;B5;B10) alors là, dis nous ce que tu as entre ces valeurs avec un fichier exemple, on pourra peut être t'aider.

JHA

 

[didierrp]

Bon je tente une dernière explication après je ferai un fichier exemple..:
Prenons deux cellules non adjacentes au hasard: B13 et B18

Si je fais =MOYENNE(B13;B18) au cas ou en B13 ou en B18 la cellule affiche 0 (ce qui veut dire que la mesure n'a pas été faite) la moyenne est erronée car elle tient compte d'une cellule dont la valeur est 0 . Donc je cherche la bonne formule pour éviter ce problème.

Est-ce que c'est comprehensible ou sinon en effet je ferai un fichier exemple.
Par avance merci

 

[JHA]

Bonjour à tous,

Avec des conditions à répétition.

JHA

 

[Jacky67]

Bon je tente une dernière explication
Est-ce que c'est comprehensible ou sinon en effet je ferai un fichier exemple..

Re...
Il serait sage AMHA..., afin de ne pas monter une usine à gaz... de disposer ces données de façon à pouvoir les utiliser par une fonction tel que déjà proposé

 

[job75]

Bonjour,

Voyez le fichier joint et cette fonction VBA :

Function MoyenneSiNonNul(r As Range)
Dim s#, n
For Each r In r
  If IsNumeric(CStr(r)) Then _
    If r <> 0 Then s = s + r: n = n + 1
Next
MoyenneSiNonNul = s / n
End Function

A+

 

[job75]

Re,

S'il y a des plages illimitées la fonction précédente prend du temps...

Il vaut donc mieux utiliser :

Function MoyenneSiNonNul(r As Range)
Dim s#, n
For Each r In Intersect(r, r.Parent.UsedRange)
  If IsNumeric(CStr(r)) Then _
    If r <> 0 Then s = s + r: n = n + 1
Next
MoyenneSiNonNul = s / n
End Function

Fichier (2).

A+

 

[ROGER2327]

Bonsoir à tous.

En l'absence de précision sur le besoin réel, un petit classeur hasardeux...

Bonne soirée.

ℝOGER2327
#8416

Samedi 21 As 144 (Saint Possible, schizophrène - fête Suprême Quarte)
3 Frimaire An CCXXV, 7,2197h - chicorée
2016-W47-3T17:19:38Z

 

[job75]

Bonsoir Roger,

Toujours très sympa de mettre des fichiers à notre disposition, merci beaucoup.

J'interviens juste sur le 1er onglet.

Bonne fin de soirée.

 

[job75]

Bonjour à tous,

Fichier (2) pour le 2ème onglet.

Edit : fichier (2 bis) avec MOYENNE.SI.ENS en B12 (à partir d'Excel 2007 comme SIERREUR).

Bonne journée.

 

[didierrp]

WWWWHAOUUUUU.... Vraiment un grand merci pour toutes vos réponses, je ne m'attendais pas a tant... !!

Toutes vos solutions convenaient a mon problème , j'ai pris la solution proposée par JHA parce qu'elle était assez simple a mettre en oeuvre et surtout parce que je comprenais à peu près la syntaxe!! ... je l'ai adaptée à mon utilisation dans une première feuille (il y en aura d'autres..!)

Comme la formule entrainait une erreur au cas ou toutes les cellules incriminées étaient à 0 (dans le cas ou il n'y ai pas eu de mesures ce jour là) et que ce n'est pas très beau comme presentation...! je l'ai accommodé d'un SIERREUR en m'inspirant de la formule que j'avais donné dans le 1er post et qui fonctionne très bien dans le cas de cellules adjacentes..

Voilà donc ma formule finale: =SIERREUR(MOYENNE(SI(P14=0;$D$1;P14);SI(P18=0;$D$1;P18));0) (on peut bien évidemment rajouter d'autres cellules )

Au cas ou vous pensiez que l'on peut faire plus simple n'hésitez pas a proposer ...

Encore merci à vous tous

 

[job75]

Re,

Si l'on veut éviter de faire appel à la cellule D1 (qui ne doit pas contenir un nombre) :

=SIERREUR(SOMME(P14;P18)/NB(1/P14;1/P18);"")

Et cette formule "pèse" 37 octets de moins en mémoire.

Edit 1 : pourquoi utilisez-vous =SIERREUR(xxx;0) ? Une moyenne peut être égale à zéro !

Edit 2 : la fonction NB est limitée à 256 arguments (séparés par des ";"), comme la fonction MOYENNE.

Pas de problème avec SOMME puisqu'on peut entrer des références multiples entre parenthèses.

A+

 

[job75]

Bonjour le fil, le forum,

Non, c'est un maximum de 255 arguments que la fonction NB peut accepter.

Fichier joint avec cette belle petite formule pour la moyenne :

=SOMME((A1;A3;A5;A7;A9;A11;A13;A15;A17;A19;A21;A23;A25;A27;A29;A31;A33;A35;A37;A39;A41;A43;A45;A47;A49;A51;A53;A55;A57;A59;A61;A63;A65;A67;A69;A71;A73;A75;A77;A79;A81;A83;A85;A87;A89;A91;A93;A95;A97;A99;A101;A103;A105;A107;A109;A111;A113;A115;A117;A119;A121;A123;A125;A127;A129;A131;A133;A135;A137;A139;A141;A143;A145;A147;A149;A151;A153;A155;A157;A159;A161;A163;A165;A167;A169;A171;A173;A175;A177;A179;A181;A183;A185;A187;A189;A191;A193;A195;A197;A199;A201;A203;A205;A207;A209;A211;A213;A215;A217;A219;A221;A223;A225;A227;A229;A231;A233;A235;A237;A239;A241;A243;A245;A247;A249;A251;A253;A255;A257;A259;A261;A263;A265;A267;A269;A271;A273;A275;A277;A279;A281;A283;A285;A287;A289;A291;A293;A295;A297;A299;A301;A303;A305;A307;A309;A311;A313;A315;A317;A319;A321;A323;A325;A327;A329;A331;A333;A335;A337;A339;A341;A343;A345;A347;A349;A351;A353;A355;A357;A359;A361;A363;A365;A367;A369;A371;A373;A375;A377;A379;A381;A383;A385;A387;A389;A391;A393;A395;A397;A399;A401;A403;A405;A407;A409;A411;A413;A415;A417;A419;A421;A423;A425;A427;A429;A431;A433;A435;A437;A439;A441;A443;A445;A447;A449;A451;A453;A455;A457;A459;A461;A463;A465;A467;A469;A471;A473;A475;A477;A479;A481;A483;A485;A487;A489;A491;A493;A495;A497;A499;A501;A503;A505;A507;A509))/NB(1/A1;1/A3;1/A5;1/A7;1/A9;1/A11;1/A13;1/A15;1/A17;1/A19;1/A21;1/A23;1/A25;1/A27;1/A29;1/A31;1/A33;1/A35;1/A37;1/A39;1/A41;1/A43;1/A45;1/A47;1/A49;1/A51;1/A53;1/A55;1/A57;1/A59;1/A61;1/A63;1/A65;1/A67;1/A69;1/A71;1/A73;1/A75;1/A77;1/A79;1/A81;1/A83;1/A85;1/A87;1/A89;1/A91;1/A93;1/A95;1/A97;1/A99;1/A101;1/A103;1/A105;1/A107;1/A109;1/A111;1/A113;1/A115;1/A117;1/A119;1/A121;1/A123;1/A125;1/A127;1/A129;1/A131;1/A133;1/A135;1/A137;1/A139;1/A141;1/A143;1/A145;1/A147;1/A149;1/A151;1/A153;1/A155;1/A157;1/A159;1/A161;1/A163;1/A165;1/A167;1/A169;1/A171;1/A173;1/A175;1/A177;1/A179;1/A181;1/A183;1/A185;1/A187;1/A189;1/A191;1/A193;1/A195;1/A197;1/A199;1/A201;1/A203;1/A205;1/A207;1/A209;1/A211;1/A213;1/A215;1/A217;1/A219;1/A221;1/A223;1/A225;1/A227;1/A229;1/A231;1/A233;1/A235;1/A237;1/A239;1/A241;1/A243;1/A245;1/A247;1/A249;1/A251;1/A253;1/A255;1/A257;1/A259;1/A261;1/A263;1/A265;1/A267;1/A269;1/A271;1/A273;1/A275;1/A277;1/A279;1/A281;1/A283;1/A285;1/A287;1/A289;1/A291;1/A293;1/A295;1/A297;1/A299;1/A301;1/A303;1/A305;1/A307;1/A309;1/A311;1/A313;1/A315;1/A317;1/A319;1/A321;1/A323;1/A325;1/A327;1/A329;1/A331;1/A333;1/A335;1/A337;1/A339;1/A341;1/A343;1/A345;1/A347;1/A349;1/A351;1/A353;1/A355;1/A357;1/A359;1/A361;1/A363;1/A365;1/A367;1/A369;1/A371;1/A373;1/A375;1/A377;1/A379;1/A381;1/A383;1/A385;1/A387;1/A389;1/A391;1/A393;1/A395;1/A397;1/A399;1/A401;1/A403;1/A405;1/A407;1/A409;1/A411;1/A413;1/A415;1/A417;1/A419;1/A421;1/A423;1/A425;1/A427;1/A429;1/A431;1/A433;1/A435;1/A437;1/A439;1/A441;1/A443;1/A445;1/A447;1/A449;1/A451;1/A453;1/A455;1/A457;1/A459;1/A461;1/A463;1/A465;1/A467;1/A469;1/A471;1/A473;1/A475;1/A477;1/A479;1/A481;1/A483;1/A485;1/A487;1/A489;1/A491;1/A493;1/A495;1/A497;1/A499;1/A501;1/A503;1/A505;1/A507;1/A509)

Bonne journée.