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
sylvanu à dit:
Bonjour Pacman,
Essayez :
VB: 
=MAX(SIERREUR(A1:A4;""))
En validant avec ALT+CTRL+Enter car formule matricielle.
PS : La solution était donnée dans :
forum.excel-pratique.com

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 forum.excel-pratique.com
Cliquez pour agrandir...
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

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

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
sylvanu à dit:
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;""))))
)
Cliquez pour agrandir...
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

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

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
sylvanu à dit:
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.
Cliquez pour agrandir...
Merci beaucoup, ça marche maintenant ! un grand ouf de soulagement :)
 
Vote positif 0 Vote négatif
P

pacman

XLDnaute Nouveau
Chris401 à dit:
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)))
Cliquez pour agrandir...
Merci beaucoup Chris pour votre aide (et l'astuce de la formule simplifiée) ;)
 
Vote positif 0 Vote négatif

Discussions similaires

S
  • Question
XL 2016 Afficher les bornes min et max dans lequel se trouve une valeur
Réponses
5
Affichages
347
stuka
S
Z
  • Résolu(e)
Microsoft 365 Récupérer les éléments d'une liste en fonction de leur position
Réponses
2
Affichages
504
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
Crapulfeust
  • Question
XL 2013 Aide sur fonction EQUIV INDEX MAX pour recherche de données
Réponses
2
Affichages
1 K
Crapulfeust
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
894
Gorfael
Gorfael
M
[Excel] Quelques Fonctions Excel
2 3
Réponses
32
Affichages
54 K
mimi1970
M

Membres actuellement en ligne

2 membres et 42 invités.

Partager cette page

Tutos vidéos

Power Query : Ajouter des requêtes (Excel et Power BI)
Créez votre première fonction LAMBDA dans Excel en 3 étapes!
LAMBDA, l’arme secrète de Microsoft pour Excel !
Office Insider - Découvrez les nouveautés Excel avant tout le monde

Statistiques des forums

Discussions
314 708
Messages
2 112 097
Membres
111 416
dernier inscrit
philipperoy83