Formule Max intégrant condition sur valeurs entrants et sortants

[bridelisse]

Bonjour Exceleurs,

Mon titre n'est pas très explicite, mais vous comprendrez une fois le problème exposé.
Dans un tableau A1:M26, je cherche à obtenir le max de chaque ligne dans la colonne "Results" (jusque là c'est facile).

J'ai ajouté un niveau de complexité :
obtenir le max de chaque ligne qui peuvent être des valeurs numériques (6,10) comme alphanumériques (6,20A), du coup, j'ai réalisé une formule ... qui marche mais qui est un peu longue, je l'étire sur chaque cellule de ma colonne (y'a surement moyen de la raccourcir, je suis pas pro la dessus), à savoir :

=MAX(IF(ISNUMBER(SEARCH(5;A2));MID(A2;1;1);A2);IF(ISNUMBER(SEARCH(5;B2));MID(B2;1;1);B2);IF(ISNUMBER(SEARCH(5;C2));MID(C2;1;1);C2);IF(ISNUMBER(SEARCH(5;D2));MID(D2;1;1);D2);IF(ISNUMBER(SEARCH(5;E2));MID(E2;1;1);E2);IF(ISNUMBER(SEARCH(5;F2));MID(F2;1;1);F2);IF(ISNUMBER(SEARCH(5;G2));MID(G2;1;1);G2);IF(ISNUMBER(SEARCH(5;H2));MID(H2;1;1);H2);IF(ISNUMBER(SEARCH(5;I2));MID(I2;1;1);I2);IF(ISNUMBER(SEARCH(5;J2));MID(J2;1;1);J2);IF(ISNUMBER(SEARCH(5;K2));MID(K2;1;1);K2);IF(ISNUMBER(SEARCH(5;L2));MID(L2;1;1);L2))

Je souhaite ajouter un 2ème niveau à cette formule (et là je me perds...😕):

pour chaque ligne, donc toujours calculer le max des valeurs alphanumériques et numériques ET si la valeur trouvée est 3 / 4 / 5 et qu'il y a des valeurs inférieures à ce résultat sur la ligne, alors prendre la valeur inférieure (alphanumérique/numérique).

Afin de rendre la problématique plus clair, j'ai joins un fichier résumant sur 2 colonnes "Results", ce que j'ai et ce que j'aimerai avoir.


Un grand merci de votre aide,

Bridelisse

 

[R@chid]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonsoir,
tout d'abord ta formule on peut l’écrire comme suite,

=SIERREUR(MAX(SI(ESTTEXTE(A2:L2);SUBSTITUE(A2:L2;DROITE(A2:L2);"")*1;A2:L2));0)

@ valider par Ctrl+Maj+Entree
@ tirer vers le bas

il faut nous dire encore, si le MAX est supérieur @ 2 et qu'il y a une valeur inférieure on prend la dernière...
Par exemple le cas de 13 et qu'il y a des valeurs inférieures comme 5, 6, 2, 1, qu'est ce qu'on prend
le cas de 13 et il y a juste les 3, 5, qu'est ce qu'on prend ausssi

Amicalement

 

[bridelisse]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonjour Rachid,

Et merci de t'intéresser à moi 😱

Si le max est 13, on garde 13 en valeur max.

Pour ma problématique (ne pas prendre le max si c'est 3/4/5):

Si 5 en max et s'il y a valeurs inférieures ( 4, 3, 2) = max est 2
Si 5 en max et si valeurs inférieures ( 4, 3 ) = ne rien inscrire


C'est un peu plus clair ?

Amicalement 🙂

 

[bridelisse]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Re,

J'ai testé la formule, et j'ai du faire une petite modification pour qu'elle fonctionne :

=IF(ISERROR(MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2)));"";MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2)))

En tout cas, c'est cool, je reste suspendue en attendant la suite ...

 

[R@chid]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonsoir,

Re,

J'ai testé la formule, et j'ai du faire une petite modification pour qu'elle fonctionne :

