Formule Excel =MOYENNE pour un grand nombre de cellule

[GuillaumA]

Bonjour,
Devant faire la moyenne de 82 cellules, j'ai un peu du mal étant donnée les restrictions d'excel sur le nombre d'élements dans une formule).

En effet, je dois faire la moyenne des cellules I9, L9, O9, ... jusqu'à IR9. Il s'agit de toute les cellules à une distance de 3 colonnes chacune.
Existe t'il une méthode plus simple de calcule que:
=MOYENNE(I9;L9;O9;...;IR9) ???

Merci de votre considération.

Amicalement,
Guillaume

 

[hoerwind]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour,

Essaie cette formule : =MOYENNE(SI(MOD(COLONNE(I9:IR9);3)=0;I9:IR9))

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour hoerwind,
Je viens d'essayer ta formule et j'ai un petit soucis:

En effet, pour l'instant il n'y a que la cellule I9 de remplis avec la valeur 3840, les autres sont vides.
Le résultat de ta formule est: 1920 soit la moitié de la valeur en cellule I9. Cependant le résultat devant apparaître dans ce cas là est: 3840.
Je n'arrive pas à comprendre ou dans ta formule le diviseur 2 est exprimé.

Par ailleurs, ta formule ne marche que si toute les cellules sont remplis? En effet, la formule doit permettre que des cellules vides existe car les 88 cellules ne seront jamais toutes complètes.

En tout cas, merci pour ton aide!

Guillaume

 

[hoerwind]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Re,

Je ne sais pas ce que tu as fait, mais je viens de tester ton exemple.
Si je saisi 3840 en I9 et rien dans aucune autre cellule, la formule renvoie 3840

Par contre si je saisi en plus en L9 un 0, j'obtiens 1920 comme résultat.
Probablement donc que tu aurais quelque part un 0 dans une autre cellule.

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bizarre, je viens de checker, re-checker, je n'ai pas un seul zéro. Je reviens vers toi plus tard.
Peut être du fait qu'entre ces colonnes il y a d'autres résultats ? (Par exemple en J9 j'ai 16%, etc...

Merci encore pour ta réponse !

EDIT: Je viens de checker, c'est bien du fait qu'il y a des résultats entre les colonnes ou je souhaite récupérer les valeurs pour une moyenne.
Dans mon exemple j'ai viré +16% et Oh miracle, le 3840 est apparu.
La formule peut-elle être modifié en conséquence ?

Amicalement,
Guillaume

 

[tototiti2008]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour GuillaumA, Bonjour hoerwind,

peut-être

=MOYENNE(SI(MOD(COLONNE(I9:IR9);3)=0;SI(I9:IR9="";"";I9:IR9)))

Edit :
@hoerwind : pour info, chez moi en ne remplissant qu'une seule cellule, ça ne marche pas comme chez toi, il me fait la moyenne de toutes les cellules en considérant 0... bizarre

 

[hoerwind]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Re,

Effectivement le formule proposée tient compte des valeurs des autres colonnes.

Une autre formule, mais matricielle, qui semble fonctionner :

=MOYENNE(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9))

à valider par Ctrl+Maj+Entrée

Voir la pièce jointe (motif vert), y compris un premier essai (motif jaune)
Pourquoi faire simple lorsqu'il y a moyen de faire compliqué !

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Re bonjour hoerwind, bonjour toto,
Vos deux solutions marchent à merveille en matricielle !
Un grand merci !!!
Excellente fin de journée.

Amicalement,
Guillaume

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour à hoerwind, tototiti, le forum, le fil
Je reviens sur ce sujet car j'ai besoin de deux formules découlant de cette dernière. Seulement voila.. mon niveau en VBA est tout de même meilleur que celui en formule Excel et j'échoue à adapter la formule proposées précédemment.
Mes deux besoins sont, pour les même cellules ciblés (I9, ..., IR9):

- Afficher la plus petite valeur de toutes ces cellules (ex: Si I9 = 3000 , L9 = 2300 et O9 = 2500, alors 2300 sera affiché)
- Une formule pour afficher la valeur présente deux lignes au dessus et une colonnes en moins de la plus petite valeur (ex: Dans mon exemple, il s'agit de L9 avec K7 ciblée)

Merci par avance de votre considération et bonne journée à vous.

Amicalement,
Guillaume

 

[hoerwind]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour,

La même formule que précédemment, en remplaçant MOYENNE par MIN.
Toujours matricielle à valider par Ctrl+Maj+Entrée.

Pour récupérer une valeur par rapport à une autre, rechercher la position de cette valeur par la fonction EQUIV et la DECALER le nombre de lignes et de colonnes souhaitées.
Avec une petite pièce jointe ce sera plus facile pour te montrer comment faire, si tu ne t'en sors pas.

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Re Hoerwind,

Voici ce que j'ai fait (sur le même principe que ce que tu m'avais donné précédement):

=MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9))

Marche

=EQUIV(MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9));DECALER(I9:R9;-2;-1))

ou

=EQUIV(DECALER(I9:R9;-2;-1);MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9)))

Ne marche pas

Le decaler devrait se situer au départ non ?

Guillaume

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Je sens que je m'approche:

=EQUIV(DECALER(MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:R9));-2;-1);I9:IR9)

 

[tototiti2008]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Bonjour à tous,

à tester

=decaler(I9;-2;Equiv(MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9));I9:IR9;0)-2)

 

[GuillaumA]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Ca marche !!!
Merci tototiti !

J'allais mettre en exemple le fichier joint. Je l'ai update avec ta solution.

Biz a vous deux

 

[hoerwind]

Re : Formule Excel =MOYENNE pour un grand nombre de cellule

Re,

Il me semble que ce serait plutôt ceci :

=DECALER(A9;-2;EQUIV(MIN(SI((MOD(COLONNE(I9:IR9);3)=0)*(I9:IR9>0);I9:IR9));A9:IR9;0)-1)
vois la pièce jointe sous laquelle la valeur 5 est récupérée.

Si tu as des doublons dans la valeur minimum, la formule ne tiendra compte que de la première valeur minimum rencontrée.