moyenne avec critére et cellule vide - résolu

[gigiwin]

Bonjour,

Je n'arrive pas à trouver une solution pour que ma formule fonctionne.
En H8, je voudrais avoir la moyenne des cellules comprise entre -1 et -15 de la plage K7:K500.
J'ai essayé avec SOMMEPROD sans plus de résultat.
Merci de votre aide.
C'est avec EXCEL 2003.

 

[vgendron]

Bonjour

avec la formule Moyenne.si.ens. (à vérifier si elle existe déjà sous excel 2003)
=MOYENNE.SI.ENS(K17:K291;K17:K291;">-15";K17:K291;"<-1")

 

[gigiwin]

Bonjour vgendron,

Merci pour la réponse, mais fonction indisponible sous 2003.

 

[vgendron]

sinon avec du sommeprod
=SOMMEPROD((K17:K291>-15)*(K17:K291<-1);(K17:K291))/SOMMEPROD((K17:K291>-15)*(K17:K291<-1))

 

[gigiwin]

Re,

Avoir la formule SOMMEPROD, j'obtiens 4.61 au lieu de 8.61.
Je mets le fichier avec cette dernière formule.
J'ai aussi essayé:
=MOYENNE(SI((k17:k500<-15)*(k17:k500<-1);(k17:k500);"")) en matricielle, pas plus de résultat.
Et je viens de m'apercevoir que la formule en H9 ne va pas non plus.
Au secours, c'est atroce...

 

[vgendron]

Avoir la formule SOMMEPROD, j'obtiens 4.61 au lieu de 8.61.

Comment obtiens tu 8.61 ??
chez moi, la formule Moy.si.ens et la formule avec les sommeprod donnent le meme résultat..
après. peut etre un pb de limites.?? <-1 ou <=-1

dans ta formule moyenne qui ne marche pas...vérife le sens des inégalités.... <-15 et<-1

 

[vgendron]

voir PJ avec calculs intermédiaires en colonnes L M N

 

[gigiwin]

Bonjour,

Pour avoir les 8.14 ( et non 8.61 erreur de frappe), j'ai simplement fait un tri sur K17:k300, puis addition par couleur, et fait la moyenne "manuellement".
Pour ce qui est des calculs intermédiaires, trop compliqué, j'ai plusieurs fichiers et cela prendrait trop de temps, même avec un copier/coller.
Je ne désespère pas, je vais trouver.

 

[JHA]

Bonjour à tous,

Un essai en pièce jointe, filtre sur une des colonnes "L" à "O"
sélectionnes la colonne "K" de la plage filtrée
regarde en bas la moyenne filtrée

JHA

 

[gigiwin]

Merci JHA, cela fonctionne.

 

[JHA]

Bonjour à tous,

Une version avec des plages nommées dynamiques.

JHA

 

[vgendron]

Hello JHA hello gigiwin

je persiste. je ne vois toujours pas comment tu obtiens ton 8.14, meme en faisant le tri dont tu parles.
d'ailleurs..les colonnes intermédiaires que j'ai utilisées tout comme JHA, sont dans le meme esprit

1) pour le critère Moyen, mon calcul donne une somme de -457.. le tiens donne -472 (cellule J8): la différence est de -15.... je réitère.. tu as un souci de mauvaise définition des bornes: <-15 ou <=-15 (en L16:N16) j'ai écris explicitement les bornes utilisées par mes formules pour que tu saches quoi adapater au besoin

2) pour le critère BON: attention pour Excel "FAUX" = 0: et donc, toutes les cellules qui contiennent FAUX sont comptabilisées...
donc SOMMEPROD((K17:K290>=0)*1) te renvoie le nombre total de lignes :274 ---> moyenne fausse
j'ai donc modifié ma formule par SOMMEPROD((K17:K290>=0)*(ESTNUM(K17:K290))*1)
parce que estnum(faux)= faux

3) les 3 formules en H7:H9 donnent normalement les bons résultats.

4) dans les montants à obtenir que tu décris: on est d'accord pour le Bon et Mauvais..
pour le Moyen: avec 472 (au lieu de 457) et 116: 472/116 = 4.10.. pas 8.14 (d'ou le mystère)


et pour finir, les colonnes intermédiaires étaient la juste pour aider à confirmer les formules: tu peux donc les supprimer

 

[gigiwin]

Bonjour Vgendron,

Merci pour tes efforts, tu as raison je me suis totalement planté sur mes calculs, j'ai voulu aller trop vite.
J'ai tout repris avec le dernier fichier que tu as posté.
Maintenant, mon classeur est tout beau et tout fonctionne parfaitement.
Avec encore tous mes remerciements, je te souhaite une bonne continuation.