Ceci est une page optimisée pour les mobiles. Cliquez sur ce texte pour afficher la vraie page.

XL 2019 Formule Max sans prendre en compte les N/A

Créer une discussion

Boostez vos compétences Excel avec notre communauté !

Rejoignez Excel Downloads, le rendez-vous des passionnés où l'entraide fait la force. Apprenez, échangez, progressez – et tout ça gratuitement ! 👉 Inscrivez-vous maintenant !

S'inscrire
P

pacman

Guest
Bonjour tout le monde,

J’aimerai incorporer un « si erreur » dans ma formule (ci-dessous) car lorsque qu’il y a une variable en « N/A » ma formule Max() renvoie un « N/A » or je voudrais que ma formule prenne la valeur la plus haute en dehors du « N/A ». Exemple :
A1 = 7
A2 = 2
A3 = N/A
A4 = 4
avec ma formule Max() je retombe sur le « N/A » or je souhaiterais avoir le « 7 ».

Formule : =MAX(SI(AA5=3;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K11: P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10: P10;0));1;-1);SI(AA5=0;M5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30: P30;0));1;-1);SI(AA5=0;T5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30: P30;0));1;-1);SI(AA5=0;W5;« N/A »))));SI(AA5=3;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));3;-3);SI(AA5=2;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));2;-2);SI(AA5=1;DECALER(INDEX(Ref.!K31: P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30: P30;0));1;-1);SI(AA5=0;Q5;« N/A »)))))

Merci d’avance pour votre aide.
PS: désolé pour la formule kilométrique, elle tient en compte plusieurs conditions et étant naze en VBA je fais avec les formules
🙂
 
Trier par date Trier par votes
Bonjour Pacman,
Essayez :
VB: 
=MAX(SIERREUR(A1:A4;""))
En validant avec ALT+CTRL+Enter car formule matricielle.
PS : La solution était donnée dans :

Valeur MIN en excluant "#N/A"

J'aimerais savoir comment exclure une valeur #N/A dans une formule. Dans ma colonne U, j'ai une formule date incluse qui me donne une date précise lorsque j'...
forum.excel-pratique.com
 
Vote positif 0 Vote négatif
Bonjour Sylvanu,
Merci beaucoup pour ce rapide retour.
En fait je sais que la formule qu'il faut c'est "MAX(SIERREUR" mais je n'arrive pas à l'incorporer dans ma longue formule 😅 pourrais-tu m'aider à ce niveau ?
 
Vote positif 0 Vote négatif
Re,
C'est vous qui intégrer les N/A dans votre formule ?
Pourquoi ne pas remplacer ce #N/A par "". De ce fait il ne sera pas utilisé pour le max, et on évite le Sierreur.
Voir PJ. Il n'y a pas d'erreur. Même si le fichier est bidon.
PS : Dans de longues formules on peut faire un retour à la ligne avec Alt+Enter, ça rend les formules plus lisibles.
VB: 
=MAX(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;T5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))))
)
 

Pièces jointes

Vote positif 0 Vote négatif
Bonjour à tous
La formule peut être raccourcie

Code: 
=MAX(
SI(AA5=0;M5;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));AA5;-AA5));
SI(AA5=0;T5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));AA5;-AA5));
SI(AA5=0;W5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));AA5;-AA5));
SI(AA5=0;Q5;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));AA5;-AA5)))
 
Vote positif 0 Vote négatif
Re,
En fait le "N/A" dont je parle correspond à la cellule T5 (fichier ci-joint). Comment dire à excel (dans ma longue formule) de prendre le Max() sans tenir compte s'il y a une cellule contenant N/A.
Merci pour l'astuce Alt+Enter, j'avoue que c'est plus lisible comme ça 🙂
 

Pièces jointes

Vote positif 0 Vote négatif
Pas tout compris à votre formule, avec cette formule ça ne plante pas mais est ce que ça fait ce que vous attendez ???
Code: 
=MAX(
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));1;-1);
SI(AA5=0;M5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(T5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;0;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;W5;""))));0);
SIERREUR(
SI(AA5=3;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));3;-3);
SI(AA5=2;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));2;-2);
SI(AA5=1;DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));1;-1);
SI(AA5=0;Q5;""))));0)
)
Plus deux Sierreur en T5 U5.
 

Pièces jointes

Vote positif 0 Vote négatif
Re
Essaye :
Code: 
=MAX(
SI(AA5=0;SIERREUR(M5;0);SIERREUR(DECALER(INDEX(Ref.!K11:P16;EQUIV(K5;Ref.!G11:G16;0);EQUIV(L5;Ref.!K10:P10;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(T5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(S5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(W5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(V5;Ref.!K30:P30;0));AA5;-AA5);0));
SI(AA5=0;SIERREUR(Q5;0);SIERREUR(DECALER(INDEX(Ref.!K31:P36;EQUIV(K5;Ref.!G31:G36;0);EQUIV(P5;Ref.!K30:P30;0));AA5;-AA5);0)))
 
Vote positif 0 Vote négatif
Merci beaucoup, ça marche maintenant ! un grand ouf de soulagement 🙂
 
Vote positif 0 Vote négatif
Merci beaucoup Chris pour votre aide (et l'astuce de la formule simplifiée) 😉
 
Vote positif 0 Vote négatif
Identifiez-vous ou inscrivez-vous pour participer.
Rejoins nos membres "Supporter XLD" et booste ton expérience Excel !
- Navigue sans publicité
- Accède à Cléa, notre assistante IA experte Excel... et pas que...
- Profite de fonctionnalités exclusives
Ton soutien permet à Excel Downloads de rester 100% gratuit et de continuer à rassembler les passionnés d'Excel.
Je deviens Supporter XLD
Assurez vous de marquer un message comme solution pour une meilleure transparence.

Discussions similaires

E
  • Résolu(e)
Microsoft 365 Calcul de la longueur d'une liste de taille variable dans Excel sans prendre en compte les erreurs N#A (sans vba)
Réponses
3
Affichages
2 K
e_liot
E
C
  • Question Question
XL 2013 Aide sur fonction EQUIV INDEX MAX pour recherche de données
Réponses
2
Affichages
1 K
Crapulfeust
C
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…