[formule] Note pour competition (très complexe)
bonjour,
pour une compétition de trampoline, je réalise un fichier de notation, mais les spécificités étant nombreuses je ne sais pas trop comment m'y prendre. Je joins le fichier "palmarès".
Voici les règles:
1) les colonnes en bleu seront saisies manuellement donc rien à faire
2) les colonne jaunes sont les formules automatiques
préambule:
une équipe peut être constituée de 1 a 6 joueurs maximums
il y a 5 juges de départ donnant chacun une note (J1 à J5)
Demande
A) sur chaque ligne, la formule doit en m3,m4,m5 ...
1) exclure la note la plus haute et la plus basse des 5 juges pour chaque ligne
2) checker la valeur des notes : si toutes les notes sont supérieurs ou égal à 9 alors
2)a) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont inférieurs à 1/10 de point alors on fait la somme des 3 notes restantes.
2)b) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont supérieurs à 1/10 de point alors on fait la médiane multiplié par 3.
3) checker la valeur des notes : si toutes les notes sont entre 8 et 8.9 alors
3)a) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont inférieurs à 2/10 de point alors on fait la somme des 3 notes restantes.
3)b) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont supérieurs à 2/10 de point alors on fait la médiane multiplié par 3.
4) checker la valeur des notes : si toutes les notes sont inférieur à 7.9
4)a) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont inférieurs à 3/10 de point alors on fait la somme des 3 notes restantes.
4)b) déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
si une de ces 2 différences sont supérieurs à 3/10 de point alors on fait la médiane multiplié par 3.
NB: si lors d'un check des notes il y a un 9 et deux 8.5 par exemple on tombe ds la catégorie inférieur automatiquement, idem si il y a deux 9 et un 8
B) en colonne P il s'agit de la note totale de l'équipe, elle se calcule en additionnant les 3 notes les plus élevé de la colonne M
Voilà je reste a votre disposition
bonne soirée à vous et merci d'avance
Cordialement
Pourquoi faire simple quand on peut faire compliqué ... je vais essayer de faire ce numéro de trempoline, mais cela commence mal !
sur chaque ligne, la formule doit en m3,m4,m5 ...
heueueu, c'est où m3, m4, m5 ? ... sur chaque ligne !
exclure la note la plus haute et la plus basse des 5 juges pour chaque ligne
en M3 :
=SOMME(H3:L3)-MIN(H3:L3)-MAX(H3:L3)déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane
c'est la médiane ? ou la moyenne ? pour moi ce n'est pas la même chose, la médiane sépare la population en 50/50, donc avec 3 notes c'est forcément la note du milieu, alors que la moyenne, c'est ... la moyenne des 3 notes !
en colonne P il s'agit de la note totale de l'équipe, elle se calcule en additionnant les 3 notes les plus élevé de la colonne M
M ou O comme dans l'exemple fourni ?
=GRANDE.VALEUR(O3:O8;1)+GRANDE.VALEUR(O3:O8;2)+GRANDE.VALEUR(O3:O8;3)Il y a peut-être plus simple !
Le test sur les valeurs (>= à 9, entre 8.9 et 9 etc...) doit-il se faire sur l'ensemble des 5 notes ou bien sur les 3 restantes après éviction du max et du min?
Bonjour,
En me basant sur la plus faible des 3 notes restantes pour déterminer la tranche (>=9;9>x>=8;.....).
cela pourrait donner ceci :
=SI(ARRONDI(MAX(GRANDE.VALEUR(H3:L3;2)-GRANDE.VALEUR(H3:L3;3);GRANDE.VALEUR(H3:L3;3)-GRANDE.VALEUR(H3:L3;4));1)>CHOISIR(EQUIV(GRANDE.VALEUR(H3:L3;4);{0;8;9});0,3;0,2;0,1);MEDIANE(H3:L3)*3;SOMMEPROD(GRANDE.VALEUR(H3:L3;{2;3;4})))A+
checker la valeur des notes : si toutes les notes sont supérieurs ou égal à 9 alors
que fait-on si certaines notes sont sup et d'autres inf à cette valeur plafond ?
je suis bloqué sur cette règle ... mais passionnant !
merci de votre engouement je vais essayer de répondre avec méthode ^^
alors
1) "sur chaque ligne, la formule doit en m3,m4,m5 ..."
m3 doit checker les données de h3 a L3
m4 doit checker de h4 a L4 ect
2) "déterminer l’écart (sur les 3 notes restantes) entre la plus haute et la médiane ET entre la plus basse et la médiane"
exemple tu as 3 notes restantes âpres avoir enlever la plus haut et la plus basse, imaginons il te reste 9.1, 9.5, 9.7 la médiane est 9.5 et l’écart entre la médiane et la la plus haute est de (9.7 - 9.5) = 0.2
l’écart entre la médiane et la plus basse est de (9.5 - 9.1) = 0.4
NB : pas question d'utiliser la moyenne, pour simplifier médiane = la valeur au milieu des deux autres
ici dans tous les cas l’écart est supérieur à la tolérance de 1/10 comme note supérieur à 9
3) "en colonne P il s'agit de la note totale de l'équipe, elle se calcule en additionnant les 3 notes les plus élevé de la colonne M"
en colonne "O" mes excuses tu as raison
4) "Le test sur les valeurs (>= à 9, entre 8.9 et 9 etc...) doit-il se faire sur l'ensemble des 5 notes ou bien sur les 3 restantes après éviction du max et du min?"
sur les 3 valeurs restantes
5) "que fait-on si certaines notes sont sup et d'autres inf à cette valeur plafond ?"
on prend automatiquement le plafond le plus faible des 3 notes si il y a deux 9 et un 6 alors plancher de tolérance de 3/10 qui correspond aux notes de 0 à 7.9
Oui c est passionna t mais personnellement je me suis arraché les cheveux la dessus
merci de votre aide
cordialement
Voilà ce à quoi j'arrive (les formules ne sont probablement pas très jolies)
Mais il reste un souci : choisir si > ou >= à 0.1 (ou 0.2 ou 0.3).
Mais il devrait y avoir moyen à partir de là
bonsoir,
frannou, il me semble dans ton fichier tu intègres deja les conditions de >= 0.1, 0.2, 0.3 ? non ?
Sinon je ne vois pas trop comment ? je devrais rajouter 3 formules Si englobant la formule pour déterminer si l'ecart est supérieur à 0.1, 0.2, 0.3 ?
Sinon SUPER TRAVAIL merci beaucoup
Cordialement
Re,
Il me semble que ma réponse est passée inaperçue ;
https://forum.excel-pratique.com/excel/note-pour-competition-tres-complexe-t63009.html#p355671
oups effectivement excuse moi en répondant aux questions je suis passé à travers.
Merci BEAUCOUP de t'a formule qui à l'air de fonctionner et qui est un chouilla plus court !
merci encore de votre aide guys
bonne soirée à vous
Même résultat, écriture un poil plus condensée
en M3
=SI(MAX(GRANDE.VALEUR(H3:L3;2)-MEDIANE(H3:L3);MEDIANE(H3:L3)-GRANDE.VALEUR(H3:L3;4))>=INDEX({0,3;0,2;0,1};EQUIV(PETITE.VALEUR(H3:L3;2);{0;7,9;8,9}));MEDIANE(H3:L3)*3;SOMMEPROD(GRANDE.VALEUR(H3:L3;{2;3;4})))edit : in fine c'est pas mieux que OOoiste qui a battu tout le monde en rapidité !