XL 2019 Formule de globalisation

Bonjour, je me trouve face à des données à traiter pour lesquelles trouver une formule tourne au casse tête pour moi.

Je vous remercie de votre aide. En PJ un fichier de données.

A partir de 3 épreuves distinctes, j’ai besoin de faire un classement commun. Pour cela 3 étapes

ETAPE 1 : En colonne K des feuilles ECO INT et SCI, calcul automatique du classement dans la filière (lequel est fait sur la base de la moyenne en colonne J, avec départage des égalités en fonction de la note 3 et si la note 3 est à égalité alors classement à égalité c'est à dire qu'il peut y avoir plusieurs candidats avec le meme rang de classement)

ETAPE 2 : Calcul d’un coefficient global (sur la feuille GLOBAL, colonne F) des 3 épreuves sur la base de la règle suivante : indice_classement x (nbre_total_admis – 1) / (nbre_admis_par filière– 1 )

Sachant que :

• indice_classement = classement filière – 1 (en colonne L dans les feuilles)
• nbre_total_admis = total admis des filières ECO INT SCI
• nbre_admis_par filière = total admis dans la filière du candidat concerné

ETAPE 3 : Calcul d’un classement global (sur la feuille GLOBAL, colonne G) sachant que :

• le plus près de 0 est le mieux classé
• les exaequo sont départagés par la moyenne des notes 1 et 3 (en colonne M des feuilles ECO INT SCI) et en cas de nouvelle égalité départage en fonction de l’âge, au bénéfice du plus âgé

Attention : le nombre de candidats admis varie a chaque session, il faudrait donc que les formules s’adaptent au fait que le nombre d'admis par filière (et donc au total) est variable. Dans l'exemple en PJ il y a 18 + 5 + 7 candidats mais dans une prochaine session cela pourra être 25+14+9)

Merci de votre aide

L Serras