XL 2016 Problème avec formule

[Donald30290]

Bonjour je me creuse la tête depuis un moment sans trouver la réponse donc je viens vers vous pour je l'espère une solution.
Je souhaite faire une moyenne de 60 valeurs(C4 a BJ4) EN OMETTANT les valeurs en dessous du centile de 2.5 et les valeurs supérieur du centile de 97.5 auriez vous une solution a mon problème svp?
Merci

 

[JHA]

Bonjour à tous,

Peut-être en formule matricielle, sinon annexe un petit exemple de fichier.

JHA

 

[Donald30290]

Voilà le tableau pour plus de compréhension merci

 

[sylvanu]

Bonjour le fil,
Essayez de mettre ça en BL4 : =SOMMEPROD((A4:BH4)*(A4:BH4>BJ4)*(A4:BH4<BK4))/SOMMEPROD(1*(A4:BH4>BJ4)*(A4:BH4<BK4))

 

[Donald30290]

Merci je regarde ça demain matin au bureau et vous tiens au courant.

 

[job75]

Bonsoir à tous,

Centile de 2.5 que voulez-vous dire ? Moi je connais le 3ème centile compris entre 2% et 3%.

A+

 

[sylvanu]

Bonjour,
En fait Donald fait un abus de langage. Il parle de centile car il utilise la formule centile.inclure.
"Centile.inclure renvoie le k-ième centile des valeurs d’une plage" mais comme on peut faire =CENTILE.INCLURE(A4:BH4;0,025), on parle de centile même si cela ne correspond plus à la définition d'origine.

 

[job75]

Bonjour le fil, le forum,

Faut quand même être gonflé pour présenter un fichier aussi foutrac alors voyez le fichier joint.

Formule en BK4 =MOYENNE.SI.ENS(A4:BH4;A4:BH4;">"&BI4;A4:BH4;"<"&BJ4)

Bonne journée.

 

[sylvanu]

Bonjour le fil,
Job, la formule =(MAX(A:A)-MIN(A:A))*0,025 ne définit pas le centille à 2.5% car il inclut les échantillons sur bornes.
Par exemple, quand on compte les personnes de moins de 1m75, ceux qui font 1m75 sont exclus du comptage.
On le voit bien dans le fichier ci joint.
Par contre, la formule moyenne.si.ens est plus "jolie" que la mienne en sommeprod.
Bonne journée.

 

[Donald30290]

Merci beaucoup les 2 fonctionnent je vais voir avec plus de données si cela correspond à ce qui est attendu merci a vous bonne journee

 

[job75]

Job, la formule =(MAX(A:A)-MIN(A:A))*0,025 ne définit pas le centille à 2.5% car il inclut les échantillons sur bornes.

Bon d'accord, voyez ce fichier (2) avec la formule en BI4 =PETITE.VALEUR(A4:BH4;ARRONDI(NBVAL(A4:BH4)*2,5%;0))

 

[sylvanu]

Bon d'accord, voyez ce fichier (2) avec la formule en BI4 =PETITE.VALEUR(A4:BH4;ARRONDI(NBVAL(A4:BH4)*2,5%;0))

Job, une petite.valeur ne peut que renvoyer un élément de la table, or le centile n'est pas toujours dans la table.
Le centile est défini comme étant la moyenne de la plus petite valeur de rang 1 ET de la plus petite valeur de rang 2 d'une série d'échantillons.
D'où les formules rébartative :
=(PETITE.VALEUR(Ech;ARRONDI(NBVAL(Ech)*2,5%;0))+PETITE.VALEUR(Ech;1+ARRONDI(NBVAL(Ech)*2,5%;0)))/2 pour la valeur basse et
=(PETITE.VALEUR(Ech;ARRONDI(NBVAL(Ech)*0,975;0))+PETITE.VALEUR(Ech;1+ARRONDI(NBVAL(Ech)*0,975;0)))/2 pour la valeur supérieure.
C'est pour ça que la formule Centile est plus simple.
J'ai testé dans le fichier joint les 4 formules.

 

[job75]

Pourquoi parler de centiles ? Au post #11 je détermine le maximum des 2,5% des valeurs classées en ordre croissant.

Comme ici 60*2,5% = 1, 5 => arrondi à 2 il s'agit de la 2ème valeur la plus petite.

Pour la moyenne les 2 1ères valeurs et les 2 dernières seront donc exclues Edit : s'il n'y a pas de doublons sur les bornes...

 

[sylvanu]

Les centiles étaient un pré requis dans le fichier de Donald, c'est pour ça que j'ai travaillé sur cette base.
J'ai considéré le fichier comme étant les "spécifications", qui utilisent le centille pour déterminer les bornes. C'est sans doute son besoin.

 

[Donald30290]

Merci pour toutes vos réponses cela m'a fortement aidé et solutionné pour cette partie .j'aurai une autre question si vous avez la réponse ,il me faut faire une colonne qui se colore selon si dans un échantillon donné la valeur est supérieure ou inférieure a une valeur fixe.
En clair ce serait pour toute valeur dans une case de la ligne 4(par exemple) si cela est < 7,35 la case est bleu ou si 7,35<valeur<23,53 la case est verte ou si 23,53<valeur<2000 la case est jaune ou si 2000<valeur la case est rouge.
Est il possible de faire cela? Merci