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

P

pacman

XLDnaute Nouveau
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
P

pacman

XLDnaute Nouveau
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

sylvanu

XLDnaute Barbatruc
Supporter XLD
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

  • Pacman.xlsx
    8.4 KB · Affichages: 7
Vote positif 0 Vote négatif

Chris401

XLDnaute Accro
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
P

pacman

XLDnaute Nouveau
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

  • Pacman2.xlsx
    11.6 KB · Affichages: 4
Vote positif 0 Vote négatif

sylvanu

XLDnaute Barbatruc
Supporter XLD
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

  • Pacman4.xlsx
    11.4 KB · Affichages: 2
Vote positif 0 Vote négatif

Chris401

XLDnaute Accro
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
P

pacman

XLDnaute Nouveau
Merci beaucoup, ça marche maintenant ! un grand ouf de soulagement
 
Vote positif 0 Vote négatif
P

pacman

XLDnaute Nouveau
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.

Discussions similaires

Z
  • Résolu(e)
Microsoft 365 Récupérer les éléments d'une liste en fonction de leur position
Réponses
2
Affichages
251
Zarnir
Z
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
1 K
e_liot
E
  • Question
XL 2013 Aide sur fonction EQUIV INDEX MAX pour recherche de données
Réponses
2
Affichages
920
Crapulfeust
D
  • Question
Procédure trop grande
2 3 4
Réponses
46
Affichages
9 K
domeli
D
S
  • Question
Eviter les erreurs dans la formule en cas de N/A et suppression d'onglet
Réponses
2
Affichages
838
Gorfael
M
[Excel] Quelques Fonctions Excel
2 3
Réponses
32
Affichages
53 K
mimi1970
M
Menu
Connexion
S'inscrire
Les cookies sont requis pour utiliser ce site. Vous devez les accepter pour continuer à utiliser le site. En savoir plus…