=IF(ISERROR(MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2)));"";MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2)))

En tout cas, c'est cool, je reste suspendue en attendant la suite ...

Pourquoi cette modification??? tu n'es pas sur Excel2007???

Si le max est 13, on garde 13 en valeur max.

Pour ma problématique (ne pas prendre le max si c'est 3/4/5):

Si 5 en max et s'il y a valeurs inférieures ( 4, 3, 2) = max est 2
Si 5 en max et si valeurs inférieures ( 4, 3 ) = ne rien inscrire

C'est un peu plus clair ?

Si le Max est 5 et qu'il y le 2 parmi les valeurs inférieures on prend le 2,
et si il y a le 1 ???

On laisse la cellule vide ou bien on garde le 5???

C'est pas encore clair...


Amicalement

 

[bridelisse]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonjour Rachid,

Si le Max est 5 et qu'il y le 2 parmi les valeurs inférieures on prend le 2,
et si il y a le 1 ???

On laisse la cellule vide ou bien on garde le 5???

Illustration de ta question :

5 - 5A - 1A - 0 = on affiche 1

Autres exemples :
5 - 4 - 2 - 1 = on affiche 2
5 - 3 - 4A - 1 = on affiche 1
5 - 0 - 0 - 5A = on affiche 0


Je rappelle ma problématique, sur chaque ligne, si le max est l'un de ces chiffres (5 / 4 ou 3) alors il faut prendre la valeur suivante inférieure (et s'il n'y a pas de valeur inférieur =afficher 0).

 

[bridelisse]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Ah oui, oui et je suis sur Excel 2010 ... au boulot 🙂

 

[Jocelyn]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonjour le Forum,
Bonjour Bridelisse, Rachid🙂,

A tester en M2 et à étirer vers le bas formule matricielle

=SI(ESTNUM(EQUIV(MAX(SI(ESTTEXTE(A2:L2);SUBSTITUE(A2:L2;DROITE(A2:L2);"")*1;A2:L2));{3;4;5};0));SI(NB.SI(A2:L2;2)>0;2;SI(NB.SI(A2:L2;1)>0;1;0));MAX(SI(ESTTEXTE(A2:L2);SUBSTITUE(A2:L2;DROITE(A2:L2);"")*1;A2:L2)))

Cordialement

 

[Jocelyn]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

re,

petite modification de la formule pour le cas ou tes 1 et tes 2 seraient suivi d'une lettre

=SI(ESTNUM(EQUIV(MAX(SI(ESTTEXTE(A2:L2);SUBSTITUE(A2:L2;DROITE(A2:L2);"")*1;A2:L2));{3;4;5};0));SI(SOMMEPROD((ESTNUM(CHERCHE(2;A2:M2)))*1)>0;2;SI(SOMMEPROD((ESTNUM(CHERCHE(1;A2:M2)))*1)>0;1;0));MAX(SI(ESTTEXTE(A2:L2);SUBSTITUE(A2:L2;DROITE(A2:L2);"")*1;A2:L2)))

A toi te tester🙂

EDIT : Bien entendu toujours en formule matricielle

 

[bridelisse]

Re : Formule Max intégrant condition sur valeurs entrants et sortants

Bonjour Jocelyn,

Merci de ton aide,

j'ai testé ta formule, et elle était légèrement décalée par rapport à ce que je recherchais, du coup, je l'ai modifié comme suit et ça marche :

=IF(ISNUMBER(MATCH(MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2));{3;4;5};0));IF(SUMPRODUCT((ISNUMBER(SEARCH(1;A2:L2)))*1)>0;1;IF(SUMPRODUCT((ISNUMBER(SEARCH(2;A2:L2)))*1)>0;2;0));MAX(IF(ISTEXT(A2:L2);SUBSTITUTE(A2:L2;RIGHT(A2:L2);"")*1;A2:L2)))

UN MOT = NICKEL !


Au plaisir,

Bridelisse,